总结了常用的也是比较复杂的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(); } } } }