//linq执行多表查询
var q = from a in dbContext.Sxcountylevelcityinfo
join b in dbContext.Sxprefecturelevelcityinfo
on a.PrefectureId equals b.PrefectureId
join c in table_c on c.id equals b.id
select new { a, b };
//efcore执行多表查询
var p = dbContext.Student.Join(dbContext.School, stu=> stu.SchoolId,sch => sch.Id, (stu, sch) => new {stu,sch.SchoolName });
//通过include显示加载相关联接表，前提是在Dbcontext中的模型类中建立关系，如一对多等
var xx= dbContext.Sxcountylevelcityinfo.Include(o=>o.Sxprefecturelevelcityinfo);
//efcore执行sql语句，如增，删，改以及创建表等,这里一般不执行查询
var s = dbContext.Database.ExecuteSqlCommand("update sxprefecturelevelcityinfo set comments='b' where id=13");
var student = await _context.Students .AsNoTracking() .FirstOrDefaultAsync(m => m.ID == id);//执行异步查询
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
// 实现多key分组的扩展函数版本 var sums = empList .GroupBy(x => new { x.Age, x.Sex }) .Select(group => new { Peo = group.Key, Count = group.Count() }); foreach (var employee in sums) { Console.WriteLine(employee.Count + ": " + employee.Peo); } // 实现多key分组的lambda版本 var sums2 = from emp in empList group emp by new { emp.Age, emp.Sex } into g select new { Peo = g.Key, Count = g.Count() }; foreach (var employee in sums) { Console.WriteLine(employee.Count + ": " + employee.Peo); } |
groupby sum组合查询统计求和某列：
var x = dbContext.User.GroupBy(o => o.CategoryName).Select(x => new { x.Key, Total = x.Sum(o => o.PersonNumber) });
x.Key代表Key=x.Key的简写，将来按分组名取数据。当然也可以写成Name=x.key，那么取数据就用Name
GroupBy根据多个字段分组使用方式：
|
1 2 3 4 5 6 7 8 9 10 |
query.GroupBy(q => new { q.Year, q.Month }) .Select(q => new { Year = q.Key.Year, Month = q.Key.Month, BuildAmount = q.Sum(i => i.BuildAmount), RecAmount = q.Sum(i => i.RecAmount), Amount = q.Sum(i => i.Amount), RealAmount = q.Sum(i => i.RealAmount) }); |
发表评论