SQL Query in Linq with HAVING SUM

Question

I have a small database example with three tables: Cities, Country and Geo_Languages (Languages spoken in the countries). Now I want to get the most spoken languages of all cities in the world with a population of at least one million people.

Here is the SQL-Query:

SELECT SUM(c.population) AS totalPop, g.name_en
FROM cities c,  country cy,  geo_languages g
WHERE   c.country_code = cy.id AND 
    cy.id = g.code2l
GROUP BY g.name_en
HAVING SUM(c.population) > 1000000
ORDER BY totalPop DESC;

Here the Linq-Query so far:

Var query =
    from c in db.City
    join country in db.Country on c.country_code equals country.id
    join languages in db.geo_languages on country.id equals
        languages.code2l    
    group languages by languages.name_en    
    select new{
        totalPop = c.Sum (c => c.population)    
    };

I just don't know how to convert the HAVING SUM and the ORDER BY into Linq. I'm thankful for any help.

Answer 1

Try that one:

var query =
    from c in db.City
    join country in db.Country on c.country_code equals country.id
    join languages in db.geo_languages on country.id equals
        languages.code2l    
    group c by languages.name_en into g
    where g.Sum(x => x.population) > 1000000
    select new {
        totalPop = g.Sum(x => x.population)    
    };