Joining or Including multiple tables with Entity Framework Core LINQ to Entities - One to Many and then Many to One

Question

Setup with Entity Framework Core, EF Migrated the database in a way that make sense to me, now I want to retrieve the data from database and return a list of members with a list of positions held with the full name so I can loop and display the list of records as needed.

Each Member may, rarely, hold more than one position. Each Position may be held by more than one Members.

For example, 3 Members may only be in the Position of "Member", 3 Members hold "Unit Leader", and 1 poor member holds the position of "Unit Administrator Officer" and "Unit Training Officer".

I tried to Include, IncludeThen but that seem to be one to many, one to many type of nesting. Joins and LINQ to entity, table/entity splitting and I'm digging myself deeper into information overload and overthinking.

How do I return a full list of members, their list of positions and the name of the positions they hold?

My attempt so far

public class MembersDB : IMembersDB
{
    private readonly ApplicationDbContext appDbContext;
    public MembersDB(ApplicationDbContext appDbContext)
    {
        this.appDbContext = appDbContext;
    }

    public async Task<IEnumerable<Member>> GetMembers()
    {
        /*var ListOfMembers = appDbContext.Members
            .Select(m => new
            {
                Member = m,
                PositionHeld = m.PositionsHeld,
                Unit = m.Unit
            });
        return await ListOfMembers.ToListAsync(); //error - since not a <IEnumerable<Member>> type*/
    return await appDbContext.Members.ToListAsync(); //Missing Position Name         
    }
}

Trimmed Models

public class Member
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public string MemberId { get; set; } 
    [Required]
    public string FirstName { get; set; }
    [Required]
    public ICollection<PositionsHeld> PositionsHeld { get; set; }
}

[Index(nameof(PositionDisplayName), IsUnique = true)]
public class Position
{
    [Key]
    public int PositionId { get; set; }
    [Required]
    public string PositionDisplayName { get; set; }
    public string PositionFullName { get; set; }
    [Required]
    public int PositionRank { get; set; }
    public ICollection<PositionsHeld> PositionsHelds {get; set;}
}

public class PositionsHeld
{
    [Key]
    public int PositionsHeldId { get; set; }
    public int PositionId { get; set; }
    public Position Position { get; set; }
    public string MemberId { get; set; }
    public Member Member { get; set; }
}

Answer 1

Here is a solution using the signers of the declaration of independence.

The 1st 10 rows of oputput:

Adams, John [Lawyer] 
Adams, Samuel [Merchant] 
Bartlett, Josiah [Physician] 
Braxton, Carter [Plantation Owner] 
Carroll, Charles [Merchant,Plantation Owner] 
Chase, Samuel [Lawyer] 
Clark, Abraham [Lawyer,Surveyer]
Clymer, George [Merchant]
Ellery, William [Lawyer,Merchant]
Floyd, William [Land Speculator]

Here's the code

void Main()
{
    var members = GetMembers();
    var positions = GetPositions();
    var positionsHeld = GetPositionsHeld();
    
    var summary = positionsHeld
        .Join(
            positions, 
            ph => ph.PositionId,
            p => p.PositionId,
            (ph, p) => new { PositionHeld = ph, Position = p })
        .Join(
            members,
            ph => ph.PositionHeld.MemberId,
            m => m.MemberId,
            (ph, m) => new { Member = m, Position = ph.Position })
        .GroupBy(x => x.Member)
        .Select(x => new { x.Key.LastName, x.Key.FirstName, Positions = string.Join(",", x.Select(h => h.Position.PositionFullName).ToList())});
        
    foreach (var line in summary)
    {
        Console.WriteLine($"{line.LastName}, {line.FirstName} [{line.Positions}]");
    }
    
}

