背景
在使用EFCore中碰到最多的一类问题就是自己写的Linq语句最终在转换为SQL语句的时候失败，特别是嵌套子查询的时候经常可能不符合预期，这个时候该怎么解决这个问题，这个是在进行SQL查询的时候经常碰到下面的这个错误：
System.InvalidOperationException: Error generated for warning 'Microsoft.EntityFrameworkCore.Query.QueryClientEvaluationWarning: The LINQ expression 'XXX' could not be translated and will be evaluated locally.'. This exception can be suppressed or logged by passing event ID 'RelationalEventId.QueryClientEvaluationWarning' to the 'ConfigureWarnings' method in 'DbContext.OnConfiguring' or 'AddDbContext'.
这个在开发的时候经常会遇到，如果忽略这个错误发到线上环境的时候有可能会将之前的Linq语句直接查询数据到内存中进行处理，这个在很多情况下会造成几乎灾难的后果，所以在开发的时候我们要通过一定的手段提前将这个问题在调试的时候就解除掉，经过我们团队长期的摸索最后找到了一些EFCore的一些规律和技巧，在后面的部分我们将会就这个问题进行分析。
案例分析
在下面的一个代码片段中，freeMaintainFees 是我们查询的一个IQueryable<T>的对象，这个是一个简单的两个表join后分组并求和的过程，但是这个返回的结果会作为一个子查询并作为查询workItemAndMaterialQuery的一个部分，这个时候在EFCore中经常会出现之前报的那个System.InvalidOperationException的异常，就是由于整个查询的结果过于复杂所以造成EFCore在转换SQL的时候经常失败，这个就有了我们之前的那个结论：提前将表达式转换成Sql并返回IQueryable<T> 类型的数据避免转换失败，那我们怎么实现这样的一个效果呢？如果你细心观察这个方法你就会发现我们用了一个freeMaintainFees = _repairContractRepository.GetAllByConvertSql(freeMaintainFees)的中间方法，那这个里面核心就是GetAllByConvertSql的方法，那么这个方法到底是怎么实现的呢？
private IQueryable<GetWorkItemAndMaterialDetailOutput> GetEmployeePerformanceWorkItemAndMaterialDetailQuery(GetEmployeePerformanceDetailsInput input) {
// 服务顾问结清费用：各服务顾问非作废的委托书，对应的维修结算单的付款方式为已经结清的实收金额费用
//修理工完工费用：非作废的委托书中各维修项目上的修理工对应的委托书中实收金额费用
var repairFees = _repairContractRepository.GetAll()
.Where(r => r.ContractType == ContractType.维修)
.Select(r => new { r.Id, r.Status, TotalFeeAfter = r.TotalFeeAfter ?? default });
//免保费：合同类型 = 索赔 且 委托书.维修类别 = 首次保养、VIP保养、定期保养的委托书，对应非作废的维修索赔单.费用合计
var freeMaintainFees = from claim in _claimRepository.GetAll().Where(c => c.Status != ClaimStatus.作废 && c.Type == ClaimType.保养)
join contract in _repairContractRepository.GetAll()
.Include(r => r.RepairContractWorkItems)
.Where(r => r.ContractType == ContractType.索赔 && r.RepairContractWorkItems.Any(w => w.RepairType == RepairType.VIP保养
|| w.RepairType == RepairType.首次保养 || w.RepairType == RepairType.定期保养))
on claim.RepairContractId equals contract.Id
group claim.TotalFee by claim.RepairContractId into grouped
select new FreeMaintainFeeModel {
RepairContractId = grouped.Key,
TotalFee = grouped.Sum()
};
freeMaintainFees = _repairContractRepository.GetAllByConvertSql(freeMaintainFees);
var dealerId = SdtSession.TenantId.GetValueOrDefault();
var workItemAndMaterialQuery = from repairContract in _repairContractRepository.GetAll().Where(x => x.ServiceAdvisorId == input.EmployeeId)
.WhereIf(input.BeginTime.HasValue, x => input.BeginTime <= x.CreateTime)
.WhereIf(input.EndTime.HasValue, x => x.CreateTime <= input.EndTime)
.Where(r => r.DealerId == dealerId && r.Status == RepairContractStatus.已结算)
join workItem in _repairContractWorkItemRepository.GetAll()
on repairContract.Id equals workItem.RepairContractId
join material in _repairContractMaterialRepository.GetAll()
on workItem.Id equals material.RepairContractWorkItemId into materials
from material in materials.DefaultIfEmpty()
join fMaintainFee in freeMaintainFees
on repairContract.Id equals fMaintainFee.RepairContractId into fMaintainFees
from fMaintainFee in fMaintainFees.DefaultIfEmpty()
join repair in repairFees.Where(r => r.Status == RepairContractStatus.已结算)
on repairContract.Id equals repair.Id into repairs
from repair in repairs.DefaultIfEmpty()
select new GetWorkItemAndMaterialDetailOutput {
RepairContractCode = repairContract.Code,
LicensePlate = repairContract.LicensePlate,
Vin = repairContract.Vin,
BrandName = repairContract.BrandName,
ProductCode = repairContract.ProductCode,
ProductCategoryCode = repairContract.ProductCategoryCode,
ServiceAdvisorName = repairContract.ServiceAdvisorName,
WorkerName = workItem.Id == null ? "" : workItem.WorkerName,
WorkItemCode = workItem.Id == null ? "" : workItem.WorkItemCode,
WorkItemName = workItem.Id == null ? "" : workItem.WorkItemName,
RepairType = workItem.Id == null ? new RepairType() : workItem.RepairType,
LaborFeeAfter = repairContract.ContractType == ContractType.索赔
? workItem.Id == null ? 0 : workItem.LaborHour * workItem.LaborPrice
: workItem.Id == null ? 0 : workItem.LaborFeeAfter == null ? 0 : workItem.LaborFeeAfter,
PartCode = material.Id == null ? "" : material.NewPartCode,
PartName = material.Id == null ? "" : material.NewPartName,
Quantity = material.Id == null ? default : material.Quantity,
Price = material.Id == null ? default : material.Price,
MaterialFee = material.Id == null ? default : material.Quantity * material.Price,
MaterialFeeAfter = repairContract.ContractType == ContractType.索赔
? material.Id == null ? default : material.Price * material.Quantity
: material.Id == null ? default : material.MaterialFeeAfter ?? 0,
SettlementProperty = workItem.Id == null ? new RepairSettlementType() : workItem.SettlementProperty,
FinishDate = workItem.Id == null ? null : workItem.FinishDate,
FreeMaintainFee = fMaintainFee == null ? default : fMaintainFee.TotalFee,
RepairFee = repair.Id == null ? default : repair.TotalFeeAfter
};
return workItemAndMaterialQuery;
}
我们先来看看GetAllByConvertSql这个方法实现的源码，我们先来看这个方法的接口定义
using System;
using System.Linq;
using Abp.Domain.Entities;
using Abp.Domain.Repositories;
using Microsoft.EntityFrameworkCore;
namespace Sunlight.EFCore.Repositories {
/// <inheritdoc />
public interface ISdtRepository<TEntity, TPrimaryKey> : IRepository<TEntity, TPrimaryKey> where TEntity : class, IEntity<TPrimaryKey> {
/// <summary>
/// 用Sql查询数据，解决有些时候无法转换LinQ的问题
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
IQueryable<TEntity> GetAllFromSqlRaw(string sql, params object[] parameters);
/// <summary>
/// 用Sql查询数据，解决有些时候无法转换LinQ的问题
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
IQueryable<TEntity> GetAllFromSqlInterpolated(FormattableString sql);
/// <summary>
/// 提前将表达式转换成Sql，然后返回<see cref="IQueryable{T}"/> 类型的数据，避免ef core转换失败
/// <para/>
/// 使用时需要在 DbContext 里面创建 <typeparamref name="T"/>类型的集合：
/// <para/>
/// Ef Core 2.x <see cref="DbQuery{T}"/>
/// <para/>
/// Ef Core 3.x <see cref="DbSet{T}"/>，同时在 <see cref="DbContext.OnModelCreating(ModelBuilder)"/> 里 设置 <c>modelBuilder.Entity<T>(e => e.HasNoKey().ToView("xxx"))</c>
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query"></param>
/// <returns></returns>
IQueryable<T> GetAllByConvertSql<T>(IQueryable<T> query) where T : class ;
}
}
这个接口中最后一个方法就是我们今天要说的GetAllByConvertSql方法，我们来看看这个具体的实现。
using System;
using System.Linq;
using System.Net.Http.Headers;
using Abp.Domain.Entities;
using Abp.EntityFrameworkCore;
using Abp.EntityFrameworkCore.Repositories;
using Microsoft.EntityFrameworkCore;
using Sunlight.EFCore.Extensions;
namespace Sunlight.EFCore.Repositories {
/// <summary>
/// 自定义 Repository 的基类，使用方式参照 https://aspnetboilerplate.com/Pages/Documents/Entity-Framework-Core#custom-repositories
/// </summary>
/// <typeparam name="TEntity">Entity type</typeparam>
/// <typeparam name="TPrimaryKey">Primary key type of the entity</typeparam>
/// <typeparam name="TDbContext"></typeparam>
public abstract class SdtEfRepositoryBase<TDbContext, TEntity, TPrimaryKey> : EfCoreRepositoryBase<TDbContext, TEntity, TPrimaryKey>, ISdtRepository<TEntity, TPrimaryKey>
where TEntity : class, IEntity<TPrimaryKey> where TDbContext : DbContext {
/// <inheritdoc />
protected SdtEfRepositoryBase(IDbContextProvider<TDbContext> dbContextProvider)
: base(dbContextProvider) {
}
// Add your common methods for all repositories
/// <inheritdoc />
public IQueryable<TEntity> GetAllFromSqlRaw(string sql, params object[] parameters) {
#if NETCOREAPP2_2
return Table.FromSql(sql, parameters);
# else
return Table.FromSqlRaw(sql, parameters);
#endif
}
/// <inheritdoc />
public IQueryable<TEntity> GetAllFromSqlInterpolated(FormattableString sql) {
#if NETCOREAPP2_2
return Table.FromSql(sql);
# else
return Table.FromSqlInterpolated(sql);
#endif
}
/// <inheritdoc />
public IQueryable<T> GetAllByConvertSql<T>(IQueryable<T> query) where T : class {
#if NETCOREAPP2_2
return Context.Query<T>().FromSql(query.ToSql(Context));
# else
return Context.Set<T>().FromSqlRaw(query.ToSql());
#endif
}
}
}
这个里面我们是通过继承ABP框架中的EfCoreRepositoryBase来实现的，这个方法里面的FromSqlRaw是EFCore框架中Microsoft.EntityFrameworkCore命名空间下面的RelationalQueryableExtensions进行定义的，另外这个方法的参数是query.ToSql方法，这个按照我们的解释是用于将IQueryable<T>转换成sql语句的方法，这个也是我们的代码中用到的一个扩展方法，因为很多时候我们是需要将我们的IQueryable<T>对象转变为sql的，特别是我们需要进行报表处理的时候，有时候我们不想全部用SQL语句进行写，有些我们要先用Linq写然后将其转换成SQL语句然后再和我们的sql语句进行拼接，从而达到最终的目的。
下面我们来看看ToSql语句的具体实现。
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
}
}
这个里面ToSql语句分为EFCore2.2版本和EFCore3.1版本，在这段代码中我们使用了NETCOREAPP2_2编译开关来区分，在使用的时候需要注意。另外我们这个是定义在ISdtRepository<TEntity, TPrimaryKey>接口中的，所以要想使用这个方法我们的Repository必须实现这个接口，在我们上面的示例代码中，我们的_repairContractRepository的定义是这样的
private readonly ISdtRepository<RepairContract, Guid> _repairContractRepository;
在EFCore中只有默认的IRepository<TEntity, TPrimaryKey>接口才会注入到依赖注入容器中去，所以我们定义的ISdtRepository<RepairContract, Guid>是无法注入到依赖注意容器中去的，那么在使用ABP框架进行开发时是怎么注入到依赖注入容器中去的呢？如果你想了解ABP中的依赖注入原理请点击这里，回到主题，这个问题的答案是在我们定义的EFCoreModule中重写AbpModule中的虚方法Initialize()，并通过下面这句代码实现
IocManager.Register<ISdtRepository<RepairContract, Guid>,
StatsRepositoryBase<RepairContract>>(DependencyLifeStyle.Transient);
整个过程就像下面的例子：
using System;
using System.Collections.Generic;
using System.Reflection;
using Abp.Dependency;
using Abp.EntityFrameworkCore;
using Abp.Localization.Dictionaries;
using Abp.Localization.Dictionaries.Xml;
using Abp.Modules;
using Abp.Reflection.Extensions;
using Microsoft.Extensions.Configuration;
using Sunlight.EFCore;
using Sunlight.EFCore.Repositories;
using Sunlight.Stats.Data.EntityFrameworkCore.Repository;
using Sunlight.Stats.Domain;
using Sunlight.Stats.Domain.Models.DmsAfterSales;
namespace Sunlight.Stats.Data.EntityFrameworkCore {
[DependsOn(
typeof(StatsDomainModule),
typeof(AbpEntityFrameworkCoreModule))]
public class StatsEFCoreModule : AbpModule {
public const string LocalizationSourceName = "Sunlight";
public override void PreInitialize() {
// Oracle 11 目前不支持默认的 ReadUncommitted
Configuration.UnitOfWork.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
var thisAssembly = typeof(StatsEFCoreModule).GetAssembly();
Configuration.Localization.Sources.Add(
new DictionaryBasedLocalizationSource(LocalizationSourceName,
new XmlEmbeddedFileLocalizationDictionaryProvider(thisAssembly, "Sunlight.Localization.SourceFiles")
)
);
}
public override void Initialize() {
IocManager.RegisterAssemblyByConvention(typeof(StatsEFCoreModule).GetAssembly());
var configuration = IocManager.Resolve<IConfiguration>();
var dbType = Enum.Parse<DataBaseType>(configuration["DataBase:Dialect"]);
switch (dbType) {
case DataBaseType.SqlServer:
case DataBaseType.Oracle:
case DataBaseType.MySql:
IocManager.AddCodeGenerator<StatsDbContext, Guid>(dbType);
break;
case DataBaseType.InMemory:
break;
case DataBaseType.Unknown:
default:
throw new ArgumentOutOfRangeException();
}
IocManager.Register<ISdtRepository<RepairContract, Guid>,
StatsRepositoryBase<RepairContract>>(DependencyLifeStyle.Transient);
DapperExtensions.DapperExtensions.SetMappingAssemblies(new List<Assembly> { typeof(StatsEFCoreModule).GetAssembly() });
}
}
}
上面介绍完了整个GetAllByConvertSql方法的源码及定义，后面我们再介绍一个重要的部分，就是我们使用这个方法的时候返回freeMaintainFees的时候，这部分的数据必须是一个视图，只有这样我们才能够正确返回从而不报错，这里我们看看在EFCore3.1中我们怎么定义这个视图。
1 定义实体Model
这个定义比较简单，代码如下：
public class FreeMaintainFeeModel {
public Guid RepairContractId { get; set; }
public decimal TotalFee { get; set; }
}
2 在DbContext中定义成视图类型
A 由于EFCore3.1中取消了DbQuery类型，所以只能定义成DbSet类型，定义如下：
public DbSet<FreeMaintainFeeModel> FreeMaintainFeeModels { get; set; }
B OnModelCreating中定义视图
modelBuilder.Entity<FreeMaintainFeeModel>(e => e.HasNoKey().ToView("FreeMaintainFeeModel"));
上面的介绍完成了整个过程的分析，这个信息量还是很大的需要自己去认真分析，其次需要对ABP框架和EFCore有很多的了解。
总结
最后面我们来猜测一下为什么加了GetAllByConvertSql这个方法后的IQueryable<T>对象后然后和后面的部分一起查询的时候整个过程就能够正确生成SQL呢？这个是因为GetAllByConvertSql这个方法内部通过ToSql语句提前将这部分转化成了SQL语句，后面再使用Linq和这部分的IQueryable<T>进行操作然后转换的时候，最终查询的SQL就会使用之前已经经过GetAllByConvertSql方法转换好的部分SQL语句，而不是整个再将整个IQueryable重新生成一遍，如果我们将最终的IQueryable一次性生成的话，如果里面的逻辑太复杂EFCore有可能最终生成的语句达不到预期，这个时候就需要我们将里面复杂的子查询单独拆出来提前将其转换成SQL，然后将这部分SQL和最终结果生成的SQL进行拼接这样就能大大提高最终生成SQL的准确率，这个结论也是我们在经过大量总结后得出的实战经验，这个是非常重要的。