背景
在使用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;