EF复杂查询

总结了常用的也是比较复杂的EF操作，希望提供给大家参考，有不对的地方欢迎小伙伴们留言指出，谢谢大家！

1、实体模型和上下文

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Text;

namespace EFDemo
{
    public class MyContext  : DbContext
    {
        public DbSet<School> Schools { get; set; }
        public DbSet<Major> Majors { get; set; }
        public DbSet<Student> Students { get; set; }

        public DbSet<School2> SchoolsNoFK { get; set; }
        public DbSet<Major2> MajorsNoFK { get; set; }
        public DbSet<Student2> StudentsNoFK { get; set; }
        public DbSet<StudentMajor2> StudentMajorsNoFK { get; set; }


        protected override void OnConfiguring(DbContextOptionsBuilder options)
        { 
             options.UseSqlite("Data Source=efdemo.db");
            options.LogTo(Console.WriteLine, Microsoft.Extensions.Logging.LogLevel.Information);
        }
    }
}

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace EFDemo
{

    public enum MajorTypeEnum
    {
        [Display(Name = "必修")]
        Required,
        [Display(Name = "选修")]
        Optional
    }

    public class Major
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int ID { get; set; }

        public string MajorCode { get; set; }

        public string MajorName { get; set; }

        public MajorTypeEnum? MajorType { get; set; }

        public string Remark { get; set; }

        [Required()]
        public int? SchoolId { get; set; }

        public School School { get; set; }

        public List<StudentMajor> StudentMajors { get; set; }

    }

    public class Major2
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int ID { get; set; }

        public string MajorCode { get; set; }

        public string MajorName { get; set; }

        public MajorTypeEnum? MajorType { get; set; }

        public string Remark { get; set; }

        public string SchoolName { get; set; }

    }

}

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace EFDemo
{
    public enum SchoolTypeEnum
    {
        [Display(Name = "公立学校")]
        PUB,
        [Display(Name = "私立学校")]
        PRI
    }

    public class School 
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int ID { get; set; }

        public string SchoolCode { get; set; }

        public string SchoolName { get; set; }

        public SchoolTypeEnum? SchoolType { get; set; }

        public string Remark { get; set; }

        public List<Major> Majors { get; set; }
    }

    public class School2
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int ID { get; set; }

        public string SchoolCode { get; set; }

        public string SchoolName { get; set; }

        public SchoolTypeEnum? SchoolType { get; set; }

        public string Remark { get; set; }

    }


}

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace EFDemo
{

    public class Student 
    {

        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int ID { get; set; }

        public string Password { get; set; }

        public string Name { get; set; }

        public bool IsValid { get; set; }

        public int Age { get; set; }

        public DateTime EnRollDate { get; set; }

        [Display(Name = "专业")]
        public List<StudentMajor> StudentMajor { get; set; }
    }

    public class Student2
    {

        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int ID { get; set; }

        public string Password { get; set; }

        public string Name { get; set; }

        public bool IsValid { get; set; }

        public int Age { get; set; }

        public DateTime EnRollDate { get; set; }

    }

}

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace EFDemo
{
    public class StudentMajor 
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int ID { get; set; }
        public Major Major { get; set; }
        public Student Student { get; set; }

        public int MajorId { get; set; }

        public int StudentId { get; set; }
    }

    public class StudentMajor2
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int ID { get; set; }

        public string StudentName { get; set; }
        public string MajorName { get; set; }
    }

}

2、EF查询操作

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;

