Loading a list of entities containing the parent as a property

Question

I'm having difficulties acchieving certain functionality when using Entity Framework's Code First approach. Using the Fluent API I was trying to accomplish that the Agencies property of a User loads all Agency entities whose Principal or Agent contain him as a user.

public class User
{
    public int Id { get; set; }
    public string Username { get; set; }
    public IList<Agency> Agencies { get; set; }
}

public class Agency
{
    public int Id { get; set; }

    [Required]
    public User Principal { get; set; }

    [Required]
    public User Agent { get; set; }
}

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

    modelBuilder.Entity<User>().HasMany(u => u.Agencies).WithRequired(a => a.Agent).WillCascadeOnDelete(true);
    modelBuilder.Entity<User>().HasMany(u => u.Agencies).WithRequired(a => a.Principal).WillCascadeOnDelete(true);
}

For some reason only the Agencies whose Principal is set to the owning User are being loaded. Any ideas why or how I could alternatively achieve the functionality I'm asking?

score 0 · Answer 1 · answered Nov 20 '15 at 11:25

You would probably have to introduce some intermediary class that connects user and agency like:

public class AgencyUser {
     public int Id {get;set;}
     public Agency Agency {get;set;}
     public User User {get;set;}
     public UserRole UserRole {get;set;}
}

public enum UserRole {
     Principal,
     Agent
}

So your agency will have a list of AgencyUser inside, and User.Agencies will be a collection of type AgencyUser. Otherwise, I believe it will be much less effective in terms of SQL calls.

score 0 · Accepted Answer · answered Nov 20 '15 at 11:35

You can't have single collection referenced by two foreign keys. You have to make 2 collections, and map each of them to one foreign key.

If you want to have a single collection for easier fetching you can do something like this:

    public IList<Agency> Agencies1 { get; set; }
    public IList<Agency> Agencies2 { get; set; }
    [NotMapped]
    public string AllAgencies
    {
        get
        {
            return Agencies1.Concat(Agencies2).ToList();
        }
    }

Loading a list of entities containing the parent as a property

2 Answers2