LINQ query runs on using `Microsoft.EntityFrameworkCore.SqlServer` but crashes on `Microsoft.EntityFrameworkCore.InMemory`

Question

I have the following query:

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
};

The query runs successfully on production and the results are as expected. I have a unit test that creates a few entities in an InMemory db, but fails on the above query. Unit test:

[Fact]
public async void OnGetAsync_Displays_All_And_Only_Active_Dsps()
{
    SeedDb();
    Sut.Enterprise = new() { EnterpriseId = ENTERPRISE_ID };

    await Sut.OnGetAsync(Sut.Enterprise.EnterpriseId, null, null, null, null, null, null, null, null, null, null, null, null, null, default);

    int cntActiveDspsInDb = Db.DistributorStores.Where(x => x.IsActive).Count();
    int cntDspsOnPage = Sut.Distributors.Items.Count;
    Assert.Equal(cntActiveDspsInDb, cntDspsOnPage);
}

Error is

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)

If I comment out // || parentEnterprise.IsActive and // || parentEnterprise.IsActive the tests pass but that obviously ruins the results of the query.

The obvious solution is to add a null check like so:

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

Then the tests pass successfully, but the production code crashes with the following error:

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'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Q: Why should the null check crash the LINQ in my prod code? Its only checking for null, what could be wrong with that?

Answer 1

The error tells you EF cannot convert your model expression into SQL logic.
See if this will work instead:

let active = enterprise.IsActive || parentEnterprise?.IsActive ?? false

I doubt it will because the expression is evaluated at runtime, but it is worth a try.

If that doesn't work, you can also create an Enterprise object to represent the null (research the null object pattern).

public static Enterprise NotAnEnterprise = new([some creation logic here to provide defaults for eac property when nullable, such as defaulting IsActive to false]);

Then you would change your linq to be

let enterprise = ds.DistributorStore_Enterprise.Where(x => x.EnterpriseId == Enterprise.EnterpriseId).SingleOrDefault() ?? Enterprise.NotAnEnterprise

This ensures your Enterprise object is not null and would make your original let statement null safe.

The null object pattern can be helpful to deal with these situations, but it does take some getting used to. One definite benefit is you'll only need to declare the null replacement value in one place, rather than scattering null checks and replacement values everywhere. Enterprise.Name = "Unknown"; Enterprise.Display = "Nothing To See Here";