namespace EFDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            using (MyContext dc = new MyContext())
            {
                InitData(dc);

                var students = dc.Set<Student>().AsNoTracking().ToList();
                var students2 = dc.Set<Student>().Include(x => x.StudentMajor).ThenInclude(x => x.Major).ToList();
                var student3 = dc.Set<Student>().Select(x => new
                {
                    name = x.Name,
                    majors = string.Join(',', x.StudentMajor.Select(y => y.Major.MajorCode))
                }).ToList();

                var student4 = dc.Set<Student>().Where(x => x.StudentMajor.Select(y => y.Major.School.SchoolName).Contains("School1")).ToList();

                var student5 = dc.Set<Student>().GroupBy(x => x.IsValid).Select(x => new
                {
                    name = x.Key,
                    count = x.Count()
                }).ToList();

                var student6 = dc.Set<Student>().GroupBy(x => new { x.IsValid, x.EnRollDate }, x => x.Age).Where(x => x.Average() > 15).Select(x => new
                {
                    name = x.Key.IsValid,
                    date = x.Key.EnRollDate,
                    count = x.Count(),
                    aveAge = x.Average()
                }).ToList();

                var student7 = dc.Set<Student>().SelectMany(x => x.StudentMajor, (a, b) => new
                {
                    stu = a,
                    school = b.Major.School.SchoolName
                }).GroupBy(x => new { x.school, x.stu.EnRollDate }, x => x.stu.Age).Where(x => x.Average() > 15).Select(x => new
                {
                    school = x.Key.school,
                    date = x.Key.EnRollDate,
                    count = x.Count(),
                    aveAge = x.Average()
                }).ToList();


                var studentsNoKey = dc.Set<Student2>().ToList();
                //var studentsNoKey2 = dc.Set<Student2>().Include(x => x.StudentMajor).ThenInclude(x => x.Major).ToList(); //不能使用Include
                var studentsNoKey3 = dc.Set<Student2>().Select(x => new
                {
                    name = x.Name,
                    majors = string.Join(',', dc.Set<StudentMajor2>().Where(y => y.StudentName == x.Name).Join(dc.Set<Major2>(), y => y.MajorName, y => y.MajorName, (middle, major) => new { mid = middle, maj = major }).Select(y => y.maj.MajorCode))
                }).ToList();

                var studentsNoKey4 = dc.Set<Student2>().Where(x =>
                            dc.Set<StudentMajor2>().Where(y => y.StudentName == x.Name)
                            .Join(dc.Set<Major2>(), y => y.MajorName, y => y.MajorName, (middle, major) => new { mid = middle, maj = major })
                            .Join(dc.Set<School2>(), y => y.maj.SchoolName, y => y.SchoolName, (last, school) => new { last = last, school = school })
                            .Select(y => y.school.SchoolName).Contains("School1")
               ).ToList();

                var studentsNoKey5 = dc.Set<Student2>().GroupBy(x => x.IsValid).Select(x => new
                {
                    name = x.Key,
                    count = x.Count()
                }).ToList();

                var studentsNoKey6 = dc.Set<Student2>().GroupBy(x => new { x.IsValid, x.EnRollDate }, x => x.Age).Where(x => x.Average() > 15).Select(x => new
                {
                    name = x.Key.IsValid,
                    date = x.Key.EnRollDate,
                    count = x.Count(),
                    aveAge = x.Average()
                }).ToList();

                var studentsNoKey7 = dc.Set<Student2>().SelectMany(x =>
                                    dc.Set<StudentMajor2>().Where(y => y.StudentName == x.Name).Join(dc.Set<Major2>(), y => y.MajorName, y => y.MajorName, (middle, major) => new { mid = middle, maj = major })
                                    .Join(dc.Set<School2>(), y => y.maj.SchoolName, y => y.SchoolName, (last, school) => new { last = last, school = school })
                                    .Select(y => y.school.SchoolName),
                            (a, b) => new
                            {
                                stu = a,
                                school = b
                            }).GroupBy(x => new { x.school, x.stu.EnRollDate }, x => x.stu.Age).Where(x => x.Average() > 15).Select(x => new
                            {
                                school = x.Key.school,
                                date = x.Key.EnRollDate,
                                count = x.Count(),
                                aveAge = x.Average()
                            }).ToList();

            }
            Console.WriteLine("Hello World!");
        }

        static void InitData(MyContext dc)
        {
            //如果是第一次建库
            if(dc.Database.EnsureCreated() == true)
            {
                //初始化有主外键关系的表
                List<School> schools = new List<School>();
                for(int i = 1; i <= 10; i++)
                {
                    School s = new School
                    {
                        SchoolCode = "s00" + i,
                        SchoolName = "School" + i,
                        Majors = new List<Major>()
                    }; 
                    for(int j = 1; j <= 5; j++)
                    {
                        Major m = new Major
                        {
                            MajorCode = "m00" + j,
                            MajorName = $"School{i}_Major{j}"
                        };
                        s.Majors.Add(m);
                    }
                    schools.Add(s);
                }
                Random r = new Random();
                List<Student> students = new List<Student>();
                for (int i = 1; i <= 100; i++)
                {
                    Student stu = new Student
                    {
                        Name = "Student" + i,
                        IsValid = i % 3 == 0,
                        EnRollDate = DateTime.Today.AddDays(i%5),
                        Age = r.Next(10,20)
                    };
                    students.Add(stu);
                }

                List<StudentMajor> middle = new List<StudentMajor>();

                for(int i = 1; i <= 1000; i++)
                {
                    StudentMajor m = new StudentMajor
                    {
                        Major = schools[r.Next(10)].Majors[r.Next(5)],
                        Student = students[r.Next(100)]
                    };
                    middle.Add(m);
                }
                dc.Schools.AddRange(schools);
                dc.Students.AddRange(students);
                dc.Set<StudentMajor>().AddRange(middle);
                dc.SaveChanges();

                //初始化没有主外键关联的表
                List<School2> schools2 = new List<School2>();
                List<Major2> majors2 = new List<Major2>();
                for (int i = 1; i <= 10; i++)
                {
                    School2 s = new School2
                    {
                        SchoolCode = "s00" + i,
                        SchoolName = "School" + i
                    };
                    for (int j = 1; j <= 5; j++)
                    {
                        Major2 m = new Major2
                        {
                            MajorCode = "m00" + j,
                            MajorName = $"School{i}_Major{j}",
                            SchoolName = s.SchoolName
                        };
                        majors2.Add(m);
                    }
                    schools2.Add(s);
                }
                List<Student2> students2 = new List<Student2>();
                for (int i = 1; i <= 100; i++)
                {
                    Student2 stu = new Student2
                    {
                        Name = "Student" + i,
                        IsValid = i % 3 == 0,
                        EnRollDate = DateTime.Today.AddDays(i % 5),
                        Age = r.Next(10, 20)
                    };
                    students2.Add(stu);
                }

                List<StudentMajor2> middle2 = new List<StudentMajor2>();

                for (int i = 1; i <= 1000; i++)
                {
                    StudentMajor2 m = new StudentMajor2
                    {
                        MajorName = majors2[r.Next(50)].MajorName,
                        StudentName = students2[r.Next(100)].Name
                    };
                    middle2.Add(m);
                }
                dc.SchoolsNoFK.AddRange(schools2);
                dc.MajorsNoFK.AddRange(majors2);
                dc.StudentsNoFK.AddRange(students2);
                dc.Set<StudentMajor2>().AddRange(middle2);
                dc.SaveChanges();

            }
        }

    }
}