C# Linq 查询汇总
C# Linq 查询汇总
分组取值、求和、计数
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 = "清除推送列表成功！" });
}
漫思
