EF Core 5 relacja wiele do wielu

AddData.cs

private static async Task AddData()
{
    using (var db = new DataContext())
    {
        var project1 = new Project()
        {
            Name = "project 1"
        };

        await db.Projects.AddAsync(project1);

        var project2 = new Project()
        {
            Name = "project 2"
        };

        await db.Projects.AddAsync(project2);

        var employee = new Employee()
        {
            FirstName = "Daniel",
            LastName = "Plawgo",
            Projects = new List<Project>()
            {
                project1,
                project2
            }
        };

        await db.Employees.AddAsync(employee);

        await db.SaveChangesAsync();
    }
}

AddData.log

INSERT INTO "Employees" ("Id", "FirstName", "LastName")
VALUES (@p0, @p1, @p2);

INSERT INTO "Projects" ("Id", "Name")
VALUES (@p0, @p1);

INSERT INTO "Projects" ("Id", "Name")
VALUES (@p0, @p1);

INSERT INTO "EmployeeProject" ("EmployeesId", "ProjectsId")
VALUES (@p2, @p3);

INSERT INTO "EmployeeProject" ("EmployeesId", "ProjectsId")
VALUES (@p0, @p1);

AddDataWithRole.cs

private static async Task AddDataWithRole()
{
    using (var db = new DataContext())
    {
        var project3 = new Project()
        {
            Name = "project 3"
        };

        await db.Projects.AddAsync(project3);

        var employee = await db.Employees
            .FirstOrDefaultAsync();

        var employeeProject = new EmployeeProject()
        {
            Project = project3,
            Employee = employee,
            Role = "Owner"
        };

        employee.EmployeeProjects.Add(employeeProject);

        await db.SaveChangesAsync();
    }
}

DataContext.cs

public class DataContext : DbContext
{
    public DataContext()
    {
    }

    public DataContext(DbContextOptions options) : base(options)
    {
    }

    public DbSet<Project> Projects { get; set; }

    public DbSet<Employee> Employees { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite("Filename=EFCoreManyToMany.db", options =>
        {
            options.MigrationsAssembly(this.GetType().Assembly.FullName);
        });
        base.OnConfiguring(optionsBuilder);
    }
}

Employee.cs

public class Employee
{
    public Guid Id { get; set; } = Guid.NewGuid();

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public virtual ICollection<Project> Projects { get; set; } = new List<Project>();
}

Employee2.cs

public class Employee
{
    public Guid Id { get; set; } = Guid.NewGuid();

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public virtual ICollection<Project> Projects { get; set; } = new List<Project>();
    public virtual ICollection<EmployeeProject> EmployeeProjects { get; set; } = new List<EmployeeProject>();
}

EmployeeProject.cs

public class EmployeeProject
{
    public Guid EmployeesId { get; set; }
    public virtual Employee Employee { get; set; }

    public Guid ProjectsId { get; set; }
    public virtual Project Project { get; set; }

    public string Role { get; set; }
}

Initial.cs

public partial class Initial : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Employees",
            columns: table => new
            {
                Id = table.Column<Guid>(type: "TEXT", nullable: false),
                FirstName = table.Column<string>(type: "TEXT", nullable: true),
                LastName = table.Column<string>(type: "TEXT", nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Employees", x => x.Id);
            });

        migrationBuilder.CreateTable(
            name: "Projects",
            columns: table => new
            {
                Id = table.Column<Guid>(type: "TEXT", nullable: false),
                Name = table.Column<string>(type: "TEXT", nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Projects", x => x.Id);
            });

        migrationBuilder.CreateTable(
            name: "EmployeeProject",
            columns: table => new
            {
                EmployeesId = table.Column<Guid>(type: "TEXT", nullable: false),
                ProjectsId = table.Column<Guid>(type: "TEXT", nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_EmployeeProject", x => new { x.EmployeesId, x.ProjectsId });
                table.ForeignKey(
                    name: "FK_EmployeeProject_Employees_EmployeesId",
                    column: x => x.EmployeesId,
                    principalTable: "Employees",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
                table.ForeignKey(
                    name: "FK_EmployeeProject_Projects_ProjectsId",
                    column: x => x.ProjectsId,
                    principalTable: "Projects",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
            });

        migrationBuilder.CreateIndex(
            name: "IX_EmployeeProject_ProjectsId",
            table: "EmployeeProject",
            column: "ProjectsId");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "EmployeeProject");

        migrationBuilder.DropTable(
            name: "Employees");

        migrationBuilder.DropTable(
            name: "Projects");
    }
}

OnModelCreating.cs

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Project>()
        .HasMany(p => p.Employees)
        .WithMany(p => p.Projects)
        .UsingEntity<EmployeeProject>(
            j => j
                .HasOne(pt => pt.Employee)
                .WithMany(t => t.EmployeeProjects)
                .HasForeignKey(pt => pt.EmployeesId),
            j => j
                .HasOne(pt => pt.Project)
                .WithMany(p => p.EmployeeProjects)
                .HasForeignKey(pt => pt.ProjectsId),
            j =>
            {
                j.Property(pt => pt.Role).HasDefaultValueSql("'employee'");
                j.HasKey(t => new { t.EmployeesId, t.ProjectsId });
            });
}

