Cannot call Method in LINQ Expression

Question

I am writing an application in C# using EFCore.

I have the Entities Customer, Order, OrderItem and Product. OrderItem is a associative table connecting Order with Product so an order can have multiple products.

Order contains a reference to customer.

OrderItem contains a reference to Product and Order.

By reference I mean a foreign-key constraint.

The problem is that when I try to execute the following method, I get the following error:

public static List<SalesStatistic> GetInvoices()
{
    using ApplicationDbContext context = new ApplicationDbContext();

    return context.Customers.Select(c => new SalesStatistic()
    {
        FirstName = c.FirstName,
        LastName = c.LastName,
        TotalPrice = context.Orders.Where(o => o.CustomerId == c.Id).Sum(oo => GetSalesPerOrder(oo.Nr))
    }).ToList();
}

System.InvalidOperationException: The LINQ expression 'DbSet<Order>()
    .Where(o => o.CustomerId == EntityShaperExpression: 
        Core.Entities.Customer
        ValueBufferExpression: 
            ProjectionBindingExpression: EmptyProjectionMember
        IsNullable: False
    .Id)
    .Sum(o => Repository.GetSalesPerOrder(o.Nr))' could not be translated. Additional information: Translation of method 'Persistence.Repository.GetSalesPerOrder' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. 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.

The method GetSalesPerOrder works as I have Unit-Tests set up for these methods.

public static double GetSalesPerOrder(string orderNr)
{
    using ApplicationDbContext context = new ApplicationDbContext();

    return context.Orders.Include(o => o.OrderItems).Where(o => o.Nr == orderNr).First().OrderItems!.Sum(oi => context.OrderItems.Include(o => o.Product).Where(oii => oii.Id == oi.Id).First().Product.Price * oi.Amount);
}

I tried to modify GetInvoices so it doesn't call GetSalesPerOrder and then no exception was thrown.

I want to know what I am doing wrong in the above code.

The `GetSalesPerOrder(oo.Nr)` is a method. EF is trying to convert this into a SQL query, but it has no idea what to do with the method, since that can't be translated to SQL. — rotgers, Nov 02 '22 at 12:45

score 2 · Answer 1 · answered Nov 02 '22 at 12:50

2

Because GetSalesPerOrder() can't be translated into SQL. It's a custom method you wrote in C#. What you can do is move that logic into the expression tree:

return context.Customers.Select(c => new SalesStatistic()
{
    FirstName = c.FirstName,
    LastName = c.LastName,
    TotalPrice = context.Orders
        .Where(o => o.CustomerId == c.Id)
        .Sum(oo => context.Orders
            .Include(o => o.OrderItems)
            .Where(o => o.Nr == oo.Nr)
            .First()
            .OrderItems!.Sum(oi => context.OrderItems
                .Include(o => o.Product)
                .Where(oii => oii.Id == oi.Id)
                .First()
                .Product.Price * oi.Amount))
}).ToList();

Alternatively, if you want to refactor this into a separate method, that method would need to at least be a Func<> (or more likely an Expression<Func<>>) in order for Entity Framework to translate it into SQL. Probably the easiest way to do that would be to rely on the IDE's refactoring tools to extract a method by highlighting the entire expression in the .Sum() operation and extracting that.

answered Nov 02 '22 at 12:50

David

208,112
36
198
279

When I try to execute your code, (I tried the same earlier) I get an error message saying: `Microsoft.Data.SqlClient.SqlException: Cannot perform an aggregate function on an expression containing an aggregate or a subquery. Cannot perform an aggregate function on an expression containing an aggregate or a subquery.` – trauni Nov 02 '22 at 12:56
@trauni: I can't really speak to the structure of the data or the goal of this operation, only to the original error in the question. But that new error implies to me that the query you're attempting to execute against the data isn't valid in the first place. Abstracting parts of that query into other methods won't change that. You're probably going to want to start re-defining this a piece at a time, starting with the simplest parts of the query and building up from there. Note also that this code shouldn't be copied/pasted, as it was created from code from the question and was never tested. – David Nov 02 '22 at 13:02
I fixed it by executing the linq statements on the client by calling `.AsEnumerable().ToList()` on `context.Customers` at the beginning. – trauni Nov 02 '22 at 13:21
1

@trauni: That may make the error stop happening, but be aware of the consequences of that "fix". Now *the entire `Customers` table* will materialize in the application's memory and all operations will be performed in the application, not in the database. Which means you can then start using your custom C# methods again. But it also means drastic performance problems as that table grows. – David Nov 02 '22 at 13:28

score 1 · Answer 2 · answered Nov 02 '22 at 16:41

If you care about performance, think in SQL. Your query can be simplified and improved a lot.

var query = 
    from c in context.Customers
    join o in context.Orders on c.Id equals o.CustomerId
    from oi in o.OrderItems
    group new { oi.Amount, oi.Product.Price } by new { c.Id, c.Firstname, c.LastName } into g
    new SalesStatistic
    {
        FirstName = g.Key.FirstName,
        LastName = g.Key.LastName,
        TotalPrice = g.Sum(x => x.Price * x.Amount)
    };

Cannot call Method in LINQ Expression

2 Answers2