LINQ JOIN和OUTER JOIN-如何将SQL转换为LINQ表达式

我想将以下SQL查询转换为LINQ表达式（使用Entity Framework 6.1）。到目前为止，我还找不到一个可接受的LINQ表达式来产生类似的结果。如果您能将这个简单的SQL语句转换为LINQ express，我们将不胜感激。

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)

如果没有LEFT OUTER JOIN部分，下面的LINQ语句将部分完成，但我无法找到添加LEFT OUTER JOIN条件的适当语法：

var assignments = await (from g in db.AssignmentGroups.AsNoTracking().Where(x => 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)
                                 });

编辑：

谢谢你的回复，鲍勃。我尝试使用DefaultIfEmpty并查看实体框架生成的SQL查询结果，但没有成功。在发表这篇文章之前，这是我尝试的LINQ声明：

var assignments = from g in db.AssignmentGroups.AsNoTracking().Where(x => 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 => 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 => y.PointValue ?? 0), //g.PointValue ?? 0, 
                  Title = s.Key.Title,
                  GroupId = s.Key.GroupId,
                  Complete = s.Key.Completed
              };

当然，它也不起作用。结果是缺少Id（ActivityId）的项目。

var assignments = await (from g in db.AssignmentGroups.AsNoTracking().Where(x => x.AssignedToId == studentTask.Result.PersonId)
                             join aa in db.ActivityAssignments.AsNoTracking() on g.Id equals aa.GroupId
                             join a1 in db.Activities.AsNoTracking() on aa.ActivityId equals a1.Id into a2
                             from a in a2.DefaultIfEmpty()
                             select new ActivityListViewModel
                             {
                                 Id = a == null ? null : a.Id,
                                 Points = g.PointValue ?? 0,
                                 Title = a == null ? null : a.Title,
                                 GroupId = g.Id,
                                 Complete = (aa.CompletedOn != null)
                             });

var assignments = from g in db.AssignmentGroups.AsNoTracking().Where(x => 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 => q.ActivityId == a.Id).DefaultIfEmpty()
                          group aq by new { ActivityId = a.Id, 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 => y.PointValue ?? 0), //g.PointValue ?? 0, 
                              Title = s.Key.Title,
                              GroupId = s.Key.GroupId,
                              Complete = s.Key.Completed
                          };