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

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

         /// <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;

            }

        }