Convert Count ... Group By From SQL Query To LINQ

Question

Please help me convert the MSSQL below to LINQ.

SELECT A.id, A.type, COUNT(IIf(B.BookId IS NOT NULL, 0, null)) AS TotalCount
FROM Store A
LEFT JOIN Book B ON A.id = B.id
GROUP BY A.id, A.type;

I currently have this LINQ code:

from a in Store
join b in Book on a.id equals b.id into c
from d in c.DefaultIfEmpty()
group a by new
{
    a.id,
    a.type
} into g 
select new
{
    StoreId = g.Key.id,
    StoreType = g.Key.type,
    TotalCount = g.Key !=null?g.Count():0
}

I think I'm missing something important.

Please describe the issue with your LINQ query in more detail. Does your query compile? If it compiles what happens when it executes? If not, what is the error? — Mike Zboray, May 20 '13 at 18:11
Hi, welcome at StackOverflow. To get the most out of StackOverflow, please pay close attention to your question _right after_ asking it. Most questions get most views in the first few minutes. So if someone asks for clarification _be quick_ to provide it by editing your post, otherwise people will lose interest and move on. — Gert Arnold, May 20 '13 at 20:32
Hi, sorry for the late reply. There's nothing wrong with my LINQ query but that's not my expected result. However, **D Stanley** has been pointed out the problem. Thanks you everone... — user2402624, May 21 '13 at 02:09

score 1 · Accepted Answer · answered May 20 '13 at 18:42

1

Since you're not trying to extract any values from the Book part of the join you don't need DefaultIfEmpty(), and since the join is a "group join" as a result of the into clause you don't need to group again:

from a in Store
join b in Book on a.id equals b.id into c
select new
{
    StoreId = a.id,
    StoreType = a.type,
    TotalCount = C.Count()
}

answered May 20 '13 at 18:42

D Stanley

149,601
11
178
240

Thanks for the answer, u save me for a day. If i want to add in a WHERE CLAUSE like this - `WHERE (B.RackId = 4 OR B.RackId IS null)` then how to do it on LINQ. I'm new to LINQ, that's why i'm not so understand the infrastructure & concept of the LINQ. Do u have any recommended article or tutorial for me?? – user2402624 May 21 '13 at 02:29
ok, I found the answer - SQL Query `WHERE (B.RackId = 4 OR B.RackId IS null)` LINQ query `TotalCount = c.Count(e => e.RackId == 4 || e.RackId == null)` – user2402624 May 21 '13 at 02:57

Convert Count ... Group By From SQL Query To LINQ

1 Answers1