项目中经常会用到linq表达式,在这里对常用查询做个记录
在net core 3.1中使用两个表进行联合查询
一 内连接
1、内连接lambda
// 内连接(lambda表达式又称点标试)join方法的参数,第一个是要join的对象(下面是students),第二个是外部的(下面的teacher)key,第三个参数内部(下面是students)的key,第四个是结果(自己定义)
var result = await _dbContext.teachers.Join(_dbContext.students, t => t.Id, s => s.TeacherId, (t, s) =>
new TeacherAndStudentDto
{
TeacherId = t.Id,
TeacherName = t.Name,
StudentId = s.Id,
StudentName = s.Name
}).ToListAsync();
2、内连接linq
// 内连接(linq表达式又称查询表达式)
var result = await (from s in _dbContext.students
join t in _dbContext.teachers
on s.TeacherId equals t.Id
select new TeacherAndStudentDto
{
TeacherId = t.Id,
TeacherName = t.Name,
StudentId = s.Id,
StudentName = s.Name
}).ToListAsync();
二 左链接
1、左链接linq
//左连接(已左表为基础表(下面就是已students为基础表),匹配条件,如果有就加上,没有就已默认值添上)和sql中left join类似
var result = await (from s in _dbContext.students
join t in _dbContext.teachers
on s.TeacherId equals t.Id into newTeacher
from nt in newTeacher.DefaultIfEmpty() //nt的类型为Teacher
select new TeacherAndStudentDto
{
TeacherId = nt.Id,
TeacherName = nt.Name,
StudentId = s.Id,
StudentName = s.Name
}).ToListAsync();
2、左连接后分组linq
var workTasks = await _workTaskRepository.Where(d => d.CreateTime >= _startMonth && d.CreateTime < _endMonth).ToListAsync();
var workCenters = await _workCenterRepository.AsQueryable().ToListAsync();
var items = from A in workTasks
join B in workCenters on A.WorkCenterId equals B.Id.ToString() into C
from D in C.DefaultIfEmpty() // 左连接,即使A中有但B中没有的也会输出
select new
{
A.WorkCenterId,
D.Code,
D.Name,
A.Quantity,
A.FinishedQuantity
} into E
group E by E.WorkCenterId into F
select new
{
WorkCenterId = F.Key,
WorkCenterCode = F.FirstOrDefault().Code,
WorkCenterName = F.FirstOrDefault().Name,
Quantity = F.Sum(d => d.Quantity),
FinishedQuantity = F.Sum(d => d.FinishedQuantity)
}
;
3、左链接lambda
//左连接
var students = await _dbContext.students.ToListAsync();
var teachers = await _dbContext.teachers.ToListAsync();
var result = students.GroupJoin(teachers, s => s.TeacherId, t => t.Id, //调用的是 IEnumerable<TResult> GroupJoin<>这个方法,是已studens为基础表,Teacher表附加到上面
(s, t) => new TeacherAndStudentDto // t为IEnumerable<Teacher>
{
StudentId = s.Id,
StudentName = s.Name,
TeacherId = s.TeacherId,
TeacherName = t.FirstOrDefault(c => c.Id == s.TeacherId) == null ? null : t.FirstOrDefault(c => c.Id == s.TeacherId).Name
}
).ToList();
三 错误示例
下面这种写法报错
var result = await _dbContext.students.GroupJoin(_dbContext.teachers, s => s.TeacherId, t => t.Id, //调用IQueryable<TResult> GroupJoin<>这个方法
(s, t) => new TeacherAndStudentDto
{
StudentId = s.Id,
StudentName = s.Name,
TeacherId = t.FirstOrDefault(c => c.Id == s.TeacherId) == null ? 0 : t.FirstOrDefault(c => c.Id == s.TeacherId).Id,
TeacherName = t.FirstOrDefault(c => c.Id == s.TeacherId) == null ? null : t.FirstOrDefault(c => c.Id == s.TeacherId).Name
}).ToListAsync();
一些相关类
public class TeacherAndStudentDto
{
public int TeacherId { get; set; }
public string TeacherName { get; set; }
public string StudentName { get; set; }
public int StudentId { get; set; }
}
public class Teacher
{
[Key]
public int Id { get; set; }
public string Name { get; set; }
/// <summary>
/// 所教科目
/// </summary>
public string Subject { get; set; }
public GenderEnum GenderEnum { get; set; }
public DateTime Birthday { get; set; }
public double Hegiht { get; set; }
public double? Weight { get; set; }
}
public class Student
{
[Key]
public int Id { get; set; }
public string Name { get; set; }
public DateTime? Birthday { get; set; }
/// <summary>
/// 学号
/// </summary>
public string Numerber { get; set; }
/// <summary>
/// 分数
/// </summary>
public int? Score { get; set; }
/// <summary>
/// 年级
/// </summary>
public string Grade { get; set; }
public GenderEnum? GenderEnum { get; set; }
public double Hegiht { get; set; }
public double? Weight { get; set; }
public int TeacherId { get; set; }
}
public enum GenderEnum
{
[Description("女")]
Female=0,
[Description("男")]
Male =1,
}
public class EFCoreDbContext : DbContext
{
public EFCoreDbContext(DbContextOptions<EFCoreDbContext> options) : base(options)
{
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
//如果appseting.json中没有配置数据库连接,会读取这里的配置
if (!optionsBuilder.IsConfigured)
{
optionsBuilder.UseSqlServer("Server=.;Data Source=(local);uid=sa;pwd=xxx;DataBase=xxx");
}
base.OnConfiguring(optionsBuilder);
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
//Fluent API
}
public DbSet<Teacher> teachers { get; set; }
public DbSet<Student> students { get; set; }
}