EF Core SqlServer正常的LINQ查询加空校验后生产环境报错

问题背景 #

生产环境使用Microsoft.EntityFrameworkCore.SqlServer时，以下LINQ查询可正常运行且结果符合预期：

from ds in Db.DistributorStores
where ds.IsActive
let enterprise = ds.DistributorStore_Enterprise.Where(x => x.EnterpriseId == Enterprise.EnterpriseId).SingleOrDefault()
let parentEnterprise = ds.DistributorStore_Enterprise.Where(x => x.Enterprise.ChildEnterprises.Any(child => child.EnterpriseId == Enterprise.EnterpriseId)).SingleOrDefault()
let active = enterprise.IsActive || parentEnterprise.IsActive
let display = enterprise.Display || parentEnterprise.Display
select new DistributorEnterpriseModel
{
   DistributorStoreId = ds.DistributorStoreId,
   DistributorStoreName = ds.Name,
   Active = active,
   Display = ds.Display && display
};

但在使用Microsoft.EntityFrameworkCore.InMemory的单元测试中执行该查询时，抛出以下错误：

Message: 
System.InvalidOperationException : Nullable object must have a value.

Stack Trace: 
lambda_method2062(Closure , QueryContext , ValueBuffer )
Enumerator.MoveNextHelper()
Enumerator.MoveNextAsync()
Enumerator.MoveNextAsync()
EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)

若注释掉|| parentEnterprise.IsActive和|| parentEnterprise.Display，测试可通过但查询结果不符合业务需求。添加空值校验后：

let active = enterprise.IsActive || parentEnterprise != null && parentEnterprise.IsActive
let display = enterprise.Display || parentEnterprise != null && parentEnterprise.Display

单元测试可正常通过，但生产环境又出现新的报错：

InvalidOperationException: The LINQ expression 'Projection Mapping: EmptyProjectionMember -> [EntityProjectionExpression] SELECT TOP(1) d.EnterpriseId, d.DistributorStoreId FROM DistributorStores_Enterprises AS d INNER JOIN Enterprises AS e ON d.EnterpriseId == e.EnterpriseId WHERE ((d.DistributorStoreId != NULL) && (d.DistributorStoreId == d.DistributorStoreId)) && EXISTS ( Projection Mapping: SELECT 1 FROM Enterprises AS e WHERE ((e.EnterpriseId != NULL) && (e.EnterpriseId == e.ParentEnterpriseId)) && (e.EnterpriseId == @__Enterprise_EnterpriseId_0))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

原因解析 #

1. 内存数据库的空值处理差异
   InMemory数据库在执行LINQ查询时，不会像SQL Server那样对||运算符进行短路求值。当parentEnterprise为null时，原始查询中parentEnterprise.IsActive会直接访问null对象的属性，触发空引用错误；而SQL Server在执行时会利用OR的短路逻辑，当enterprise.IsActive为true时，不会再判断后半部分，因此不会触发错误。

2. SQL翻译器的局限性
   添加parentEnterprise != null && parentEnterprise.IsActive的空值校验后，EF Core的SQL Server翻译器无法将这种嵌套关联查询（x.Enterprise.ChildEnterprises.Any(...)）与空值检查的组合逻辑正确转换为SQL。这种组合涉及到关联子查询的null判断，超出了当前EF Core版本对SQL翻译的支持范围。

解决方案 #

方案1：使用空值合并运算符简化空值判断 #

利用?.和??运算符处理空值，既兼容InMemory数据库，又能被SQL Server正确翻译：

from ds in Db.DistributorStores
where ds.IsActive
let enterprise = ds.DistributorStore_Enterprise.Where(x => x.EnterpriseId == Enterprise.EnterpriseId).SingleOrDefault()
let parentEnterprise = ds.DistributorStore_Enterprise.Where(x => x.Enterprise.ChildEnterprises.Any(child => child.EnterpriseId == Enterprise.EnterpriseId)).SingleOrDefault()
let active = (enterprise?.IsActive ?? false) || (parentEnterprise?.IsActive ?? false)
let display = (enterprise?.Display ?? false) || (parentEnterprise?.Display ?? false)
select new DistributorEnterpriseModel
{
   DistributorStoreId = ds.DistributorStoreId,
   DistributorStoreName = ds.Name,
   Active = active,
   Display = ds.Display && display
};

方案2：投影仅需属性减少翻译复杂度 #

避免加载完整实体，只投影需要的IsActive和Display属性，降低EF Core翻译逻辑的复杂度：

from ds in Db.DistributorStores
where ds.IsActive
let enterprise = ds.DistributorStore_Enterprise
    .Where(x => x.EnterpriseId == Enterprise.EnterpriseId)
    .Select(x => new { x.IsActive, x.Display })
    .SingleOrDefault()
let parentEnterprise = ds.DistributorStore_Enterprise
    .Where(x => x.Enterprise.ChildEnterprises.Any(child => child.EnterpriseId == Enterprise.EnterpriseId))
    .Select(x => new { x.IsActive, x.Display })
    .SingleOrDefault()
let active = (enterprise?.IsActive ?? false) || (parentEnterprise?.IsActive ?? false)
let display = (enterprise?.Display ?? false) || (parentEnterprise?.Display ?? false)
select new DistributorEnterpriseModel
{
   DistributorStoreId = ds.DistributorStoreId,
   DistributorStoreName = ds.Name,
   Active = active,
   Display = ds.Display && display
};

内容的提问来源于stack exchange，提问作者Zvi