linq 2 左连接

Question

所以我想从我的左连接 sql 中进行 linq 查询（请参阅下面）。我只是不知道如何在连接上正确定位“.TournamentId = 1”条件。目前，当在我的数据库上运行它时，我得到了我想要的结果。这是类型表中带有空字段的几行。

select typ.Id, stat.PromoterId, temp.PromoterId
from ReportTypes type
left join ReportTemplateStatus status on status.PromoterId = type.TypeId and status.TournamentId = 1
left join ReportTemplates temp on temp.ClientId = status.PromoterId and temp.TournamentId = 1

Promoter
 - promoterId
 - promoterName

Tournament
 - tournamentId
 - tournamentName

ReportType
 - TypeId

ReportTemplateStatus 
 - promoterId (this is the key)
 - tournamentId
 - typeId

ReportTemplates
 - promoterId
 - tournamentId

这就是我目前所拥有的：

var report  =  from type in context.ReportTypes 
                 join status in context.ReportTemplateStatus on type.TypeId equals status.TypeId
                 join temp in context.ReportTemplates  on status.promoterId equals temp.promoterId into iReports
                 from reports in iReports.Where (rep => rep.promoterId == _promoterId && rep.tournamentId == _tournamentId).DefaultIfEmpty()
select new { my fields});

但它给了我一个空值。

关于 linq 应该如何工作有什么想法吗？也许分成“itables”（iReports）或其他东西？

Answer 1

这应该会给你你正在寻找的东西

var report  =  from type in context.ReportTypes 
               from status in context.ReportTemplateStatus.Where(x => type.TypeId == x.TypeId)        
                                                          .Where(x => x.TournamentId == 1)  
                                                          .DefaultIfEmpty()
               from reports in context.ReportTemplates.Where(x => status.promoterId == x.promoterId)
                                                      .Where(x => x.TournamentId == 1)
                                                      .DefaultIfEmpty()
               select new { my fields};

Answer 2

你可以像这样使用

  var query = from person in people
                    join pet in pets on person equals pet.Owner into gj
                    from subpet in gj.DefaultIfEmpty()
                    select new { person.FirstName, PetName = (subpet == null ? String.Empty : subpet.Name) };

谢谢