linq和lambda表达式，已经使用join和groupjoin

在net core 3.1中使用两个表进行联合查询

          // 内连接（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();

         // 内连接（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(); 

       //左连接（已左表为基础表（下面就是已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();

最后我用groupjoin 先用下面的方法，可以查到数据

 //左连接  
            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();

然后就报错（暂时没有找到问题所在）

An unhandled exception occurred while processing the request.
InvalidOperationException: Processing of the LINQ expression 'DbSet<Student>
.GroupJoin(
outer: DbSet<Teacher>,
inner: s => s.TeacherId,
outerKeySelector: t => t.Id,
innerKeySelector: (s, t) => new TeacherAndStudentDto{
StudentName = s.Name,
TeacherName = t
.FirstOrDefault(c => c.Id == s.TeacherId).Name
}
)' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.
Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)

8.3号更新（解决这个问题）：看来还是要多看微软EF文档，大概的意思就是需要获取所用的数据来进行groupjoin，这也就是IEnumerable可以调用，而IQuerable这个不可用的原因，具体看下面链接

https://docs.microsoft.com/zh-cn/ef/core/querying/complex-query-operators

 

一些相关的类

 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; }

    }

这个问题还要摸索一下（已解决），以后解决吧，又要学习go语言了

https://www.cnblogs.com/zitjubiz/p/a-left-outer-join-using-linq-extension-methods.html