Question 160587 in ASP.NET Question posted in
The official documentation can be found here.

Can Entity Framework GroupBy & sum over 3 tables in Asp.net?

I wrote a SQL query to get a overview of sales revenue per car manufacturer.

Simple enough:

select 
    sum(s.Price * s.NumberOfCars) as Revenue, m.Name
from
    Sale as s
join 
    Car as c on c.Id = s.CarId
join 
    Manufacturer as m on m.Id = c.ManufacturerId
group by 
    m.Name

That returns the following output:

enter image description here

Does anybody have an idea how to accomplish that with Entity Framework?

I tried this Linq:

 var salez = from s in _context.Sale
             join c in _context.Car on s.CarId equals c.Id
             join m in _context.Manufacturer on c.ManufacturerId equals m.Id
             group m by m.Name into g
             select new Manufacturer()
                        { 
                            Name = g.Key,
                            Revenue = g.Sum(s => s.)
                        };

I somehow can’t build the sum, as the table "s" is not available in the group "g".

I have also found quite similar questions, but none covered this case.

Similar question:

Group Multiple Tables in LINQ

Further, I have tried the follwing:

SaleGroupManu = gret
    .GroupBy(s => s.Car)
    .Select(sc => new Car()
    {
        Umsatz = sc.Sum(s => s.Umsatz),
        Manufacturer = sc.Key.Manufacturer
    }).GroupBy(sc => sc.Manufacturer)
    .Select(scm => new Manufacturer()
    {
        Name = scm.Key.Name,
        Umsatz = scm.Sum(s => s.Umsatz)
    }).ToList();

But I don’t find it very elegant, as I have to group by Car first, whilst with SQL, I can group by Manufacturer directly. It works, but is there any better idea?

I have tried raw sql, but it gave me errors, such as "The required column ‘Id’ was not present in the results of a ‘FromSql’ operation"

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I have eventually worked it out, with the "!."-Syntax I didn't know about.

    SaleGroupManu = sale
    .Include(s => s.Car)
    .Include(s => s.Car!.Manufacturer).ToList()
    .GroupBy(s => s.Car!.Manufacturer)
    .Select(ms => new Manufacturer()
    {
        Name = ms.Key!.Name,
        Revenue = ms.Sum(s => s.Revenue)
    }).ToList();

  2. 0

    You can pass your working sql query directly into EF via DbSet.SqlQuery() as documented here:
    https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.dbset.sqlquery?view=entity-framework-6.2.0

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search