LINQ to SQL Joining Entities

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 or shorter?

Answer 1

Your j and i variables are pretty plain and won't make this code fun to maintain (especially if it were a big block of code)

You should use more descriptive variables.

you should create a collection that groups the Jobs by their Industry and then select from that collection the industry name and the count of jobs in the collection with that name.

from job in dbConnection.jobs
join industry in dbConnection.industries on job.industryId equals industry.id
group new {industry, job} by industry.Name
into jobsByIndustry

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