LINQ - 3 sub selects with group by having count

Question

SQL:

SELECT i.name, l.city, COUNT( l.city ) AS num
FROM locality l
JOIN event e ON e.ID_locality = l.ID
JOIN program p ON p.ID_event = e.ID
JOIN interpreter i ON i.ID = p.ID_interpreter
WHERE i.name = 'XXXX'
GROUP BY l.city
HAVING COUNT( l.city) = (
SELECT MAX( num ) 
FROM ( SELECT COUNT( city ) AS num
FROM (SELECT l.city FROM locality l
JOIN event e ON e.ID_locality = l.ID
JOIN program p ON p.ID_event = e.id
JOIN interpreter i ON i.ID = p.ID_interpreter
WHERE i.name = 'XXXX'
GROUP BY l.city) tmp ) tmp2)

LINQ:

var q4 = from l in db.locality
         join e in db.event on l.ID equals e.ID_locality
         join p in db.program on e.ID equals p.ID_event
         join i in db.interpreter on p.ID_interpreter equals i.ID
         where i.name == "XXXX"
         group l by l.city into grp

Hello, I have rly serious problem with this select in LINQ. I wrote this select in SQL but I rly dont know how I can use multi sub select with group by having count... I wanna select city where interpreter "XXXX" acted the most often and also how many times interpreter acted there. I also checked enter link description here but it didnt help me when I was trying use it. I will be sooo happy if someone can help me. For better orientation: databases tables

Sakir SEN · Accepted Answer · 2013-05-05T21:06:27.090

I'm not sure, but I hope you do, something like that.

var query = (from l in db.locality
            join e in db.event on l.ID equals e.ID_locality
            join p in db.program on e.ID equals p.ID_event
            join i in db.interpreter on i.ID equals p.ID_interpreter
            group l by new { i.name, l.city } into g
            where i.name == "XXXX" &&
            g.Count(x => x.city) == (from l in db.locality
                                            join e in db.event on l.ID equals e.ID_locality
                                            join p in db.program on e.ID equals p.ID_event
                                            join i in db.interpreter on i.ID equals p.ID_interpreter
                                            where i.name == "XXXX"                  
                                            group l by l.city into g
                                            select new 
                                            {
                                                cityCount = g.Count(x => x.city)
                                            }).Max()

            select new 
            {
                g.key.name,
                g.key.city,
                maxNum = g.count(x => x.city)
            }).ToList();

Thank u. Ur solution helped me a lot just g.count(x => x.city) = (... doesnt work. I guess there should be g.count(x => x.city) == (... but also subselect need return int number. Cuz g.Count() == here it wants int number. So still need little fix it. However thank you a lot :) — Matwosk, May 05 '13 at 20:52
Hmm. Yes. g.Count (x => x.city) == must be. Unnoticed. g.Count (x => x.city) == cancel later than when the brackets might work. I'm glad it worked. — Sakir SEN, May 05 '13 at 21:04

LINQ - 3 sub selects with group by having count

1 Answers1