C# Linq 查询汇总

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

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

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

  var resultlist = orderllist.GroupBy(oo => new { oo.Deptname, oo.Userid, oo.Username })
            .Select(ss => new OBreakfirstModel
            {
                Number = number++,
                Deptname = ss.Key.Deptname,
                Userid = ss.Key.Userid,
                Username = ss.Key.Username,
                Ototalamount = ss.Sum(s => s.Ototalamount),
                Ocount = ss.Sum(s => s.Ocount)
            });

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

嵌入模式，复制给实体的一个属性：（如下示例中的属性：jijianmxs）

        var danzilist = DBconn.Query<ZuZModel>(sql_list, dynamicParameters).ToList();
        var zijianlist= DBconn.Query<ZiMX>(sql_jian).ToList();
        var resultlist = (from danzi in danzilist
                        select new ZuZhuangCXModel()
                        {
                            fid = danzi.fid,
                            rukurq=danzi.rukurq,
                            shiwulx=danzi.shiwulx,
                            wuliaobm=danzi.wuliaobm,
                            wuliaomc=danzi.wuliaomc,
                            wuliaolbdm=danzi.wuliaolbdm,
                            wuliaolbmc=danzi.wuliaolbmc,
                            shuliang=danzi.shuliang,
                            cangkuid=danzi.cangkuid,
                            cangkumc=danzi.cangkumc,
                            zijianmxs = (from zijian in zijianlist
                                        where zijian.fid == danzi.fid
                                        select new ZiJianMX()
                                        {
                                            fid = zijian.fid,
                                            fdetailid = zijian.fdetailid,
                                            wuliaobm = zijian.wuliaobm,
                                            wuliaomc = zijian.wuliaomc,
                                            cangkuid = zijian.cangkuid,
                                            cangkumc = zijian.cangkumc,
                                            shuliang = zijian.shuliang
                                        }).ToList()
                        }).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 方法。