C# Linq 查询汇总

一、分组取值、求和、计数

求和、计数都用 Sum,计数前提是在 list 中添加一个固定值为 1 的列。

取分组关键字用 ss.Key.XXX。

 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             });

二、主从表关联,对应多条明细数据

嵌入模式,复制给实体的一个属性:(如下示例中的属性:jijianmxs)

 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,然后通过 ForEach 逐个通过 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 = "清除推送列表成功!" });
    }

也可以,针对筛选结果 list,使用 foreach 遍历执行 Remove 方法。

posted @ 2022-03-03 15:23  橙子家  阅读(1089)  评论(0编辑  收藏  举报