AwonDanag AwonDanag - 17 days ago 5
Vb.net Question

Simple SQL Count to LINQ?

I've spent the last two hours or so researching this. Is new to LINQ btw.
How do I get an SQL like this to a LINQ query?

SELECT r.id, name, designation, COUNT(a.id)
FROM region r
LEFT JOIN area a on a.region_id = r.id
GROUP BY r.id, name, designation


My alternative of course is to place this in a
View
(which I can figure out) but I'd like this to be added to my knowledge. Thanks!

P.S. Either VB or C# will do but I'd prefer the former.

Answer

I think it would be like this:

var result =
            from r in regions
            join a in areas on r.id equals a.region_id into subvalues
            from _ in subvalues.DefaultIfEmpty()
            select new { id = r.id, name = r.name, designation = r.designation, count = subvalues.Count() };
Comments