CODEWITHSUNDEEP
c#sql-serverentity-frameworklinqiqueryable
Stijn Van Antwerpen
Stijn Van Antwerpen

Reputation: 1988

Property Sum translated to sql on IQueryable with linq to entities

Given two classes generated code first with EF, with a parent-child relation:

class Parent {
    //...
    public virtual ICollection<Child> Children
}

class Child {
   //...
   public decimal Amount{ get; set; }
   public decimal UnitPrice { get; set; }
}

I would like to create a property Total on Parent, something like

decimal Total => Children.Sum(child => child.UnitPrice * child.Amount)

But if I do this like that, and then do

var list = ctx.Parents
                  .Where(p => p.Total > 1000)
                  .Select(p => new {
                       p.Id, 
                       p.Total,
                       Count = p.Children.Count });
foreach(var item in list){
      Console.WriteLine($"Id: {item.} ...");
}

I got an error that says

An unhandled exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll

Additional information: The specified type member 'Total' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

While, at my opinion, it should not be that hard for EF to generate an query using SUM(UnitPrice * Amount) as Total I can't get it working.

I have already tried it with a static expression like 

  public static Expression<Func<Parent, decimal>> GetTotal()
  {
      return p=> p.Children.Sum(line => line.ItemQty * line.UnitPrice);
  }

While it should be acceptable to do this calculation just in code. I want to use this example to learn more about how to use IQueryable.

A Little Succes

Given the following static property on Parent

  public static Expression<Func<Parent, decimal?>> Total =>
        p=> (from c in p.Childeren
             select c.UnitPrice * c.ItemQty).Sum();

And then doing 

var list = ctx.Parents.Select(Parent.Total);

I got a list containing all the totals, and I see that EF generated the following query

SELECT
    (SELECT
        SUM([Filter1].[A1]) AS [A1]
        FROM ( SELECT
            [Extent2].[UnitPrice] *  CAST( [Extent2].[Amount] AS decimal(19,0)) AS [A1]
            FROM [dbo].[Child] AS [Extent2]
            WHERE [Extent1].[Id] = [Extent2].[ParentId]
        )  AS [Filter1]) AS [C1]
    FROM [dbo].[Parent] AS [Extent1]

So, it EF is indeed capable of translating the Sum() method to SQL. now I only need to use it in the Where().

Upvotes: 0

Views: 1013

Answers (1)

Flater
Flater

Reputation: 13773

Calculated properties are only known in the class, not in the database. You'll need to instantiate your objects (into classes) before you can access these calculated properties.

It's not that hard to get this to work:

var list = ctx.Parents
              .Where(p => p.Total > 1000)
              .ToList() //This instantiates your data into objects
              .Select(p => new {
                   p.Id, 
                   p.Total,
                   Count = p.Children.Count });

This may require an include statement, depending on whether you've got lazy loading or not:

var list = ctx.Parents
              .Include(p => p.Children) //Children will be populated upon instantiation
              .Where(p => p.Total > 1000)
              .ToList() //This instantiates your data into objects
              .Select(p => new {
                   p.Id, 
                   p.Total,
                   Count = p.Children.Count });

While, at my opinion, it should not be that hard for EF to generate an query using SUM(UnitPrice * Amount) as Total I can't get it working.

There's more to it than you're mentioning.

The SUM of what? Not of the current result (which is the Parents table), you want a sum of a calculation done on the Children table (grouped by ParentId), which is a completely different dataset than the one you've been working with so far.

This is why EF won't do what you want it to do. It's trying to decide on the SELECT part of the query, but your expectation requires a much more complex query body; one which joins children and parents together in order to perform a calculation on the children and then sum those outcomes into a single value.

If you try writing the SQL query yourself, you'll see that it's much more complicated than just doing SUM(UnitPrice * Amount) as Total.

Upvotes: 1

Related Questions