分享案例：EF【LINQ用法】多表联动内嵌子查询Count分组用法

[morliz子轩]

SQL原型：

select a.BranjobId
	,a.Jobno
	,b.Jobna
	,Planqty
	,Jobqty = (select count(Manno) from BgMan c where a.Branno = c.Branno and a.Jobno = c.Jobno)	--人事档案表
	,Overtime = sum(a.Overtime)

from BranJob a	--部门职位表
left join JobSet b on a.Jobno =b.Jobno	--职位表
where a.Branno = '006'

group by a.BranjobId,a.Branno,a.Jobno,b.Jobna,Planqty
order by a.BranjobId

EF联动查询用法：

         /// <summary>
        /// 获取部门职务信息
        /// </summary>
        /// <param name="branno">部门编号</param>
        /// <returns></returns>
        public static DataTable GetBranjob(string branno)
        {
            try
            {
                using(CusProContext db = new CusProContext())
                {
                    var BranjobObj = (from p in db.BranJob
                                      where p.Branno == branno

                                      join a in db.JobSet on p.Jobno equals a.Jobno into t1
                                      from a in t1.DefaultIfEmpty()

                                      join b in db.BgMan on new { p.Branno, p.Jobno } equals new { b.Branno, b.Jobno } into t2
                                      from c in t2.DefaultIfEmpty()

                                      group c by new
                                      {
                                          p.BranjobId,
                                          p.Jobno,
                                          a.Jobna,
                                          p.Planqty,
                                          p.Overtime
                                      } into grp

                                      select new
                                      {
                                          grp.Key.BranjobId,
                                          grp.Key.Jobno,
                                          grp.Key.Jobna,
                                          grp.Key.Planqty,
                                          Jobqty = grp.Count(x => x.Manno != null),
                                          Lackqty = grp.Key.Planqty - grp.Count(x => x.Manno != null),
                                          grp.Key.Overtime
                                      }).ToList();

                    return LinqToDataTable(BranjobObj);
                }
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }

这里是采用的是左联连的查询用法，如有什么不足不处，欢迎大神指正。

[兔子-顾问]

感谢分享，虽然没看出问题，但不影响我发帖赞一下。

[morliz子轩]

谢谢 版主大大

[morliz子轩]

@tlqxc_ 用鼠标放在BranjobObj这个对象上，也就是var的对象上，看是个什么类型。如果是有实体的，那就会告诉你。如果是匿名类型（带问号的），那就是你写的语句存在问题。

[tlqxc_]

前端如何接收这个数据啊，我的数据类型匹配不上