SQL query convert it to Linq

Question

select 
    COUNT(prosold.Product_ID) as c,
    prosold.Product_ID,
    ProImg.ImagePath 
from 
    ProductSold as ProSold,
    ProductImages as ProImg  
where 
    ProSold.Product_ID = ProImg.Product_ID  
group by 
    ProSold.Product_ID, ProImg.ImagePath 
order by 
    c desc

As I did it so but not working finally

var result = from p in db.Products
             join c in db.ProductSolds on p.Product_ID equals c.Product_ID into j1
             from j2 in j1.DefaultIfEmpty()
             group j2 by p.Product_ID into grouped
             select new { ParentId = grouped.Key, Count = grouped.Count(t => t.Product_ID != null) };

I'm voting to close this question as off-topic because it's a "please send me the code" question — KristoferA, Jun 07 '17 at 16:58
from p in db.Products join c in db.ProductSolds on p.Product_ID equals c.Product_ID into j1 from j2 in j1.DefaultIfEmpty() group j2 by p.Product_ID into grouped select new { ParentId = grouped.Key, Count = grouped.Count(t => t.Product_ID != null) }; — Raheem Khan Dawar, Jun 07 '17 at 17:20
As i did it so but it is not working as per required results that's why i am asking for help — Raheem Khan Dawar, Jun 07 '17 at 17:21
The SQL and the Linq are for different queries (i.e., they use different tables) — James Curran, Jun 07 '17 at 18:38

score 1 · Answer 1 · answered Jun 08 '17 at 00:02

1

var ans = from ProSold in ProductSold
          join ProImg in ProductImages on ProSold.Product_ID equals ProImg.Product_ID
          group new { ProSold, ProImg } by new { ProSold.Product_ID, ProImg.ImagePath } into ppg
          let c = ppg.Count()
          orderby ppg descending
          select new { c, ppg.Key.Product_ID, ppg.Key.ImagePath };

answered Jun 08 '17 at 00:02

NetMage

26,163
3
34
55

I have consolidated my notes about SQL to LINQ in my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786). – NetMage Oct 18 '18 at 19:05

SQL query convert it to Linq

1 Answers1