Count number of times a record is refrenced in another table using LINQ-To-SQL

Question

I have a SQL Server table named DeficiencyTag and its PK is an integer named DeficiencyTagID. It will have a couple hundred records. There's another table named Deficiency that references a particular DeficiencyTagID. There will be millions of these eventually.

What I need to do is make a list of the top 10 DeficiencyTag records based on the number of times each DeficiencyTagID is referenced from the Deficiency table.

The only way I can think of is having to manually loop through each DeficiencyTag record and count all Deficiency records that reference it. That sounds really slow and messy.

Is there a better way? I'm using LINQ-To-SQL for my DB interaction.

Answer 1

Assuming there is a relationship in the designer between DeficiencyTag and Deficiency:

var query = dataContext.DeficiencyTags
  .OrderByDescending(dt => dt.Deficiencies.Count())
  .Take(10);

Answer 2

In response to your comment

var query = (from dt in dataContext.DeficiencyTags
             let count = dt.Deficiencies.Count
             orderby count descending
             select new { DeficiencyTag = dt, Count = count}).Take(10);