LINQ to SQL / entities join / group by / count

Question

I have two tables, one for jobs, and one for the names of industries (e.g. automotive, IT, etc).

In SQL I would just do:

    SELECT industryName, count(*)
    FROM jobs
    JOIN industry
    ON jobs.industryId = industry.id
    GROUP BY industryName

In LINQ I have the following, but it's three separate statements and I'm pretty sure this would be doable in one.

    var allIndustries =
        from j in dbConnection.jobs
        join i in dbConnection.industries on j.industryId equals i.id
        select i.industryName;
    var industriesWithCount =
        from i in allIndustries
        group i by i into iGrouped
        select new { Industry = iGrouped.Key, Count = iGrouped.Count() };
    var industries = new Dictionary<string, int>();
    foreach (var ic in industriesWithCount)
    {
        industries.Add(ic.Industry, ic.Count);
    }

Is there a way to make this simpler / shorter?

Answer 1

from j in dbConnection.jobs
join i in dbConnection.industries on j.industryId equals i.id
group new {i, j} by new {
  i.Name
} into g

select new {
  industryName = g.Key.Name,
  jobscount = g.Count()
}