C# Linq 查询汇总

C# Linq 查询汇总 

 

阅读目录

•  分组取值、求和、计数
•  主从表关联，对应多条明细数据
• 分组去重
• 两表关联、排序、对象赋值、分组去重
• 两个 list 关联（inner join）
• 两个 list 关联（left join）（list2 为空添加默认值）
• list.Remove 单个实例 / 多个实例

 

---

回到顶部

 分组取值、求和、计数

　　

 1   var resultlist = orderllist.GroupBy(oo => new { oo.Deptname, oo.Userid, oo.Username })
 2             .Select(ss => new OBreakfirstModel
 3             {
 4                 Number = number++,
 5                 Deptname = ss.Key.Deptname,
 6                 Userid = ss.Key.Userid,
 7                 Username = ss.Key.Username,
 8                 Ototalamount = ss.Sum(s => s.Ototalamount),
 9                 Ocount = ss.Sum(s => s.Ocount)
10             });

回到顶部

 主从表关联，对应多条明细数据

　　

 1         var danzilist = DBconn.Query<ZuZModel>(sql_list, dynamicParameters).ToList();
 2         var zijianlist= DBconn.Query<ZiMX>(sql_jian).ToList();
 3         var resultlist = (from danzi in danzilist
 4                         select new ZuZhuangCXModel()
 5                         {
 6                             fid = danzi.fid,
 7                             rukurq=danzi.rukurq,
 8                             shiwulx=danzi.shiwulx,
 9                             wuliaobm=danzi.wuliaobm,
10                             wuliaomc=danzi.wuliaomc,
11                             wuliaolbdm=danzi.wuliaolbdm,
12                             wuliaolbmc=danzi.wuliaolbmc,
13                             shuliang=danzi.shuliang,
14                             cangkuid=danzi.cangkuid,
15                             cangkumc=danzi.cangkumc,
16                             zijianmxs = (from zijian in zijianlist
17                                         where zijian.fid == danzi.fid
18                                         select new ZiJianMX()
19                                         {
20                                             fid = zijian.fid,
21                                             fdetailid = zijian.fdetailid,
22                                             wuliaobm = zijian.wuliaobm,
23                                             wuliaomc = zijian.wuliaomc,
24                                             cangkuid = zijian.cangkuid,
25                                             cangkumc = zijian.cangkumc,
26                                             shuliang = zijian.shuliang
27                                         }).ToList()
28                         }).ToList();

回到顶部

分组去重

    var newycmxlist = ycmxlist.GroupBy(g => new { g.EMPI, g.SHIFOUXZ })
.Select(item => item.First()).ToList();

回到顶部

两表关联、排序、对象赋值、分组去重

           List<YiChangEMPIModel> ycmxlist = new List<YiChangEMPIModel>();
            var yichang = from yc in AnalysisDbContext.EMPI_YC
                          from ycmx in AnalysisDbContext.EMPI_YCMX
                          where yc.ID == ycmx.ZHUBIAOID && (state == "*" ? 1==1: yc.SHIFOUXZ==state)
                          && (yc.EMPI.Contains(text) || ycmx.SHENFENZH.Contains(text) || ycmx.XINGMING.Contains(text))
                          orderby yc.UPDATE_TIME descending
                          select new { 
                            ID=yc.ID,
                            EMPI=yc.EMPI,
                            SHIFOUXZ=yc.SHIFOUXZ,
                            INSERT_TIME=yc.INSERT_TIME,
                            UPDATE_TIME=yc.UPDATE_TIME
                          };
            foreach (var yc in yichang)
            {
                ycmxlist.Add(new YiChangEMPIModel
                {
                    ID = yc.ID,
                    EMPI = yc.EMPI,
                    SHIFOUXZ = yc.SHIFOUXZ,
                    INSERT_TIME = yc.INSERT_TIME,
                    UPDATE_TIME = yc.UPDATE_TIME
                });
            }

回到顶部

两个 list 关联（inner join）

    //部门信息 关联 日志表 取出上次处理时间
    //deptresult：包含各级部门和上传时间
    var deptresult = (from t1 in dapartments
                      join t2 in logs on t1.DeptID equals t2.DeptID
　　　　　　　　　　　　　select new DepartmentInfoModel
                        {
                            CurntDeptID = t1.CurntDeptID,
                            CurntDeptName = t1.CurntDeptName,
                            DeptID = t1.DeptID,
                            DeptName = t1.DeptName,
                            LeaderID = t1.LeaderID,
                            LeaderName = t1.LeaderName,
                            Starttime = t2.Starttime,
                            Endtime = t2.Endtime
                        }).ToList();

回到顶部

两个 list 关联（left join）（list2 为空添加默认值）

    //部门信息 关联 日志表 取出上次处理时间
    //deptresult：包含各级部门和上传时间
    var deptresult = (from t1 in dapartments
                        join t2 in logs on t1.DeptID equals t2.DeptID into temp
                        from tempresule in temp.DefaultIfEmpty(
                            new SendLogModel { Starttime = Convert.ToDateTime("2020-12-22"), Endtime = Convert.ToDateTime("2020-12-22") })
                        select new DepartmentInfoModel
                        {
                            CurntDeptID = t1.CurntDeptID,
                            CurntDeptName = t1.CurntDeptName,
                            DeptID = t1.DeptID,
                            DeptName = t1.DeptName,
                            LeaderID = t1.LeaderID,
                            LeaderName = t1.LeaderName,
                            Starttime = tempresule.Starttime,
                            Endtime = tempresule.Endtime
                        }).ToList();

回到顶部

list.Remove 单个实例 / 多个实例

　　 

        List<TemplistExpenseModel> currentlist = templistExpenseModels;
        if (userid != "" && userid != null)
        {
            currentlist.Where(ee => ee.Userid == userid).ToList().ForEach(cc => { templistExpenseModels.Remove(cc); });
            return Json(new { code = 200, msg = "清除推送列表成功！" });
    }