Пример EFCore Sqlite

ef2022-10-13T01-48-20.log

dbug: 13.10.2022 13:48:21.799 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT COUNT(*) FROM "sqlite_master" WHERE "type" = 'table' AND "rootpage" IS NOT NULL;
-----------
dbug: 13.10.2022 13:48:22.170 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT EXISTS (
          SELECT 1
          FROM "Students" AS "s")
-----------
dbug: 13.10.2022 13:48:22.194 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT COUNT(*)
      FROM "Students" AS "s"
-----------
dbug: 13.10.2022 13:48:22.228 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT COUNT(*)
      FROM "Students" AS "s"
-----------
dbug: 13.10.2022 13:48:22.231 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT COUNT(*)
      FROM "Groups" AS "g"
-----------
dbug: 13.10.2022 13:48:22.233 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT COUNT(*)
      FROM "Courses" AS "c"
-----------
dbug: 13.10.2022 13:48:22.460 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "g"."Name", (
          SELECT COUNT(*)
          FROM "Students" AS "s"
          WHERE "g"."Id" = "s"."GroupId") AS "StudentsCount", (
          SELECT AVG("s0"."Rating")
          FROM "Students" AS "s0"
          WHERE "g"."Id" = "s0"."GroupId") AS "AverageRating"
      FROM "Groups" AS "g"
-----------
dbug: 13.10.2022 13:48:22.521 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[@__p_0='3'], CommandType='Text', CommandTimeout='30']
      SELECT "g"."Id", "g"."Name"
      FROM "Groups" AS "g"
      ORDER BY (
          SELECT AVG("s"."Rating")
          FROM "Students" AS "s"
          WHERE "g"."Id" = "s"."GroupId") DESC
      LIMIT @__p_0
-----------
dbug: 13.10.2022 13:48:22.607 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[@__p_0='3'], CommandType='Text', CommandTimeout='30']
      SELECT "g"."Name", (
          SELECT AVG("t0"."Rating")
          FROM (
              SELECT "s0"."Id", "s0"."Age", "s0"."GroupId", "s0"."Name", "s0"."Rating"
              FROM "Students" AS "s0"
              WHERE "g"."Id" = "s0"."GroupId"
              ORDER BY "s0"."Rating"
              LIMIT 5 OFFSET 2
          ) AS "t0") AS "AverageRating"
      FROM "Groups" AS "g"
      ORDER BY (
          SELECT AVG("t"."Rating")
          FROM (
              SELECT "s"."Id", "s"."Age", "s"."GroupId", "s"."Name", "s"."Rating"
              FROM "Students" AS "s"
              WHERE "g"."Id" = "s"."GroupId"
              ORDER BY "s"."Rating"
              LIMIT 5 OFFSET 2
          ) AS "t") DESC
      LIMIT @__p_0
-----------

=====================================================
dbug: 13.10.2022 13:48:22.637 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[@__p_0='5'], CommandType='Text', CommandTimeout='30']
      SELECT "t"."Name", "g"."Name" AS "GroupName"
      FROM (
          SELECT "s"."GroupId", "s"."Name", "s"."Rating"
          FROM "Students" AS "s"
          ORDER BY "s"."Rating" DESC
          LIMIT @__p_0
      ) AS "t"
      INNER JOIN "Groups" AS "g" ON "t"."GroupId" = "g"."Id"
      ORDER BY "t"."Rating" DESC
-----------

EFTest.csproj

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net6.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="6.0.10" />
    <PackageReference Include="Serilog.Extensions.Logging.File" Version="3.0.0" />
  </ItemGroup>

</Project>

Program.cs

using EFTest;
using Microsoft.EntityFrameworkCore;

var log_file_name = $"ef{DateTime.Now:yyyy-MM-ddThh-mm-ss}.log";
var options = new DbContextOptionsBuilder<StudentsDB>()
    .UseSqlite("Data source=students.db")
    .EnableSensitiveDataLogging() // Включаем в журнал конфиденциальную информацию (значения параметров)
    .UseLoggerFactory(LoggerFactory.Create(builder => builder.AddFile("log.txt")))
    .LogTo(log => 
    {
        // Фильтруем в журнал записи о выполнении команд
        if(log.Contains("Executing"))
            File.AppendAllText(log_file_name, log + "\r\n-----------\r\n");
    })
    .Options;