public IEnumerable<Member> GetMembers()
{
    return new List<Member>
    {
        new Member { MemberId = "10", FirstName = "John", LastName = "Adams" },
        new Member { MemberId = "11", FirstName = "Samuel", LastName = "Adams" },
        new Member { MemberId = "12", FirstName = "Josiah", LastName = "Bartlett" },
        new Member { MemberId = "13", FirstName = "Carter", LastName = "Braxton" },
        new Member { MemberId = "14", FirstName = "Charles", LastName = "Carroll" },
        new Member { MemberId = "15", FirstName = "Samuel", LastName = "Chase" },
        new Member { MemberId = "16", FirstName = "Abraham", LastName = "Clark" },
        new Member { MemberId = "17", FirstName = "George", LastName = "Clymer" },
        new Member { MemberId = "18", FirstName = "William", LastName = "Ellery" },
        new Member { MemberId = "19", FirstName = "William", LastName = "Floyd" },
        new Member { MemberId = "20", FirstName = "Benjamin", LastName = "Franklin" },
        new Member { MemberId = "21", FirstName = "Elbridge", LastName = "Gerry" },
        new Member { MemberId = "22", FirstName = "Button", LastName = "Gwinnett" },
        new Member { MemberId = "23", FirstName = "Lyman", LastName = "Hall" },
        new Member { MemberId = "24", FirstName = "John", LastName = "Hancock" },
        new Member { MemberId = "25", FirstName = "Benjamin", LastName = "Harrison" },
        new Member { MemberId = "26", FirstName = "John", LastName = "Hart" },
        new Member { MemberId = "27", FirstName = "Joseph", LastName = "Hewes" },
        new Member { MemberId = "28", FirstName = "Thomas", LastName = "Heyward" },
        new Member { MemberId = "29", FirstName = "William", LastName = "Hooper" },
        new Member { MemberId = "30", FirstName = "Stephen", LastName = "Hopkins" },
        new Member { MemberId = "31", FirstName = "Francis", LastName = "Hopkinson" },
        new Member { MemberId = "32", FirstName = "Samuel", LastName = "Huntington" },
        new Member { MemberId = "33", FirstName = "Thomas", LastName = "Jefferson" },
        new Member { MemberId = "34", FirstName = "Francis", LastName = "Lee" },
        new Member { MemberId = "35", FirstName = "Richard", LastName = "Lee" },
        new Member { MemberId = "36", FirstName = "Francis", LastName = "Lewis" },
        new Member { MemberId = "37", FirstName = "Philip", LastName = "Livingston" },
        new Member { MemberId = "38", FirstName = "Thomas", LastName = "Lynch" },
        new Member { MemberId = "39", FirstName = "Thomas", LastName = "McKean" },
        new Member { MemberId = "40", FirstName = "Arthur", LastName = "Middleton" },
        new Member { MemberId = "41", FirstName = "Lewis", LastName = "Morris" },
        new Member { MemberId = "42", FirstName = "Robert", LastName = "Morris" },
        new Member { MemberId = "43", FirstName = "John", LastName = "Morton" },
        new Member { MemberId = "44", FirstName = "Thomas", LastName = "Nelson" },
        new Member { MemberId = "45", FirstName = "William", LastName = "Paca" },
        new Member { MemberId = "46", FirstName = "Robert", LastName = "Paine" },
        new Member { MemberId = "47", FirstName = "John", LastName = "Penn" },
        new Member { MemberId = "48", FirstName = "George", LastName = "Read" },
        new Member { MemberId = "49", FirstName = "Ceasar", LastName = "Rodney" },
        new Member { MemberId = "50", FirstName = "George", LastName = "Ross" },
        new Member { MemberId = "51", FirstName = "Benjamin", LastName = "Rush" },
        new Member { MemberId = "52", FirstName = "Edward", LastName = "Rutledge" },
        new Member { MemberId = "53", FirstName = "Roger", LastName = "Sherman" },
        new Member { MemberId = "54", FirstName = "James", LastName = "Smith" },
        new Member { MemberId = "55", FirstName = "Richard", LastName = "Stockton" },
        new Member { MemberId = "56", FirstName = "Thomas", LastName = "Stone" },
        new Member { MemberId = "57", FirstName = "George", LastName = "Taylor" },
        new Member { MemberId = "58", FirstName = "Matthew", LastName = "Thornton" },
        new Member { MemberId = "59", FirstName = "George", LastName = "Walton" },
        new Member { MemberId = "60", FirstName = "William", LastName = "Whipple" },
        new Member { MemberId = "61", FirstName = "William", LastName = "Williams" },
        new Member { MemberId = "62", FirstName = "James", LastName = "Wilson" },
        new Member { MemberId = "63", FirstName = "John", LastName = "Witherspoon" },
        new Member { MemberId = "64", FirstName = "Oliver", LastName = "Wolcott" },
        new Member { MemberId = "65", FirstName = "George", LastName = "Wythe" }
    };
}

