Entity Framework Code First Design

Question

I'm doing the design for a Councillors Directory using Entity Framework 6 and am after some guidance on how to structure my entities. Been a while since I've done anything from scratch like this, any help would be much appreciated. I'm more used to doing this database first, rather than code first and it's taking awhile to switch how I'm thinking about things.

I have two entities:

public class Councillor
{
    [Key]
    public int CouncillorId { get; set; }
    public string Name { get; set; }
    public ICollection<Committee> Committees { get; set; }
}

public class Committee
{
    [Key]
    public int CommitteeId { get; set; }
    public string CommitteeName { get; set; }
    public ICollection<Councillor> Councillors { get; set; }
}

There is a many to many relationship in that a Councillor can sit on many Committees and a Committee has many Councillors.

To add to this a Committee has Councillors that act as Chairmen, Vice-Chairman or just Members. The List handles the later, but I'm looking for suggestion (best practice) on setting the other positions.

I'm currently thinking of going with:

public class Committee
{
    [Key]
    public int CommitteeId { get; set; }
    public string CommitteeName { get; set; }
    public int ChairmanId { get; set; } // link to councillor entity
    public int ViceChairId { get; set; } // link to councillor entity
    public ICollection<Councillor> Councillors { get; set; }
}

Any suggestions or problems with this approach? In database first I'd likely just have a link table for members of committees with CouncillorId, CommitteeId and Position. But can't get my head around how to translate that to code first.

Answer 1

It sounds like you just need to add a junction table to join your councillors to committees. I'd keep the chairman and vice chairman properties on the committee object.

public class Councillor
{
    [Key]
    public int CouncillorId { get; set; }

    public string Name { get; set; }

    // Junction Table Navigation Property
    [ForeignKey("CouncillorId")]
    public virtual ICollection<CouncillorCommittee> CouncilorCommittees { get; set; }
}

public class Committee
{
    [Key]
    public int CommitteeId { get; set; }

    public string Name { get; set; }

    public int ChairmanId { get; set; }

    public int ViceChairmanId { get; set; }

    [ForeignKey("ChairmanId")]
    public virtual Councillor Chairman { get; set; }

    [ForeignKey("ViceChairmanId")]
    public virtual Councillor ViceChairman { get; set; }

    // Junction Table Navigation Property
    [ForeignKey("CommitteeId")]
    public virtual ICollection<CouncillorCommittee> CouncilorCommittees { get; set; }
}

// Represents the joining table
public class CouncillorCommittee
{
    [Key]
    public int CouncillorCommitteeId { get; set; }

    public int CouncillorId { get; set; }

    public int CommitteeId { get; set; }

    [ForeignKey("CouncillorId")]
    public virtual Councillor Councillor { get; set; }

    [ForeignKey("CommitteeId")]
    public virtual Committee Committee { get; set; }
}

Within your code, you would then have something like this to get a list of committees and the councillors:

var committees =  from c
                  in Committees
                  select new
                  {
                      CommitteeName = c.Name,
                      Chairman = c.Chairman,
                      ViceChairman = c.ViceChairman,
                      Councillors = from cc
                                    in c.CouncilorCommittees
                                    select cc.Councillor
                  }

Similarly, to get all of the councillors and the committes that they belong to, you could do something like this:

var councillors = from c
                  in Councillors
                  select new
                  {
                      CouncillorName = c.Name,
                      Committes = from cc
                                    in c.CouncilorCommittees
                                    select cc.Committee
                  }