背景
在EFCore中有些时候我们不可避免需要将EFCore中使用Linq写的查询语句提前转换成SQL语句，特别是在写一些报表应用的时候特别适用，在我们的应用中我们可以将部分查询操作的语句通过Linq来写，然后再将其转换成SQL语句，将转换的SQL语句嵌入到其它SQL语句中，我们先来看看我们的是如何将IQueryable泛型对象直接转换成SQL语句的。
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query;
using Sunlight.Domain.Models;
using Sunlight.EFCore.Repositories;
#if NETCOREAPP2_2
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Query.Internal;
using Microsoft.EntityFrameworkCore.Storage;
#else
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;
#endif
namespace Sunlight.EFCore.Extensions {
/// <summary>
/// IQueryable类型的扩展方法
/// </summary>
public static class QueryableExtensions {
#if NETCOREAPP2_2
/// <summary>
/// 将查询语句转换成Sql, 便于进一步的Sql拼接
/// <seealso href="https://github.com/yangzhongke/ZackData.Net/blob/master/Tests.NetCore/IQueryableExtensions.cs" />
/// </summary>
/// <param name="query"></param>
/// <param name="dbCtx"></param>
/// <typeparam name="TEntity"></typeparam>
/// <returns></returns>
public static string ToSql<TEntity>(this IQueryable<TEntity> query, DbContext dbCtx) {
var modelGenerator = dbCtx.GetService<IQueryModelGenerator>();
var queryModel = modelGenerator.ParseQuery(query.Expression);
var databaseDependencies = dbCtx.GetService<DatabaseDependencies>();
var queryCompilationContext = databaseDependencies.QueryCompilationContextFactory.Create(false);
var modelVisitor = (RelationalQueryModelVisitor)queryCompilationContext.CreateQueryModelVisitor();
modelVisitor.CreateQueryExecutor<TEntity>(queryModel);
var sql = modelVisitor.Queries.First().ToString();
return sql;
}
#else
/// <summary>
/// 将查询语句转换成Sql, 便于进一步的Sql拼接
/// <seealso href="https://gist.github.com/rionmonster/2c59f449e67edf8cd6164e9fe66c545a#gistcomment-3109335" />
/// </summary>
/// <param name="query"></param>
/// <param name="dbCtx">数据库上下文</param>
/// <typeparam name="TEntity"></typeparam>
/// <returns></returns>
public static string ToSql<TEntity>(this IQueryable<TEntity> query, DbContext dbCtx = null) where TEntity : class {
return ToSql(query);
}
/// <summary>
/// 将查询语句转换成Sql, 便于进一步的Sql拼接
/// <seealso href="https://gist.github.com/rionmonster/2c59f449e67edf8cd6164e9fe66c545a#gistcomment-3109335" />
/// </summary>
/// <param name="query"></param>
/// <typeparam name="TEntity"></typeparam>
/// <returns></returns>
private static string ToSql<TEntity>(this IQueryable<TEntity> query) where TEntity : class {
using var enumerator = query.Provider.Execute<IEnumerable<TEntity>>(query.Expression).GetEnumerator();
var relationalCommandCache = enumerator.Private("_relationalCommandCache");
var selectExpression = relationalCommandCache.Private<SelectExpression>("_selectExpression");
var factory = relationalCommandCache.Private<IQuerySqlGeneratorFactory>("_querySqlGeneratorFactory");
var sqlGenerator = factory.Create();
var command = sqlGenerator.GetCommand(selectExpression);
var sql = command.CommandText;
return sql;
}
private static object Private(this object obj, string privateField) => obj?.GetType().GetField(privateField, BindingFlags.Instance | BindingFlags.NonPublic)?.GetValue(obj);
private static T Private<T>(this object obj, string privateField) => (T)obj?.GetType().GetField(privateField, BindingFlags.Instance | BindingFlags.NonPublic)?.GetValue(obj);
/// <summary>
/// 增加
/// </summary>
/// <param name="query"></param>
/// <typeparam name="TEntity"></typeparam>
/// <returns></returns>
public static (string, IReadOnlyDictionary<string, object>) ToSqlWithParams<TEntity>(this IQueryable<TEntity> query) {
using var enumerator = query.Provider.Execute<IEnumerable<TEntity>>(query.Expression).GetEnumerator();
var relationalCommandCache = enumerator.Private("_relationalCommandCache");
var selectExpression = relationalCommandCache.Private<SelectExpression>("_selectExpression");
var factory = relationalCommandCache.Private<IQuerySqlGeneratorFactory>("_querySqlGeneratorFactory");
var queryContext = enumerator.Private<RelationalQueryContext>("_relationalQueryContext");
var sqlGenerator = factory.Create();
var command = sqlGenerator.GetCommand(selectExpression);
var parametersDict = queryContext.ParameterValues;
var sql = command.CommandText;
return (sql, parametersDict);
}
#endif
}
}
在这个里面有个NETCOREAPP2_2的编译开关便于我们在EFCore2.2版本和EFCore3.1版本中分别使用不同的方法，我们首先来看在EFCore2.2版本中的这段用法，任何IQueryable<T>类型的查询表达式都可以使用ToSql方法将我们的查询表达式转换成最终的SQL语句，这个方法还必须传入当前的DbContext对象从而正确的转换，另外一种是EFCore3.1版本中的两种方法，其中一种是查询的时候不带变量的ToSql方法另外一种是带参数的ToSqlWithParams,下面我们着重来说明在Asp.Net Core中我们到底该怎么使用这几个方法。
使用
无论是在EFCore2.2和EFCore3.1 版本中不带参数的方法都很好理解，下面的例子主要来讲述EFCore3.1中如何执行带参数的ToSqlWithParams方法，我们来看下面的Linq方法
public async Task<int> PartConsumeStatisticAsync(DateTime? statisticDateTime) {
// 每个服务站，每个仓库 + 备件生成一条结转数据
var lastMonth = statisticDateTime ?? DateTime.Now;
lastMonth = new DateTime(lastMonth.AddMonths(-1).Year, lastMonth.AddMonths(-1).Month, 1);
await _partConsumeRepository.BatchDeleteAsync(c => c.Month == lastMonth);
var outTypes = new[] {
PartOutType.维修领料出库,
PartOutType.零售出库,
PartOutType.保养套餐销售出库,
PartOutType.延保销售出库,
PartOutType.二网调拨出库,
PartOutType.领用出库
};
var inTypes = new[] {
PartInType.维修退料入库,
PartInType.零售退货入库,
PartInType.保养套餐退货入库,
PartInType.延保销售退货入库
};
var partOuts = (from partOut in _partOutRepository.GetAll()
.Where(p => p.CreateTime.HasValue && p.CreateTime.Value.Year == lastMonth.Year && p.CreateTime.Value.Month == lastMonth.Month
//这里在EFCore 3.1 版本使用 outTypes.Contains(p.OutType) 会报错：Unable to cast object of type 'Microsoft.EntityFrameworkCore.Query.SqlExpressions.SqlParameterExpression'
//to type 'Microsoft.EntityFrameworkCore.Query.SqlExpressions.SqlConstantExpression'
// chery/home#4597
&& (p.OutType == PartOutType.维修领料出库 || p.OutType == PartOutType.零售出库 || p.OutType == PartOutType.保养套餐销售出库
|| p.OutType == PartOutType.延保销售出库 || p.OutType == PartOutType.二网调拨出库 || p.OutType == PartOutType.领用出库))
join detail in _partOutDetailRepository.GetAll() on partOut.Id equals detail.PartOutId
select new {
partOut.DealerId,
partOut.WarehouseId,
partOut.OutType,
detail.PartId,
detail.OutQuantity
}).GroupBy(p => new { p.DealerId, p.WarehouseId, p.PartId },
(k, g) => new {
k.DealerId,
k.WarehouseId,
k.PartId,
WXOutQuantity = g.Sum(s => s.OutType == PartOutType.维修领料出库 ? s.OutQuantity : 0),
LSOutQuantity = g.Sum(s => s.OutType == PartOutType.零售出库 ? s.OutQuantity : 0),
BYOutQuantity = g.Sum(s => s.OutType == PartOutType.保养套餐销售出库 ? s.OutQuantity : 0),
YBOutQuantity = g.Sum(s => s.OutType == PartOutType.延保销售出库 ? s.OutQuantity : 0),
EWOutQuantity = g.Sum(s => s.OutType == PartOutType.二网调拨出库 ? s.OutQuantity : 0),
LYOutQuantity = g.Sum(s => s.OutType == PartOutType.领用出库 ? s.OutQuantity : 0)
});
var partIns = (from partIn in _partInRepository.GetAll().Where(p => p.CreateTime.HasValue && p.CreateTime.Value.Year == lastMonth.Year && p.CreateTime.Value.Month == lastMonth.Month
//这里在EFCore 3.1 版本使用 inTypes.Contains(p.InType) 会报错 同上
&& (p.InType == PartInType.维修退料入库 || p.InType == PartInType.零售退货入库 || p.InType == PartInType.保养套餐退货入库 || p.InType == PartInType.延保销售退货入库))
join detail in _partInDetailRepository.GetAll() on partIn.Id equals detail.PartInId
select new {
partIn.DealerId,
partIn.WarehouseId,
partIn.InType,
detail.PartId,
detail.InQuantity
}).GroupBy(p => new {
p.DealerId,
p.WarehouseId,
p.PartId
}, (k, g) => new {
k.DealerId,
k.WarehouseId,
k.PartId,
WXInQuantity = g.Sum(s => s.InType == PartInType.维修退料入库 ? s.InQuantity : 0),
LSInQuantity = g.Sum(s => s.InType == PartInType.零售退货入库 ? s.InQuantity : 0),
BYInQuantity = g.Sum(s => s.InType == PartInType.保养套餐退货入库 ? s.InQuantity : 0),
YBInQuantity = g.Sum(s => s.InType == PartInType.延保销售退货入库 ? s.InQuantity : 0),
});
return await _partConsumeStatsRepository.GeneratePartConsume(partOuts, partIns, lastMonth);
}
这里面partIns和partOuts是两个IQueryable的匿名对象的集合，这里我们先来看看使用 var partInsSql=partIns.ToSql()方法，我们来看看最终转换成的sql到底长成啥样子。
SELECT
[p].[DealerId],
[p].[WarehouseId],
[p0].[PartId],
SUM(CASE
WHEN [p].[InType] = 6
THEN [p0].[InQuantity]
ELSE 0.0
END) AS [WXInQuantity],
SUM(CASE
WHEN [p].[InType] = 4
THEN [p0].[InQuantity]
ELSE 0.0
END) AS [LSInQuantity],
SUM(CASE
WHEN [p].[InType] = 5
THEN [p0].[InQuantity]
ELSE 0.0
END) AS [BYInQuantity],
SUM(CASE
WHEN [p].[InType] = 8
THEN [p0].[InQuantity]
ELSE 0.0
END) AS [YBInQuantity]
FROM [PartIn] AS [p]
INNER JOIN [PartInDetail] AS [p0] ON [p].[Id] = [p0].[PartInId]
WHERE (((DATEPART(year, [p].[CreateTime]) = @__lastMonth_Year_0) OR
(DATEPART(year, [p].[CreateTime]) IS NULL AND @__lastMonth_Year_0 IS NULL)) AND
((DATEPART(month, [p].[CreateTime]) = @__lastMonth_Month_1) OR
(DATEPART(month, [p].[CreateTime]) IS NULL AND @__lastMonth_Month_1 IS NULL))) AND
(((([p].[InType] = 6) OR ([p].[InType] = 4)) OR ([p].[InType] = 5)) OR ([p].[InType] = 8))
GROUP BY [p].[DealerId], [p].[WarehouseId], [p0].[PartId]
这里我们发现我们定义的lastMonth变量传递到Linq中去,最后我们发现转换成的SQL中是以变量@__lastMonth_Year_0、@__lastMonth_Month_1的形式呈现的，那么我们怎样将最终的变量传递到这两个参数中去呢？这里我们肯定想到了使用ToSqlWithParams方法，那么我们来看看这个_partConsumeStatsRepository.GeneratePartConsume(partOuts, partIns, lastMonth)这个子方法我们最终是怎么实现的？
public async Task<int> GeneratePartConsume<T1, T2>(IQueryable<T1> outQuery, IQueryable<T2> inQuery, DateTime theMonth)
where T1 : class where T2 : class {
var (outQuerySql, outParams) = outQuery.ToSqlWithParams();
var (inQuerySql, inParams) = inQuery.ToSqlWithParams();
var sql = $@"Insert into PartConsume
(Id, DealerId, WarehouseId, PartId,
WXOutQuantity,LSOutQuantity,BYOutQuantity,YBOutQuantity,EWOutQuantity, LYOutQuantity,
WXInQuantity,LSInQuantity,BYInQuantity,YBInQuantity,TotalQuantity,
PartName, PartCode, DealerName, DealerCode, WarehouseName, WarehouseCode, Month, TheDate, IsExternalPart)
select newid(), a.*, Part.Name PartName, Part.Code PartCode,
(select Name From Company WHERE Id = a.DealerId) DealerName,
(select Code From Company WHERE Id = a.DealerId) DealerCode,
(select Name From DealerPartWarehouse WHERE Id = a.WarehouseId) WarehouseName,
(select Code From DealerPartWarehouse WHERE Id = a.WarehouseId) WarehouseCode, '{theMonth:u}', GetDate(), Part.IsExternalPart
FROM (select isnull(outQ.DealerId, inQ.DealerId) DealerId,
isnull(outQ.WarehouseId, inQ.WarehouseId) WarehouseId,
isnull(outQ.PartId, inQ.PartId) PartId,
ISNULL(WXOutQuantity,0) WXOutQuantity,ISNULL(LSOutQuantity,0) LSOutQuantity,ISNULL(BYOutQuantity,0) BYOutQuantity,
ISNULL(YBOutQuantity,0) YBOutQuantity,ISNULL(EWOutQuantity,0) EWOutQuantity, ISNULL(LYOutQuantity,0) LYOutQuantity,
ISNULL(WXInQuantity,0) WXInQuantity,ISNULL(LSInQuantity,0) LSInQuantity,ISNULL(BYInQuantity,0) BYInQuantity,ISNULL(YBInQuantity,0) YBInQuantity,
(ISNULL(WXOutQuantity,0)+ISNULL(LSOutQuantity,0)+ISNULL(BYOutQuantity,0)
+ISNULL(YBOutQuantity,0)+ISNULL(EWOutQuantity,0)+ISNULL(LYOutQuantity,0)
-ISNULL(WXInQuantity,0)-ISNULL(LSInQuantity,0)-ISNULL(BYInQuantity,0)-ISNULL(YBInQuantity,0)) TotalQuantity
From ({outQuerySql}) outQ
full join
({inQuerySql}) inQ
on outQ.DealerId = inQ.DealerId and outQ.WarehouseId = inQ.WarehouseId and outQ.PartId = inQ.PartId) a
inner join Part on Part.Id = a.PartId";
var parameters = new List<SqlParameter>();
outParams.ForEach(outParam => {
parameters.Add(new SqlParameter(outParam.Key, outParam.Value));
});
inParams.ForEach(inParam => {
if (parameters.Any(p => p.ParameterName == inParam.Key && p.Value.ToString() != inParam.Value.ToString())) {
throw new ValidationException("转换出的SQL语句中参数存在参数名称相同但是值不同的对象");
}
if (parameters.All(p => p.ParameterName != inParam.Key && p.Value != inParam.Value)) {
parameters.Add(new SqlParameter(inParam.Key, inParam.Value));
}
});
return await Context.Database.ExecuteSqlRawAsync(sql, parameters.ToArray());
}
在ToSqlWithParams返回值除了当前的sql之外还有当前sql中的参数信息，我们后面需要将当前的参数信息转换成SqlParameter集合，然后通过ExecuteSqlRawAsync带参数的方法将参数传递进去，这样才能够真正将最终的参数值传递到sql中的@__lastMonth_Year_0、@__lastMonth_Month_1中去，从而最终实现数据库中的sql的执行和应用。