EF Core sum on nested collection's properties does not work

c# entity-framework-core

Question

My database has the following structure: One User can have many Accounts. One Account can have many Transfers.

This is the map configuration. I want to store in my DTO information about each user and his total savings in every account. (the code below represents one part of the whole configuration).

this.CreateMap<User, UserTestModel>()
    .ForMember(
        utm => utm.AccountsSavings, 
        options => options.MapFrom(u => u.Accounts
            .Select(a => a.Transfers.Sum(t => t.Amount))
            .ToList()));

My query to the database (I am using EF Core 3.1.3 with Sqlite database) looks like this:

var resultSet = await this._dbContext.Users
    .ProjectTo<UserTestModel>(this.Mapper.ConfigurationProvider)
    .ToListAsync(cancellationToken)
    .ConfigureAwait(false);

However the following exception is thrown.

*

System.InvalidOperationException : The LINQ expression 'DbSet .Where(t => EF.Property>((EntityShaperExpression: EntityType: Account ValueBufferExpression: (ProjectionBindingExpression: EmptyProjectionMember) IsNullable: False ), "Id") != null && EF.Property>((EntityShaperExpression: EntityType: Account ValueBufferExpression: (ProjectionBindingExpression: EmptyProjectionMember) IsNullable: False ), "Id") == EF.Property>(t, "AccountId")) .Sum(t => t.Amount)' 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().

*

Is this a problem with my code, or rather with some of the frameworks I am using at the moment (AutoMapper or EF Core)?

Edit 1:

I decided to try to manually gather that information from the User entities but that code leads to the same exception which means that it is not Automapper's fault:

var resultSet = await this._dbContext.Users
    .Select(u => u.Accounts.Select(a => a.Transfers.Sum(t => t.Amount)).ToList())
    .ToListAsync(cancellationToken)
    .ConfigureAwait(false);

Edit 2:

Created issue at EntityFrameworkCore's GitHub: https://github.com/dotnet/efcore/issues/20455

1
2
3/29/2020 6:16:20 PM

Accepted Answer

I was able to reproduce it with SQLite provider and Amount type being decimal (works fine with SqlServer provider).

So you seem to be hitting the following SQLite Query Limitations from EF Core documentation:

SQLite doesn't natively support the following data types. EF Core can read and write values of these types, and querying for equality (where e.Property == value) is also supported. Other operations, however, like comparison and ordering will require evaluation on the client.

  • DateTimeOffset
  • Decimal
  • TimeSpan
  • UInt64

As workaround, consider taking the advice from the docs:

The Decimal type provides a high level of precision. If you don't need that level of precision, however, we recommend using double instead. You can use a value converter to continue using decimal in your classes.

with sample

modelBuilder.Entity<MyEntity>()
    .Property(e => e.DecimalProperty)
    .HasConversion<double>();           
2
3/29/2020 4:21:21 PM


Related Questions





Related

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