EF Core 6 & Blazor: Join two tables based on relationship defined in third table-CodePudding

public class accounts
{
    [Key]
    [StringLength(80)]
    public string account_id { get; set; } = string.Empty;

    [StringLength(255)]
    public string firstname { get; set; } = string.Empty;

    [StringLength(255)]
    public string lastname { get; set; } = string.Empty;

    [StringLength(255)]
    public string email { get; set; } = string.Empty;

    [StringLength(255)]
    public string department { get; set; } = string.Empty;

    public DateTime lastmodified { get; set; } = DateTime.Now;
}

public class roles
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
    public long role_id { get; set; } = 0;

    [StringLength(80)]
    public string role_name { get; set; } = string.Empty;

    public DateTime lastmodified { get; set; } = DateTime.Now;
}

//[Keyless]
public class relations
{
    [Required]
    [ForeignKey("roles")]
    public long role_id { get; set; } = 0;

    [Required]
    [ForeignKey("accounts")]
    [StringLength(80)]
    public string account_id { get; set; } = string.Empty;

    public DateTime lastmodified { get; set; } = DateTime.Now;
}

How do I join accounts and roles in EF Core 6 in my controller (C# Blazor)? I have no clue how to write that down.

CodePudding user response：

from a in db.accounts
from ro in db.roles
from r in db.relations
where a.account_id == r.account_id && r.role_id == ro.role_id
select ...