How to convert count in linq?

Question

I have a situation where I want to count the total transaction of the customer. I can do it in a mysql query but I don't know to do it in linq sql.

select c.name, count(r.custid) 
from receipt r left join in cust c on (c.custid=r.custid) 
group by r.custid

How can I convert it in linq sql?

What are you trying to do exactly, I can not understand. You ask about linq sql but your sample query is a raw sql ? Do you want to know linq equivalent of that query ? — Cihan Uygun, Jan 20 '16 at 08:14
I want to display both name and the count expected result. name || Count david || 3 solomon || 4 joseph || 1 — Jerrime25, Jan 20 '16 at 08:18
@AhmedGalal: That doesn't look like a close enough duplicate to me, given the joining part in this question. I'm not saying a duplicate *hasn't* been asked - but I don't think that's it. — Jon Skeet, Jan 20 '16 at 08:19
OK , look at this : http://stackoverflow.com/questions/12089870/linq-join-and-count @JonSkeet — Ahmed Galal, Jan 20 '16 at 08:26
@AhmedGalal: Yup, that's a much more reasonable dupe - although I don't like the answer much, and will add another in a bit :) — Jon Skeet, Jan 20 '16 at 08:27

score 3 · Accepted Answer · answered Jan 20 '16 at 08:16

I wouldn't start with the SQL... I'd start by thinking of what you're counting, which is basically the number of receipt records in the group which matches the customer. It sounds like you want something like:

var query = from customer in db.Customers
            join receipt in db.Receipts
              on customer.CustomerId equals receipt.CustomerId
              into customerReceipts
            select new { customer.Name, Count = customerReceipts.Count() };

Note that unlike many "left join" examples in LINQ, we don't use customerReceipts.DefaultIfEmpty() because we only want the count - which should be 0 if there are no receipts for that customer.

can you help me in this follow up problem? http://stackoverflow.com/questions/35641821/how-to-convert-sql-count-order-by-to-linq-count-order-by — Jerrime25, Feb 26 '16 at 06:26

How to convert count in linq?

1 Answers1