linq to sql 查找所有开票金额大于回款金额的项目

查找所有开票金额大于回款金额的项目

TB_Projects 项目表 

TB_Recipts 发票表 

TB_Finances 回款表 

TB_Projects  一对多 TB_Recipts    

TB_Projects  一对多 TB_Finances 

select TB_Projects.ProjectCode, (CASE WHEN SUM(TB_Receipt.ReceiptMoney) IS NULL THEN 0 ELSE SUM(TB_Receipt.ReceiptMoney) END)as kp,
(CASE WHEN SUM(TB_ProjectFinance.RealityMoney) IS NULL THEN 0 ELSE SUM(TB_ProjectFinance.RealityMoney)END) as hk from TB_Projects left join TB_Receipt 
on TB_Projects.Id=TB_Receipt.Project_Id left join TB_ProjectFinance on TB_Projects.Id=TB_ProjectFinance.Project_Id where TB_Projects.ProjcetManager=N'李优'

group by TB_Projects.ProjectCode 

 

var query = (from p in Projects
                         join q in Recipts on p.Id equals q.Project.Id into l  
                         from lp in l.DefaultIfEmpty()//左链接 两个表 生成新表 Projects---Recipts
                         join t in Finances on p.Id equals t.Project.Id into z
                         from zp in z.DefaultIfEmpty() //左链接 两个表 Projects---Finance
                         where p.ProjcetManager.Contains(person) //项目经理是当前登录人
                         select new
                         {
                             ProjectName = p.ProjectName,
                             ProjectCode = p.ProjectCode,
                             KaiPiao = lp.ReceiptMoney == null ? 0 : lp.ReceiptMoney,
                             HuiKuan = zp.RealityMoney == null ? 0 : zp.RealityMoney
                         }
                             into g
                             group g by new { g.ProjectCode,g.ProjectName } into k  //根据 编号和姓名 分组
                             select new
                             {
                                 ProjectCode = k.Key.ProjectCode,
                                 ProjectName=k.Key.ProjectName,
                                 KaiPiao = k.Sum(t => t.KaiPiao) == null ? 0 : k.Sum(t => t.KaiPiao), 
                                 HuiKuan = k.Sum(t => t.HuiKuan) == null ? 0 : k.Sum(t => t.HuiKuan),
                             } into d
                             where d.KaiPiao > d.HuiKuan
                             select new
                             {
                                 ProjectName=d.ProjectName,
                                 KaiPiao = d.KaiPiao,
                                 HuiKuan = d.HuiKuan,
                                 ProjectCode = d.ProjectCode
                             }
                  ).ToList();


            return (from q in query
                    select new TB_ProjectsInputDto
                    {
                        ProjectName = q.ProjectName,
                        ProjectCode = q.ProjectCode,
                        ReceiptMoney = q.KaiPiao,
                        ReceivedPayment = q.HuiKuan
                    }).ToList();