寻linq达人解决一个多表查询问题

[z650816]

请教个问题，刚接触linq，一个查询搞不定

参数为A表id

var q = (from a in DBUtility.DB.Productions
                     join b in DBUtility.DB.Projects on a.Id equals b.ProductionId
                     join c in DBUtility.DB.Plays on b.PlayId equals c.Id
                     join d in DBUtility.DB.PlayerMedias on c.Id equals d.PlayerId 
                     where (a.Id == id) && (d.PlayerId == null) 
                     select new{
                        
                     });
            return q.ToList();

默认是内连接，导致过滤了很多空项，
我的意思是因为是根据第一张表的条件来的，所以使用左连接
不管其余3张表是否有信息，都依第一张表为准

最终返回的是 相对于A表的 D表的其中一列
请教各位同僚该如何写

其实想要的结果翻译成SQL如下，一个简单的左连接多表查询：

select a.*,b.*,c.*,d.* from 
dbo.Production a left join 
dbo.Project b
on a.Id=b.ProductionId left join 
dbo.Play c 
on b.PlayId=c.Id left join 
dbo.PlayerMedia d
on c.Id=d.PlayId 
where a.id=xx and d.PlayerId is null

[ViewStates]

from a in DbA join
b in DbB on a.id equals b.id into tmpDbC
from c in tmpDbC.DefaultIfEmpty()
.....

[z650816]

能帮忙写详细点不，或者直接照上面的sql来个完整的，不大明白。。。

[caoyuwang165]

日奶奶何来滴:var li =s1.Join(s2, temp1 => temp1.AID, temp2 => temp2.AID, (temp1, temp2) => new { temp1.AID, temp1.AName, temp2.BID, temp2.BName }); 

[caoyuwang165]

日奶奶何来滴:var li =s1.Join(s2, temp1 => temp1.AID, temp2 => temp2.AID, (temp1, temp2) => new { temp1.AID, temp1.AName, temp2.BID, temp2.BName });
s1="集合1"
s2="集合2"
可以加“集合3，集合4,......集合n”

[caoyuwang165]

var li =s1.Join(s2,temp1 => temp1.AID, temp2 => temp2.AID,(temp1, temp2) => new { temp1.AID, temp1.AName, temp2.BID, temp2.BName }).Join(......); 

[ViewStates]

[Quote=引用 2 楼 z650816 的回复:]

能帮忙写详细点不，或者直接照上面的sql来个完整的，不大明白。。。
[/Quote]
在你自己写的语句中每个EQUALS后面加上
into xxx
from x in xxx.DefaultIfEmpty()
就可以实现LEFT JOIN了，你那样写是INNER JOIN

[q107770540]

var q = (from a in DBUtility.DB.Productions
                     join b in DBUtility.DB.Projects on a.Id equals b.ProductionId into x
                     from bx in x.DefaultIfEmpty()
                     join c in DBUtility.DB.Plays on bx.PlayId equals c.Id into y
                     from cy in y.DefaultIfEmpty()
                     join d in DBUtility.DB.PlayerMedias on cy.Id equals d.PlayerId into z
                     from dz in z.DefaultIfEmpty()
                     where (a.Id == id) && (dz.PlayerId == null) 
                     select new{
                        
                     });
            return q.ToList();

[xlovexever]

超过两个表的连接我都是用存储过程的 不然多了服务器承受不起