public IEnumerable<Position> GetPositions()
{
    return new List<Position>
    {
        new Position { PositionId = 10, PositionRank = 110, PositionDisplayName = "FA", PositionFullName = "Farmer" },
        new Position { PositionId = 11, PositionRank = 111, PositionDisplayName = "LO", PositionFullName = "Land Owner" },
        new Position { PositionId = 12, PositionRank = 112, PositionDisplayName = "LS", PositionFullName = "Land Speculator" },
        new Position { PositionId = 13, PositionRank = 113, PositionDisplayName = "LA", PositionFullName = "Lawyer" },
        new Position { PositionId = 14, PositionRank = 114, PositionDisplayName = "ME", PositionFullName = "Merchant" },
        new Position { PositionId = 15, PositionRank = 115, PositionDisplayName = "MO", PositionFullName = "Military Officer" },
        new Position { PositionId = 16, PositionRank = 116, PositionDisplayName = "MI", PositionFullName = "Minister" },
        new Position { PositionId = 17, PositionRank = 117, PositionDisplayName = "MU", PositionFullName = "Musician" },
        new Position { PositionId = 18, PositionRank = 118, PositionDisplayName = "PH", PositionFullName = "Physician" },
        new Position { PositionId = 19, PositionRank = 119, PositionDisplayName = "PO", PositionFullName = "Plantation Owner" },
        new Position { PositionId = 20, PositionRank = 120, PositionDisplayName = "SC", PositionFullName = "Scientist" },
        new Position { PositionId = 21, PositionRank = 121, PositionDisplayName = "SU", PositionFullName = "Surveyer" },
        new Position { PositionId = 22, PositionRank = 122, PositionDisplayName = "PR", PositionFullName = "Printer" }
    };
}

