linq和lambda表达式,两表连接查询

项目中经常会用到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; }

    }

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

一个程序员_zhangzhen

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值