Project.cs

public class Project
{
    public Guid Id { get; set; } = Guid.NewGuid();

    public string Name { get; set; }

    public virtual ICollection<Employee> Employees { get; set; } = new List<Employee>();
}

Project2.cs

public class Project
{
    public Guid Id { get; set; } = Guid.NewGuid();

    public string Name { get; set; }

    public virtual ICollection<Employee> Employees { get; set; } = new List<Employee>();
    public virtual ICollection<EmployeeProject> EmployeeProjects { get; set; } = new List<EmployeeProject>();
}

ShowData.cs

private static async Task ShowData()
{
    using (var db = new DataContext())
    {
        var employee = await db.Employees
            .Include(e => e.Projects)
            .FirstOrDefaultAsync();

        Console.WriteLine($"{employee.FirstName} {employee.LastName}:");
        foreach (var project in employee.Projects)
        {
            Console.WriteLine($"\t{project.Name}");
        }
    }
}

ShowData.log

SELECT "t"."Id", "t"."FirstName", "t"."LastName", "t0"."EmployeesId", "t0"."ProjectsId", "t0"."Id", "t0"."Name"
FROM (
    SELECT "e"."Id", "e"."FirstName", "e"."LastName"
    FROM "Employees" AS "e"
    LIMIT 1
) AS "t"
LEFT JOIN (
    SELECT "e0"."EmployeesId", "e0"."ProjectsId", "p"."Id", "p"."Name"
    FROM "EmployeeProject" AS "e0"
    INNER JOIN "Projects" AS "p" ON "e0"."ProjectsId" = "p"."Id"
) AS "t0" ON "t"."Id" = "t0"."EmployeesId"
ORDER BY "t"."Id", "t0"."EmployeesId", "t0"."ProjectsId", "t0"."Id"

ShowData2.log

--Metoda: ShowData
SELECT "t"."Id", "t"."FirstName", "t"."LastName", "t0"."EmployeesId", "t0"."ProjectsId", "t0"."Role", "t0"."Id", "t0"."Name"
FROM (
    SELECT "e"."Id", "e"."FirstName", "e"."LastName"
    FROM "Employees" AS "e"
    LIMIT 1
) AS "t"
LEFT JOIN (
    SELECT "e0"."EmployeesId", "e0"."ProjectsId", "e0"."Role", "p"."Id", "p"."Name"
    FROM "EmployeeProject" AS "e0"
    INNER JOIN "Projects" AS "p" ON "e0"."ProjectsId" = "p"."Id"
) AS "t0" ON "t"."Id" = "t0"."EmployeesId"
ORDER BY "t"."Id", "t0"."EmployeesId", "t0"."ProjectsId", "t0"."Id"

--Metoda: ShowDataWithRole
SELECT "t"."Id", "t"."FirstName", "t"."LastName", "t0"."EmployeesId", "t0"."ProjectsId", "t0"."Role", "t0"."Id", "t0"."Name"
FROM (
    SELECT "e"."Id", "e"."FirstName", "e"."LastName"
    FROM "Employees" AS "e"
    LIMIT 1
) AS "t"
LEFT JOIN (
    SELECT "e0"."EmployeesId", "e0"."ProjectsId", "e0"."Role", "p"."Id", "p"."Name"
    FROM "EmployeeProject" AS "e0"
    INNER JOIN "Projects" AS "p" ON "e0"."ProjectsId" = "p"."Id"
) AS "t0" ON "t"."Id" = "t0"."EmployeesId"
ORDER BY "t"."Id", "t0"."EmployeesId", "t0"."ProjectsId", "t0"."Id"

ShowDataWithRole.cs

private static async Task ShowDataWithRole()
{
    using (var db = new DataContext())
    {
        var employee = await db.Employees
            .Include(e => e.EmployeeProjects)
            .ThenInclude(p => p.Project)
            .FirstOrDefaultAsync();

        Console.WriteLine($"{employee.FirstName} {employee.LastName}:");
        foreach (var item in employee.EmployeeProjects)
        {
            Console.WriteLine($"\t{item.Project.Name} - {item.Role}");
        }
    }
}