Daniel Kelley Daniel Kelley - 3 years ago 101
C# Question

Linq -- Join on joined table's primary key

Coming from T-SQL, I am attempting to work with a basic dataset in an example ASP.Net mvc (c#) program.

I have three tables as shown in the photo(linked) below:

  1. Collections (PK IdCollection)

  2. Sprints (PK IdSprint, FK IdCollection)

  3. DeployDocuments (PK IdDeployDocuments, FK IdSprint)

Table relationship diagram

In my asp.net mvc controller, I would like to pass the linq equivalent dataset of this simple query to the view:

,COUNT(dd.IdDeployDocument) [NumProjects]
FROM Collections AS c
JOIN Sprints AS s
ON s.IdCollection = c.IdCollection
LEFT JOIN DeployDocuments AS dd
ON dd.IdSprint = s.IdSprint
, s.SprintNumber;

I cannot, for the life of me, figure out how to do this!
As soon as I try to create a second join in linq (let alone a left join).

I was previously just using:

var CollectionSprints = db.Collections.Include(d => d.Sprints)

But I need the Sum of all projects (deployDocuments) as well, so now I'm trying to haggle together a query like so:

var query = from Collections in db.Collections
join Sprints in db.Sprints on Collections.IdCollection equals Sprints.IdCollection
join DeployDocuments in db.DeployDocuments on DeployDocuments.IdSprint equals Sprints.IdSprint

but as soon as I get down to the second join it's throwing off errors, is there a limitation of linq I should read up on? Is there a completely different approach that I should take to solve this problem? Or should I just GTFO and take more courses on C#

Answer Source

The Linq left join looks a bit different from an SQL left join, so it can be a little confusing. This SO answer shows an easy way to write Linq left-joins. The .DefaultIfEmpty() makes the second join a left join.

Here's what I came up with:

var result = (
    from c in Collections
    from s in Sprints.Where(s => s.IdCollection == c.IdCollection)
    from dd in DeployDocuments.Where(dd => dd.IdSprint == s.IdSprint).DefaultIfEmpty()
    select new { c, s, dd } )
.GroupBy(g => new { g.c.TxCollectionName, g.s.SprintNumber })
.Select(s => new { s.Key.TxCollectionName, s.Key.SprintNumber, NumProjects = s.Count() };
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download