LINQ JOIN and OUTER JOIN - How to turn SQL into LINQ expression <p>I would like to turn the following SQL query into a LINQ expression (using Entity Framework 6.1). Thus far I have been unable find an acceptable LINQ expression that produces similar results. Any help turning this simple SQL statement into a LINQ express would be appreciated.</p> <pre><code>SELECT AAG.Id AS GroupId, A.Id AS ActivityId, A.Title As Title, CASE WHEN AA.CompletedOn IS NULL THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Completed, COALESCE(AAG.PointValue, 0) + SUM(COALESCE(AQ.PointValue, 0)) AS PointTotal FROM ActivityAssignmentGroup AAG INNER JOIN ActivityAssignment AA ON AA.GroupId = AAG.Id INNER JOIN Activity A ON AA.ActivityId = A.Id LEFT OUTER JOIN ActivityQuestion AQ ON AQ.ActivityId = A.Id WHERE AAG.AssignedToId = 6 GROUP BY AAG.Id, A.Id, A.Title, CASE WHEN AA.CompletedOn IS NULL THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END, COALESCE(AAG.PointValue,0) </code></pre> <p>Without the LEFT OUTER JOIN portion, the below LINQ statement is partially complete, but I cannot figure out the appropriate syntax to add the LEFT OUTER JOIN condition:</p> <pre><code>var assignments = await (from g in db.AssignmentGroups.AsNoTracking().Where(x =&gt; x.AssignedToId == studentTask.Result.PersonId) join aa in db.ActivityAssignments.AsNoTracking() on g.Id equals aa.GroupId join a in db.Activities.AsNoTracking() on aa.ActivityId equals a.Id select new ActivityListViewModel { Id = a.Id, Points = g.PointValue ?? 0, Title = a.Title, GroupId = g.Id, Complete = (aa.CompletedOn != null) }); </code></pre> <p>Edit:</p> <p>Thanks for the response Bob. I attempted to use the DefaultIfEmpty and looked at the resultant SQL query generated by the Entity Framework, but it didn't work. Prior to making this post, this is the LINQ statement I attempted:</p> <pre><code>var assignments = from g in db.AssignmentGroups.AsNoTracking().Where(x =&gt; x.AssignedToId == studentTask.Result.PersonId) join aa in db.ActivityAssignments.AsNoTracking() on g.Id equals aa.GroupId join a in db.Activities.AsNoTracking() on aa.ActivityId equals a.Id from aq in db.ActivityQuestions.Where(q =&gt; q.ActivityId == a.Id).DefaultIfEmpty() group aq by new { ActivityId = aq.ActivityId, Title = a.Title, GroupId = g.Id, Points = g.PointValue ?? 0, Completed = (aa.CompletedOn != null) } into s select new ActivityListViewModel { Id = s.Key.ActivityId, Points = s.Key.Points + s.Sum(y =&gt; y.PointValue ?? 0), //g.PointValue ?? 0, Title = s.Key.Title, GroupId = s.Key.GroupId, Complete = s.Key.Completed }; </code></pre> <p>Of course, it didn't work either. The result was items missing the Id (ActivityId).</p>