LINQ嵌套连接三个表

Question

我有以下代码：

var configItems = new List<ConfigurableItem>();
var assemblies = new List<Assembly>();
var pcbas = new List<Pcba>();

configItems.Add(new ConfigurableItem() { CiCode = "TST001", AssemblyId = "ASS001" });
configItems.Add(new ConfigurableItem() { CiCode = "TST002", AssemblyId = "ASS001" });
configItems.Add(new ConfigurableItem() { CiCode = "TST003", AssemblyId = "ASS002" });
configItems.Add(new ConfigurableItem() { CiCode = "TST004", AssemblyId = "ASS003" });

assemblies.Add(new Assembly { AssemblyId = "ASS001", PcbaId = "PCBA001"});
assemblies.Add(new Assembly { AssemblyId = "ASS002", PcbaId = "PCBA002" });

pcbas.Add(new Pcba { PcbaId = "PCBA001", Pcb = "401"});

var query = from c in configItems
            join ca in (from a in assemblies join p in pcbas on a.PcbaId equals p.PcbaId into ca from x in ca.DefaultIfEmpty() select a)
            on c.AssemblyId equals ca.AssemblyId into cap
            from ca in cap.DefaultIfEmpty()
            select new {
                CiCode = c.CiCode,
                AssemblyId = (ca == null ? "-" : ca.AssemblyId),
                Pcb = ???
            };

如何使LINQ查询生成以下结果集：

TST001 | ASS001 | PCBA001 | 401 
TST002 | ASS001 | PCBA001 | 401
TST003 | ASS002 |   -     | -
TST004 |    -   |   -     | -

Answer 1

这可能会有帮助

from c in configItems
     join ca in (from a in assemblies join p in pcbas on a.PcbaId equals p.PcbaId into ca from x in ca.DefaultIfEmpty() select new {a,x})
            on c.AssemblyId equals ca.a.AssemblyId into cap
            from ca in cap.DefaultIfEmpty()
            select new {
                CiCode = c.CiCode,
                AssemblyId = (ca == null ? "-" : ca.a.AssemblyId),
                Pcb = (ca != null && ca.x != null) ?ca.x.Pcb :"-",

            }

这是一个实用的例子