LINQ到DataSet中的多台左加入

Question

有许多地方，它展示了如何LEFT JOIN使用LINQ两个表，但我无法增加更多的进入连接（我有15桌）。我以为我有这种模式，但我得到一个错误。顺便说一句，这是针对强类型的数据集，尽管我不怀疑这一点。虽然我其实有更多的表添加到一起，我试图把它用更少的加入最初的工作（星号是其中的误差存在的）：

var results = 
    from e in DataSetHelper.ds.Employee 
    join es in DataSetHelper.ds.EmployeeSkill on e.EmployeeId equals es.EmployeeId into esGroup from esItem in esGroup.DefaultIfEmpty() 
    join s in DataSetHelper.ds.Skill on **es.SkillId** equals s.SkillId     into skillGroup from skillItem in skillGroup.DefaultIfEmpty() 
    join er in DataSetHelper.ds.EmployeeRole on e.EmployeeId equals er.EmployeeId  into erGroup from erItem in erGroup.DefaultIfEmpty() 
    join r in DataSetHelper.ds.Role on **er.RoleId** equals r.RoleId      into rGroup from rItem in rGroup.DefaultIfEmpty() 

我得到两个错误（同样的事情，但在不同的联接）。他们在查询的第3和第5行。

对于es.SkillId,误差为The name 'es' does not exist in the current context.

为er.RoleId,误差The name 'er' does not exist in the current context.

同样，我需要为另外十个加入到使用这种模式，所以我希望的格局不会增加随着我走向复杂。

Answer 1

这工作。我也必须考虑到连接列上有空的可能性。我为此使用了空条件运算符。不幸的是，使用内存中的数据集与使用SQL Server一样慢（我甚至没有全部连接或所有的where子句测试）。似乎我需要使用不同的方法。

var results = from e in _data.ds.Employee 
       join es in _data.ds.EmployeeSkill on e.EmployeeId equals es.EmployeeId  into esGroup from esItem in esGroup.DefaultIfEmpty() 
       join s in _data.ds.Skill on esItem?.SkillId equals s.SkillId     into sGroup from skillItem in sGroup.DefaultIfEmpty() 
       join er in _data.ds.EmployeeRole on e.EmployeeId equals er.EmployeeId   into erGroup from erItem in erGroup.DefaultIfEmpty() 
       join r in _data.ds.Role on erItem?.RoleId equals r.RoleId      into rGroup from rItem in rGroup.DefaultIfEmpty() 
       join et in _data.ds.EmployeeTechnology on e.EmployeeId equals et.EmployeeId into etGroup from etItem in etGroup.DefaultIfEmpty() 
       join t in _data.ds.Technology on etItem?.TechnologyId equals t.TechnologyId into tGroup from tItem in etGroup.DefaultIfEmpty() 
       where 
       e.FirstName.IndexOf(searchTerm, StringComparison.OrdinalIgnoreCase) >= 0 || 
       e.LastName.IndexOf(searchTerm, StringComparison.OrdinalIgnoreCase) >= 0 || 
       e.RMMarket.IndexOf(searchTerm, StringComparison.OrdinalIgnoreCase) >= 0 || 
       !e.IsSummaryNull() && e.Summary.IndexOf(searchTerm, StringComparison.OrdinalIgnoreCase) >= 0 
       select new SearchResult 
       { 
        EmployeeId = e.EmployeeId, 
        Name = e.FirstName + " " + e.LastName, 
        Title = e.Title, 
        ImageUrl = e.IsImageUrlNull() ? string.Empty : e.ImageUrl, 
        Market = e.RMMarket, 
        Group = e.Group, 
        Summary = e.IsSummaryNull() ? string.Empty : e.Summary.Substring(1, e.Summary.Length < summaryLength ? e.Summary.Length - 1 : summaryLength), 
        AdUserName = e.AdUserName 
       }; 

Answer 2

试试这个

var results = DataSetHelper.ds.Employee 
.Join(DataSetHelper.ds.EmployeeSkill, e => e.EmployeeId, es => es.EmployeeId, (e, es) => new { e, es }) 
.Join(DataSetHelper.ds.Skill, esGroup => esGroup.es.SkillId, s => s.SkillId, (esGroup, s) => new { esGroup.e, esGroup.es, s }) 
.Join(.... 

我已经表明，直到第一个多对多的关系。对于一切都会有点长，但我希望你能管理它。