Console.WriteLine("Инициализация БД");
using(var db = new StudentsDB(options))
{
    db.Database.EnsureCreated();

    if(!db.Students.Any())
    {
        Console.WriteLine("Студенты в БД не найдены. Создаю новых");

        var rnd = new Random(5);

        var groups = Enumerable.Range(1, 10)
            .Select(i => new StudentsGroup
            {
                Name = $"Group-{i}"
            })
            .ToArray();

        var courses = Enumerable.Range(1, 10)
            .Select(i => new Course { Name = $"Course-{i}" })
            .ToArray();

        var students = Enumerable.Range(1, 1000)
            .Select(i => new Student
            {
                Name = $"Student-{i}",
                Rating = rnd.NextDouble() * 100,
                Group = groups[rnd.Next(groups.Length)],
                Courses = Enumerable.Range(0, rnd.Next(courses.Length))
                    .Select(i => courses[rnd.Next(courses.Length)])
                    .Distinct()
                    .ToList()
            })
            .ToArray();

            db.Students.AddRange(students);

            db.SaveChanges();
    }
    else
        Console.WriteLine("В БД содержится записей о студентах {0}", db.Students.Count());
}
Console.WriteLine();

Console.WriteLine("Число записей в таблицах");
using(var db = new StudentsDB(options))
{
    Console.WriteLine("Студентов в БД {0}", db.Students.Count());
    Console.WriteLine("Групп в БД {0}", db.Groups.Count());
    Console.WriteLine("Курсов в БД {0}", db.Courses.Count());
}
Console.WriteLine();

Console.WriteLine("Число студентов в группах");
using(var db = new StudentsDB(options))
{
    foreach(var group in db.Groups.Select(g => new { g.Name, StudentsCount = g.Students.Count(), AverageRating = g.Students.Average(s => s.Rating) }))
    {
        Console.WriteLine("{0}({1}) : {2}", group.Name, group.StudentsCount, group.AverageRating);
    }
}
Console.WriteLine();

Console.WriteLine("Лучше 3 группы по среднему баллу студентов");
using(var db = new StudentsDB(options))
{
    var best_groups = db.Groups
        .OrderByDescending(g => g.Students.Average(s => s.Rating))
        .Take(3);

    foreach(var group in best_groups)
    {
        Console.WriteLine(group.Name);
    }
}
Console.WriteLine();

Console.WriteLine("5 худших групп студентов без двух самых худших по среднему баллу");
using(var db = new StudentsDB(options))
{
    var best_groups = db.Groups
        .Select(g => new { Name = g.Name, AverageRating = g.Students.OrderBy(s => s.Rating).Skip(2).Take(5).Average(s => s.Rating)})
        .OrderByDescending(g => g.AverageRating)
        .Take(3);

    foreach(var group in best_groups)
    {
        Console.WriteLine("{0} : {1}", group.Name, group.AverageRating);
    }
}
Console.WriteLine();

File.AppendAllText(log_file_name, "\r\n=====================================================\r\n");

Console.WriteLine("5 лучших студентов");
using(var db = new StudentsDB(options))
{
    var students = db.Students.OrderByDescending(s => s.Rating).Take(5);
    // foreach(var s in students)
    // {
    //     Console.WriteLine("{0} group id: {1}", s.Name, s.GroupId);
    // }

    // Console.WriteLine("---------------");

    var stud_groups = students.Join(
        db.Groups, 
        s => s.GroupId, 
        g => g.Id, 
        (s, g) => new { s.Name, GroupName = g.Name });

    foreach(var s in stud_groups)
    {
        Console.WriteLine("{0} : {1}", s.Name, s.GroupName);
    }
}
Console.WriteLine();

Console.WriteLine("Вывод структуры БД");
using(var db = new StudentsDB(options))
{
    using var connection = db.Database.GetDbConnection();
    connection.Open();
    using var command = connection.CreateCommand();
    command.CommandText = "SELECT * FROM sqlite_schema";

    using var reader = command.ExecuteReader();
    while(reader.Read())
    {
        Console.WriteLine("{0}: {1}\r\n{2}", reader[0], reader[1], reader[4]);
        Console.WriteLine();
    }
}
Console.WriteLine();

Console.WriteLine("End.");

StudentsDB.cs

using Microsoft.EntityFrameworkCore;

namespace EFTest;

class StudentsDB : DbContext
{
    public DbSet<Student> Students {get;set;}    
    public DbSet<StudentsGroup> Groups {get;set;}
    public DbSet<Course> Courses { get; set; }

    public StudentsDB(DbContextOptions<StudentsDB> options) : base(options)
    {
        
    }
}

class Student
{
    public int Id { get; set; }

    public string Name { get; set; }

    public int Age { get; set; }

    public double Rating { get; set; }

    public int GroupId { get; set; }

    public StudentsGroup Group { get; set; }

    public ICollection<Course> Courses { get; set; }
}

class StudentsGroup
{
    public int Id { get; set; }

    public string Name { get; set; }

    public ICollection<Student> Students {get;set;}
}

class Course
{
    public int Id { get; set; }

    public string Name { get; set; }
}