LINQ to SQLite could not be translated but works on SQL Server

Question

I have a LINQ expression that is working fine on the production database but throws an error on the SQLite in-memory database of the test context:

The LINQ expression (EntityShaperExpression:

EntityType: Item
ValueBufferExpression:
    (ProjectionBindingExpression: Inner)
IsNullable: True ).Price * (Nullable<decimal>)(decimal)(EntityShaperExpression:
EntityType: ISItem
ValueBufferExpression:
    (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 on the production system. What can I do to this query to make it work the same on SQL Server and SQLite in-memory database?

Inland

Try .Sum(x => x.Item.Price * (decimal)x.Qty) – Crescin

Answer 1

You probably are going to use the code in production environment with some database other than sqllite. You may not want to change your code based on a dependency on your development database. in your context class, in OnModelCreating method add below snippet and remove casting from your code.

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());

        if (Database.ProviderName == "Microsoft.EntityFrameworkCore.Sqlite")
        {
            foreach (var entityType in modelBuilder.Model.GetEntityTypes())
            {
                var properties = entityType.ClrType.GetProperties().Where(p => p.PropertyType == typeof(decimal));
                var dateTimeProperties = entityType.ClrType.GetProperties()
                    .Where(p => p.PropertyType == typeof(DateTimeOffset));

                foreach (var property in properties)
                {
                    modelBuilder.Entity(entityType.Name).Property(property.Name).HasConversion<double>();
                }

                foreach (var property in dateTimeProperties)
                {
                    modelBuilder.Entity(entityType.Name).Property(property.Name)
                        .HasConversion(new DateTimeOffsetToBinaryConverter());
                }
            }
        }
    }

Mismanage

I've added the nullable types as well – Blackbeard

Answer 2

Unlike MS SQL Server, SQLite doesn't natively support the following EF data types: Decimal, DateTimeOffset, TimeSpan and UInt64. EF Core can read and write values of these types, and querying for equality is also supported. Other operations, however, like comparison and ordering will require evaluation on the client.

Fortunately, since EF Core 7.0, you can define mapping configurations for these CLR types by overriding ConfigureConventions on your type derived from DbContext (pre-convention model configuration):

protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
    if (!Database.IsSqlite()) return;

    configurationBuilder
        .Properties<decimal>()
        .HaveConversion<double>();

    configurationBuilder
        .Properties<DateTimeOffset>()
        .HaveConversion<DateTimeOffsetToStringConverter>();

    configurationBuilder
        .Properties<TimeSpan>()
        .HaveConversion<TimeSpanToStringConverter>();
}

Find more value converters here.

Phyllisphylloclade

Answer 3

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?

Inland

SQLite has no decimal type, only FLOAT. It's a very limited database, with only four data types. You shouldn't create such in the first place. ORMs arent' SQL replacements, they are meant to Map Objects to Tables. They aren't meant for complex reporting queries like this one. – Leathaleather

You could create different views for different databases and map your entities (or rather, keyless entities) to the view. The view's query will probably be a lot simpler and faster than the SQL query EF will generate by the LINQ query you used to ..... approximate LEFT JOINs and ISNULL – Leathaleather

@PanagiotisKanavos Thanks for the heads up. I use SQLite for my tests only and the main database provider of the project is SQL Server. If I can avoid some known limitations EF creates pretty much the same SQL query than I would FOR BOTH of my database contexts (production and tests). I had to write some views however for performance reasons but this means that I have to write it for both database providers. So EF is a huge help in this case, too. – Inland

Answer 4

Or you can query all the data first and process it locally instead of needing to translate to sql

var result = _context.Votes
    .Where(x => x.Id == id)
    .Select(x => new VotePublicViewModel
    {
        Title = x.Title,
        Deadline = x.Deadline,
        IsMultiple = x.IsMultiple,
        IsPublish = x.IsPublish,
        VoteItems = x.VoteItems
            .Select(item => new VotePublicViewModel.Item
            {
                Id = item.Id,
                ItemName = item.ItemName,
                Count = item.Count,
                // notice: not supported sqlite
                // Percentage = item.Count == 0 ? "0%" :
                //     (item.Count / (decimal)x.VoteItems.Where(v => v.Count != 0).Sum(v => v.Count)).ToString("p"),
            })
    })
    .SingleOrDefault();

if (result != null)
{
    foreach (var item in result.VoteItems)
    {
        item.Percentage = item.Count == 0
            ? "0%"
            : (item.Count / (decimal) result.VoteItems.Where(v => v.Count != 0).Sum(v => v.Count)).ToString("p");
    }
}

Haskel

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Vladamir