public IEnumerable<PositionsHeld> GetPositionsHeld()
{
    return new List<PositionsHeld>
    {
        new PositionsHeld { PositionsHeldId = 10, MemberId = "10", PositionId = 13 },
        new PositionsHeld { PositionsHeldId = 11, MemberId = "11", PositionId = 14 },
        new PositionsHeld { PositionsHeldId = 12, MemberId = "12", PositionId = 18 },
        new PositionsHeld { PositionsHeldId = 13, MemberId = "13", PositionId = 19 },
        new PositionsHeld { PositionsHeldId = 14, MemberId = "14", PositionId = 14 },
        new PositionsHeld { PositionsHeldId = 15, MemberId = "14", PositionId = 19 },
        new PositionsHeld { PositionsHeldId = 16, MemberId = "15", PositionId = 13 },
        new PositionsHeld { PositionsHeldId = 17, MemberId = "16", PositionId = 13 },
        new PositionsHeld { PositionsHeldId = 18, MemberId = "16", PositionId = 21 },
        new PositionsHeld { PositionsHeldId = 19, MemberId = "17", PositionId = 14 },
        new PositionsHeld { PositionsHeldId = 20, MemberId = "18", PositionId = 13 },
        new PositionsHeld { PositionsHeldId = 21, MemberId = "18", PositionId = 14 },
        new PositionsHeld { PositionsHeldId = 22, MemberId = "19", PositionId = 12 },
        new PositionsHeld { PositionsHeldId = 23, MemberId = "20", PositionId = 20 },
        new PositionsHeld { PositionsHeldId = 24, MemberId = "20", PositionId = 22 },
        new PositionsHeld { PositionsHeldId = 25, MemberId = "21", PositionId = 14 },
        new PositionsHeld { PositionsHeldId = 26, MemberId = "22", PositionId = 14 },
        new PositionsHeld { PositionsHeldId = 27, MemberId = "22", PositionId = 19 },
        new PositionsHeld { PositionsHeldId = 28, MemberId = "23", PositionId = 18 },
        new PositionsHeld { PositionsHeldId = 29, MemberId = "23", PositionId = 16 },
        new PositionsHeld { PositionsHeldId = 30, MemberId = "24", PositionId = 14 },
        new PositionsHeld { PositionsHeldId = 31, MemberId = "25", PositionId = 19 },
        new PositionsHeld { PositionsHeldId = 32, MemberId = "25", PositionId = 10 },
        new PositionsHeld { PositionsHeldId = 33, MemberId = "26", PositionId = 11 },
        new PositionsHeld { PositionsHeldId = 34, MemberId = "27", PositionId = 14 },
        new PositionsHeld { PositionsHeldId = 35, MemberId = "28", PositionId = 13 },
        new PositionsHeld { PositionsHeldId = 36, MemberId = "28", PositionId = 19 },
        new PositionsHeld { PositionsHeldId = 37, MemberId = "29", PositionId = 13 },
        new PositionsHeld { PositionsHeldId = 38, MemberId = "30", PositionId = 14 },
        new PositionsHeld { PositionsHeldId = 39, MemberId = "31", PositionId = 13 },
        new PositionsHeld { PositionsHeldId = 40, MemberId = "31", PositionId = 17 },
        new PositionsHeld { PositionsHeldId = 41, MemberId = "32", PositionId = 13 },
        new PositionsHeld { PositionsHeldId = 42, MemberId = "33", PositionId = 13 },
        new PositionsHeld { PositionsHeldId = 43, MemberId = "33", PositionId = 19 },
        new PositionsHeld { PositionsHeldId = 44, MemberId = "33", PositionId = 20 },
        new PositionsHeld { PositionsHeldId = 45, MemberId = "34", PositionId = 19 },
        new PositionsHeld { PositionsHeldId = 46, MemberId = "35", PositionId = 19 },
        new PositionsHeld { PositionsHeldId = 47, MemberId = "35", PositionId = 14 },
        new PositionsHeld { PositionsHeldId = 48, MemberId = "36", PositionId = 14 },
        new PositionsHeld { PositionsHeldId = 49, MemberId = "37", PositionId = 14 },
        new PositionsHeld { PositionsHeldId = 50, MemberId = "38", PositionId = 13 },
        new PositionsHeld { PositionsHeldId = 51, MemberId = "39", PositionId = 13 },
        new PositionsHeld { PositionsHeldId = 52, MemberId = "40", PositionId = 19 },
        new PositionsHeld { PositionsHeldId = 53, MemberId = "41", PositionId = 19 },
        new PositionsHeld { PositionsHeldId = 54, MemberId = "42", PositionId = 14 },
        new PositionsHeld { PositionsHeldId = 55, MemberId = "42", PositionId = 12 },
        new PositionsHeld { PositionsHeldId = 56, MemberId = "43", PositionId = 10 },
        new PositionsHeld { PositionsHeldId = 57, MemberId = "44", PositionId = 14 },
        new PositionsHeld { PositionsHeldId = 58, MemberId = "44", PositionId = 19 },
        new PositionsHeld { PositionsHeldId = 59, MemberId = "45", PositionId = 13 },
        new PositionsHeld { PositionsHeldId = 60, MemberId = "45", PositionId = 19 },
        new PositionsHeld { PositionsHeldId = 61, MemberId = "46", PositionId = 13 },
        new PositionsHeld { PositionsHeldId = 62, MemberId = "46", PositionId = 20 },
        new PositionsHeld { PositionsHeldId = 63, MemberId = "47", PositionId = 13 },
        new PositionsHeld { PositionsHeldId = 64, MemberId = "48", PositionId = 13 },
        new PositionsHeld { PositionsHeldId = 65, MemberId = "49", PositionId = 19 },
        new PositionsHeld { PositionsHeldId = 66, MemberId = "49", PositionId = 15 },
        new PositionsHeld { PositionsHeldId = 67, MemberId = "50", PositionId = 13 },
        new PositionsHeld { PositionsHeldId = 68, MemberId = "51", PositionId = 18 },
        new PositionsHeld { PositionsHeldId = 69, MemberId = "52", PositionId = 13 },
        new PositionsHeld { PositionsHeldId = 70, MemberId = "52", PositionId = 19 },
        new PositionsHeld { PositionsHeldId = 71, MemberId = "53", PositionId = 13 },
        new PositionsHeld { PositionsHeldId = 72, MemberId = "54", PositionId = 13 },
        new PositionsHeld { PositionsHeldId = 73, MemberId = "55", PositionId = 13 },
        new PositionsHeld { PositionsHeldId = 74, MemberId = "56", PositionId = 13 },
        new PositionsHeld { PositionsHeldId = 75, MemberId = "57", PositionId = 14 },
        new PositionsHeld { PositionsHeldId = 76, MemberId = "58", PositionId = 18 },
        new PositionsHeld { PositionsHeldId = 77, MemberId = "59", PositionId = 13 },
        new PositionsHeld { PositionsHeldId = 78, MemberId = "60", PositionId = 14 },
        new PositionsHeld { PositionsHeldId = 79, MemberId = "61", PositionId = 14 },
        new PositionsHeld { PositionsHeldId = 80, MemberId = "62", PositionId = 13 },
        new PositionsHeld { PositionsHeldId = 81, MemberId = "63", PositionId = 16 },
        new PositionsHeld { PositionsHeldId = 82, MemberId = "64", PositionId = 13 },
        new PositionsHeld { PositionsHeldId = 83, MemberId = "65", PositionId = 13 }
    };
}

public class Member
{
    public string MemberId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}
public class Position
{
    public int PositionId { get; set; }
    public string PositionDisplayName { get; set; }
    public string PositionFullName { get; set; }
    public int PositionRank { get; set; }
}
public class PositionsHeld
{
    public int PositionsHeldId { get; set; }
    public int PositionId { get; set; }
    public string MemberId { get; set; }
}