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

寻linq达人解决一个多表查询问题
请教个问题，刚接触linq，一个查询搞不定  

参数为A表id

C# code

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如下，一个简单的左连接多表查询：

SQL code

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

[解决办法]
[code=C#]
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();

[/code]

查看更多 下一篇