Linq -- Join on joined table's primary key

Question

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)

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

SELECT 
c.TxCollectionName
,s.SprintNumber
,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
GROUP BY 
c.TxCollectionName
, 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 1

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() };