Entity Framework with composite key of foreign keys error

Question

I'm a bit new to Entity Framework. I have a class defined as follows corresponding to a database table with a composite foreign key made of two columns: GroupID and CompanyID, as well as a third foreign key called RegionID:

[Table("GroupMembers")]
public class GroupMember
{
    [Key, ForeignKey("GroupID"), Column(Order = 1)]
    public int GroupCompanyID { get; set; }

    [Key, ForeignKey("CompanyID"), Column(Order = 2)]
    public int MemberCompanyID { get; set; }

    [Column("MemberCode")]
    public string MemberCompanyCode { get; set; }

    [Column("RegionID")]
    public int RegionId { get; set; }
}

I attempted the following query to retrieve the MemberCode when I have the values for the GroupID and MemberCompanyID:

var GroupMember = await repository.GroupMembers
                                  .FirstOrDefaultAsync(x => x.MemberID == memberId && 
                                                            x.CompanyID == manufacturerId);

I get the following exception:

The property 'GroupID' cannot be configured as a navigation property. The property must be a valid entity type and the property should have a non-abstract getter and setter. For collection properties the type must implement ICollection where T is a valid entity type

I think I'm missing something in the OnModelCreating method, I tried with the following but it didn't work:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<GroupMember>()
            .HasKey(gm => new {gm.GroupID, gm.MemberID});            
}

Another thing to note is that both GroupID and MemberID both of them refer to the same primary key column, which is called OrganizationID in a table called Organizations, just different values. I'm not sure if that affects anything.

Can anyone point me in the right direction? Thanks!

Answer 1

That code is a bit confusing, and I'm not really sure how it even compiled. :) My thoughts are that you'd be looking for something more like:

[Table("GroupMembers")]
public class GroupMember
{
    [Key, ForeignKey("Group"), Column(Order = 1)]
    public int GroupCompanyID { get; set; }

    [Key, ForeignKey("Company"), Column(Order = 2)]
    public int MemberCompanyID { get; set; }

    [Column("MemberCode")]
    public string MemberCompanyCode { get; set; }

    [Column("RegionID")]
    public int RegionId { get; set; }

    public virtual Company Company { get; set; }
    public virtual Group Group { get; set; }
}

[Table("Companies")]
public class Company
{
    [Key]
    public int CompanyID { get; set; }
    public string Name { get; set; }
    // ...
}

[Table("Groups")]
public class Group
{
    [Key]
    public int GroupID { get; set; }
    // ...
}

Navigation properties point to related entities, and EF will wire these up so that you can load their data and reference them via these properties.

Lazy loading: (caution as this can mean extra queries running against the DB)

var groupMember = context.GroupMembers.FirstOrDefault(x => x.RegionId == 4);
var companyName = groupMember.Company.Name; // Context will check if the company is loaded and load it if needed.

Eager loading:

var groupMember = context.GroupMembers
    .Include(x => x.Company)
    .Include(x => x.Group)
    .FirstOrDefault(x => x.RegionId == 4);
var companyName = groupMember.Company.Name; // Member's company (and group) already loaded above.

Select Map: (anonymous type example)

var groupMemberDetails = context.GroupMembers
    .Where(x=> x.RegionId == 4)
    .Select(x => new 
    { 
        x.GroupId,
        x.CompanyId,
        x.RegionId,
        CompanyName = x.Company.Name
    }).FirstOrDefault();

Select can be used to populate something like a ViewModel or DTO which is a very powerful option to utilize EF without risk of tripping lazy load calls. The advantage of this approach is that the query sent to the DB just returns the fields needed to populate the data rather than everything from the entity and related navigation properties.