• 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);
            }
        }
    }
    View Code
    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; }
    
        }
    
    }
    View Code
    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; }
    
        }
    
    
    }
    View Code
    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; }
    
        }
    
    }
    View Code
    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; }
        }
    
    }
    View Code

    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();
    
                }
            }
    
        }
    }
    View Code
  • 相关阅读:
    【转】软链接和硬链接到底有啥作用和区别
    useradd命令详解
    【转】Linux下MySQL数据库安装及配置方法
    【转】MySQL的安装与配置——详细教程-window系统下
    mysql服务器常用命令
    【转】DDL/DML/DCL区别概述
    tmux终端工具的简单使用
    linux go环境安装和基本项目结构
    ClickHouse高可用集群的配置
    centos7下使用rpm包安装clickhouse
  • 原文地址:https://www.cnblogs.com/zhao987/p/14213338.html
Copyright © 2020-2023  润新知