linq中的左外联表查询

在做项目时，遇到一个左外连接的问题。现在和大家分享如下：

  帐单表:tbZd 字段如下                    

autoid              int              //帐单编号  

name               varchar       //帐单名称

Menu1ZDid      int

addtm              datetime

isdel                bit

description      varchar    

 

用户订制 tbDzzD 表

 

autoid             numeric

cmobile           varchar

ZDID               int             //帐单编号

MustInput       varchar

addtm             datetime

istui                 bit

fdcarea            varchar

 

现在的问题是在两表中统计不同帐单的用户数那么就会用联表查询。

在sqlserver  我是这样写的。

 

      SELECT a.name, isnull(cnt, 0) 'cnt'   FROM tbZD a LEFT JOIN
                 (SELECT b.name, a.cmobile, COUNT(*) 'cnt'
                 FROM tbDZZD a INNER JOIN  tbZD b ON a.ZDID = b.autoid  WHERE a.istui = 0 AND b.isdel = 0
                GROUP BY b.name, a.cmobile) b ON a.name = b.name

 

在SQL这是可行的。

但在linQ中并没有  LEFT  join   on    。那么在linq中如何实现这样 //复杂查询 private void OtherSuearch() { //var query=from a in context.tbZD from b in (from c in context.tbDZZD join d in context.tbZD on c.ZDID equals d.autoid where c.istui.Equals(false)&&d.isdel.Equals(false) select new {c.cmobile,d.name} ) //var query1 = from g in (from c in context.tbDZZD join d in context.tbZD on c.ZDID equals d.autoid where c.istui.Equals(false) && d.isdel.Equals(false) select new { c.cmobile, d.name }) group g by new { g.name, g.cmobile } into gg select new { gg.Key.cmobile, gg.Key.name,num=gg.Count() }; DataClasses1DataContext context = new DataClasses1DataContext(); //查询订制的用户表中存在的帐单号 var query1 = from g in (from c in context.tbDZZD join d in context.tbZD on c.ZDID equals d.autoid where c.istui.Equals(false) && d.isdel.Equals(false) select new { c.cmobile, d.name }) group g by new { g.name, g.cmobile } into gg select gg.Key.name; //查询订制的用户表不存在的帐单位号，则用户数都为0 var query2 = from gg in context.tbZD where !query1.Contains(gg.name) select new { gg.name, num = 0 }; //查询订制的用户表存在的帐单号及对应用户数 var query3 = from a in context.tbZD from bb in (from g in (from c in context.tbDZZD join d in context.tbZD on c.ZDID equals d.autoid where c.istui.Equals(false) && d.isdel.Equals(false) select new { c.cmobile, d.name }) group g by new { g.name, g.cmobile } into gg select new { gg.Key.cmobile, gg.Key.name, num = gg.Count() }) where a.name == bb.name select new { a.name, bb.num }; //没用户数的帐单号的统计与有用户数的帐凌晨单号数据结合 query3.Concat(query2).ToList(); } 的查询结果呢。

苦想之下我是这样解决的。大家看看如何

 

 

 

这样觉得还是复杂，不知道还有没有更好有的办法。