0

What is the LINQ equivalent of

SELECT primaryID, name, description, unit, SUM(price)
FROM Product
JOIN so that same Products are returned multiple times
GROUP BY primaryID

I know that it is possible to make groupBy queries using LINQ, like this:

(from item in dbContext.products
 join some join statement that will make products return multiple times
 group item by item.primaryID into g
 select new {id = key, summedPrice = g.Sum(x => x.price)}
)

But this way, i only get the ID and the aggregate. How do i get the other fields aswell?

I guess i could make my key a annon class that has all the columns i need, but i'm note sure if that would impact perfarmance, like this:

group item by new { item.primaryID, item.name, item.description...} into g
M364M4N cro
  • 680
  • 2
  • 10
  • 23
  • most sql will not allow the sql shown there - you need an function for items not in the group by – Hogan Dec 18 '18 at 18:43
  • I seems to work fine with MySQL – M364M4N cro Dec 18 '18 at 18:48
  • that is why I said most -- MySQL is doing an implicit FIRST() here -- so the same will work in the Linq – Hogan Dec 18 '18 at 18:49
  • MySQL seems to be the only from popular SQL dialects which supports that. Since LINQ and EF Core queries are general, such syntax is not supported. Basically you have to group by all the fields you want tp include, or do a separate join from the grouped query to the primary table. – Ivan Stoev Dec 18 '18 at 18:51
  • As @Hogan said, `name = g.First().Name` let's you access the other fields dependent on `primaryID` since your table must be non-normalized. I added this to my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786). – NetMage Dec 18 '18 at 18:52
  • @IvanStoev -- you don't have to do that -- you can use First on the group or Last() or whatever selector you want. You could even use ToArray() on the values and return a list of all of them. – Hogan Dec 18 '18 at 18:55
  • 1
    `name = g.First().Name` is not really optimal, because the outputed SQL is pretty weird if i try do do joins and access columns from multiple tables – M364M4N cro Dec 18 '18 at 18:58
  • @Hogan There is no LINQ equivalent of LAST(column), so the generated SQL would be quite inefficient (with many correlated subqueries - one for each field). – Ivan Stoev Dec 18 '18 at 18:58
  • but grouping by all columns i need should work? Atleast without major performance hits? – M364M4N cro Dec 18 '18 at 18:59
  • It should work but performance hits really depends on your SQL engine - for example, if there isn't an index across all the columns, adding unneeded columns may cause the engine to not use the index at all, or be less efficient in using it. – NetMage Dec 18 '18 at 19:04
  • @IvanStoev -- oh I guess I had a helper function that did a reverse and then a first. In this case I think if the order matters then a join back to the table as ivan suggests it the best plan – Hogan Dec 18 '18 at 19:04
  • @Hogan LAST or FIRST doesn't matter - both are not supported at *column level*. MAX/MIN eventually could be used as replacement though - not sure if supported for string data, but otherwise yeah, they can be used to avoid joins/subqueries. – Ivan Stoev Dec 18 '18 at 19:07
  • What exactly is the standard procedure in this case? I cant be the only one who needs this. – M364M4N cro Dec 19 '18 at 07:22

0 Answers0