how to send variable of type Func<T,decimal> to sum from IQueryable using ef core

user_11954502 0
Viewed 54

I need to send a lamda in function parameter but when i do that i've error say

Expression of type 'System.Func2[KokazGoodsTransfer.Models.Receipt,System.Decimal]' cannot be used for parameter of type 'System.Linq.Expressions.Expression1[System.Func2[KokazGoodsTransfer.Models.Receipt,System.Decimal]]' of method 'System.Decimal Sum[Receipt](System.Linq.IQueryable1[KokazGoodsTransfer.Models.Receipt], System.Linq.Expressions.Expression1[System.Func2[KokazGoodsTransfer.Models.Receipt,System.Decimal]])' (Parameter 'arg1')'

example of code

var sum = await context.Receipts.GroupBy(c => c.ClientId).Select(c => new { c.Key, Sum = c.Sum(c=>c.Amount) }).ToListAsync();

it's work fine

but when i try this i see the error

Func<Receipt, Decimal> func = c => c.Amount;
var sum = await context.Receipts.GroupBy(c => c.ClientId).Select(c => new { c.Key, Sum = c.Sum(func) }).ToListAsync();

thank you

entity-framework-core linq c#
2 Answers

EF usually requires an expression tree to be able to translate the code into actual SQL query.

You can try something like this (though not tested, but in some cases such tricks worked as far as I remember):

Expression<Func<Receipt, Decimal>> func = c => c.Amount;
var sum = await context.Receipts
    .GroupBy(c => c.ClientId)
    .Select(c => new { c.Key, Sum = c.AsQueryable().Sum(func) })
    .ToListAsync();

Otherwise you maybe will need either to build select statement expression manually (which is not that easy) or look into 3rd party library like LINQKit which allows to use Func's with some magic. Something along this lines:

Expression<Func<Receipt, Decimal>> func = c => c.Amount;
var sum = await context.Receipts
    .AsExpandable() // or WithExpressionExpanding on the context DI set up
    .GroupBy(c => c.ClientId)
    .Select(c => new { c.Key, Sum = c.Sum(func.Compile()) })
    .ToListAsync();
user_25012790

You have to use not Func but Expression<Func<Receipt, Decimal>>. But it also will be not translatable without third-party extensions. I would suggest to use LINQKit. It needs just configuring DbContextOptions:

builder
    .UseSqlServer(connectionString) // or any other provider
    .WithExpressionExpanding();     // enabling LINQKit extension

Then your query will work in the following way:

Expression<Func<Receipt, Decimal>> func = c => c.Amount;

var sum = await context.Receipts.GroupBy(c => c.ClientId)
  .Select(c => new { c.Key, Sum = c.Sum(x => func.Invoke(x)) })
  .ToListAsync();
user_106463160
Related