CODEWITHSUNDEEP
c#sql-serverlinqgroup-byleft-join
Vildan
Vildan

Reputation: 1994

SQL to LINQ. LEFT OUTER JOIN and GROUP BY together

I have table "Clients" and columns "ID", "Country", "Sex". What I want is to get total amount of clients grouped by the country field and total males and females in the same query. I've done it in sql query. it works. But couldn't find how to realize it in LINQ.

SELECT  c.country, COUNT(c.id) AS TotalClients, 
ISNULL(max(c2.total),0) AS TotalMales,
COUNT(c.id)-ISNULL(MAX(c2.total),0) AS TotalFemails,

FROM Clients c
LEFT OUTER JOIN(
SELECT country, count(id) AS total FROM Clients 
WHERE sex='male'
GROUP BY country
) c2 
ON c2.country = c.country
GROUP BY c.country
ORDER BY c.country

Any suggestions?

Upvotes: 0

Views: 2025

Answers (2)

Amy B
Amy B

Reputation: 110101

Consider using a simpler query:

SELECT
  c.Country,
  SUM(CASE WHEN c.Sex = 'Male' THEN 1 ELSE 0 END) as TotalMales,
  SUM(CASE WHEN c.Sex = 'Female' THEN 1 ELSE 0 END) as TotalFemales,
  COUNT(*) as TotalClients
FROM Clients c
GROUP BY c.Country
ORDER BY c.Country

Which translates roughly as:

from c in Clients
group c by c.Country into g
order by g.Key
select new {
  Country = g.Key,
  Males = g.Count(y => y.Sex == "Male"),
  Females = g.Count(x => x.Sex == "Female"),
  Total = g.Count()
}

Upvotes: 0

Vildan
Vildan

Reputation: 1994

var query = (from c in db.Clients
            let TotalMales = (
            from c2 in db.Clients.Where(a=>a.Sex=='male') where c.Country=c2.Country select c2).Count()
            group c by new {c.Country, TotalMales}
            into g
            select new {
                g.Key.Country,
                TotalClients = g.Count(),
                TotalMales = g.Key.TotalMales,
                TotalFemales = g.Count()-TotalMales
            }).OrderBy(s=>s.Country);

Upvotes: 2

Related Questions