Problem with converting raw SQL to Entity Framework, order by, count

Question

I have a trouble with converting this SQL query to Entity Framework:

SELECT Products.Name, COUNT(*) 
FROM OrdersProducts 
JOIN Products on OrdersProducts.ProductId = Products.Id 
GROUP BY Products.Id 
ORDER BY COUNT(*) DESC

Tried hard but with no results. I don't have any idea how to make it work.

The answer is provided in another post [here](https://stackoverflow.com/a/9173783/7855321) — Mahyar Mottaghi Zadeh, Jan 19 '21 at 17:23
You should list your models and their relationships and what data you need to find. — Yinqiu, Jan 20 '21 at 05:56

score 0 · Accepted Answer · answered Jan 21 '21 at 10:02

0

If your entity model relations allows it, you could try something like that :

dbcontext.Products
.Include(orders=> orders.OrderProducts)
.Select(x => new { Name = x.Name, Count = x.OrderProducts.Count })
.OrderByDescending(x => x.Count)
.ToList();

I think the SQL generated by EF could be near of your query...

answered Jan 21 '21 at 10:02

acinace

96
4

It works good, but how can I order and group without displaying `Count`? Now I have to display only `Name`, because of my model doesn't have `Count` field it shows an error, Cannot convert to anonymous type. – adriannowaq Jan 22 '21 at 14:26
1

try to add your custom Select after OrderBy. for example `.Select(x=>x.Name)`. – acinace Jan 23 '21 at 10:17

Problem with converting raw SQL to Entity Framework, order by, count

1 Answers1