EF Core Linq to SQLite could not be translated, works on SQL Server

c# entity-framework entity-framework-core linq sqlite


I have a linq expression that is working fine on the production database but throws error on the SQLite in memory db of the test context. The error I got says:

The LINQ expression (EntityShaperExpression:

EntityType: Item
    (ProjectionBindingExpression: Inner)
IsNullable: True ).Price * (Nullable<decimal>)(decimal)(EntityShaperExpression: 
EntityType: ISItem
    (ProjectionBindingExpression: Outer)
IsNullable: False ).Qty' 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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

The linq expression:

var locationsQuery = context.DbContext.Locations
            .Include(x => x.Check)
            .Include(x => x.Scan)
            .Include(x => x.EScan)
                .ThenInclude(es => es!.Items)
                    .ThenInclude(isi => isi.Item)
            .Where(x => x.ProjectId == query.ProjectId)
            .Select(x => x);

Then I have a projection:

LocationId = entity.Id,
        LHA = entity.LHA,
        Zone = entity.Zone,
        Area = entity.Area,
        LocationState = $"DB.{nameof(LocationState)}.{entity.State.ToString()}",
        CheckUserId = entity.Check != null ? entity.Check.ScanUserId : (int?)null,
        ScanUserId = entity.Scan != null ? entity.Scan.ScanUserId : (int?)null,
        CheckUserName = entity.Check != null ? entity.Check.ScanUser.Name : null,
        ScanUserName = entity.Scan != null ? entity.Scan.ScanUser.Name : null,
        SumPrice = entity.EffectiveScan != null // This cannot be evaluated
                        ? entity.EScan.Items
                            .Where(x => x.Item != null)
                            .Sum(x => x.Item!.Price * (decimal)x.Qty)
                        : null,
        SumQty = entity.EScan != null
                        ? entity.EScan.Items
                            .Sum(x => x.Qty)
                        : (double?)null

If I remove the SumPrice calculation it works as expected (as on the production system). What can I do to this query to work the same on SqlServer and SQLite In memory db?

3/3/2020 9:01:51 AM

Popular Answer

Okay, the solution was to change my projection class property (SumPrice) to use double instead of decimal and convert the value to double:

SumPrice = entity.EffectiveScan != null
             ? entity.EffectiveScan.Items
                     .Where(x => x.Item != null)
                     .Sum(x => x.Qty * (double?)x.Item!.Price)
             : (double?)null,

I don't know what caused this. Does SQLite or its provider has problem with the decimal type?

3/4/2020 2:07:54 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow