EF code first many to many relation when no relation in DB

Question

Suppose I have two classes model like:

 public class AuthorityUser
{
    public string GUID { get; set; } 
    public int UserID { get; set; } 
    public ICollection<Authority1> Authorities { get; set; }
    public AuthorityUser()
    {
        Authorities = new HashSet<Authority1>();
    } 
}
public partial class Authority1
{
    public virtual int AID
    {
        get;
        set;
    }   
    public virtual ICollection<AuthorityUser> AuthorityUsers { get; set; }
    public Authority1()
    { 
        AuthorityUsers = new HashSet<AuthorityUser>();
    }
}

I am going to make Many To Many relation between them based on UserAuthorityMap connected table in DB.

so I did this to make M:N relation in OnModelCreating()

modelBuilder.Entity<AuthorityUser>().ToTable("Gainer").HasKey(x => x.UserID);
modelBuilder.Entity<Authority1>().ToTable("Authority").HasKey(x => x.AID);  
modelBuilder.Entity<AuthorityUser>()
          .HasMany<Authority1>(s => s.Authorities)
          .WithMany(c => c.AuthorityUsers)
          .Map(cs =>
          {
              cs.MapLeftKey("UserID");
              cs.MapRightKey("AID");
              cs.ToTable("UserAuthorityMap");
          });

As I mentioned in title there is no relation between them in DB so the diagram in DB is like picture below :

when I run this :

dbContext.AuthorityUsers.SingleOrDefault(x => x.UserID == 65);

the related Authorities won't be loaded from DB.

so should I use HasDatabaseGeneratedOption(DatabaseGeneratedOption.None) to make it right or something else?

Answer 1

If you followed the Entity Framework Code-First conventions you wouldn't have this problem.

If you really need to use non-conventional names for your tables and your primary keys, then indeed your two ModelBuilder statements for AuthorityUser and Authority will do what you want.

However, to make your many-to-many relationship easier, reconsider your method, and make your life easier by following the entity-framework conventions for many-to-many relation

In your case this would lead to two changes:

• Make AuthorityUser.Authorities virtual
• Let your classes represent your tables: let it be simple POCOs: no HashSet, no Constructor.

The reason to make your table classes simple POCOs, is because the class represents a table in a database. This table has no HashSet, and if you don't need it, why limit yourself to a HashSet? (See later)

In your case the proper many-to-many without the need tell the model builder that you configured a many-to-many would be:

class AuthorityUser
{
    // Primary Key (reconsider: Id)
    public int UserID { get; set; } 
    // an AuthorityUser belongs to zero or more Authorities (many-to-many)
    public virtual ICollection<Authority> Authorities { get; set; }
    ... // other properties
}
class Authority
{
    // primary key (reconsider: Id)
    public int AID {get; set;}
    // an Authority has zero or more AuthorityUsers (many-to-many)  
    public virtual ICollection<AuthorityUser> AuthorityUsers { get; set; }
    ... // other users
}

class MyDbContext : DbContext
{
    public DbSet<AuthorityUser> AuthorityUsers {get; set;}
    public DbSet<Authority> Authorities {get; set;}
}

You already understood that you need some Model Building to inform entity framework about your non-conventional primary keys and table names.

But removing the HashSet and declaring both ICollections in the many-to-many is enough for entity framework to understand that a many-to-many is intended. You don't need to do some model building for this. Enityt Framework will create a junction table and use it whenever needed.

When using the many-to-many you won't do a join with the junction table. Instead you think in collections:

Give me all AuthorityUsers that have xxx with their Authorities that have yyy

var result = dbContext.AuthorityUsers
    .Where(authorityUser => xxx)
    .Select(authorityUser => new
    {
        // take only the properties from authorityuser you'll need:
        UserId = authorityUser.UserId,
        GUID = authorityUser.GUID,

        // take all authorities from this authorityUser that have yyy
        Authorities = authorityUser.Authorities
            .Where(authority => yyy)
            .Select(authority => new
            {
                // take only the authority properties you'll use:
                AID = authority.AID,
                ...
            })
            .ToList(),
    });
}

Entity Framework knows that this needs two joins with the junction table, and perform the proper SQL statement for you.

The query: give me all Authorities that ... with all their AuthorityUsers which ... is similar.

Is your hashset needed?

No, in all your queries, entity framework will replace the HashSet by its own virtual ICollection<...>.

Your HashSet would only be useful if you'd add a new Authority with its AuthorityUsers. Without HashSet this would be like:

Authority addedAuthority = myDbContext.Authorieties.Add(new Authority()
    {
         GUID = ...
         ... // other properties

         // this Authority has the following AuthorityUsers:
         AuthorityUsers = new List<AuthorityUsers>()
         {
              new AuthorityUser() {...},
              new AuthorityUser() {...},
              ...
         },
    });

Instead of a List you couls assign any ICollection, like an array, or even from a Dictionary:

Dictionary<int, AuthorityUser> authorityUsers = ...
Authority addedAuthority = myDbContext.Authorieties.Add(new Authority()
    {
         ...
         // this Authority has the following AuthorityUsers:
         AuthorityUsers = authorityUsers.Values,
    });

So you see that removing the HashSet give you more freedom to provide the ICollection: Better reusability. Less code, which makes it better understandable when someone else needs to maintain it. Besides it is a waste of processing power to create a HashSet that is most of the time not used.

Answer 2

Since Authorities navigation property is not virtual, lazy loading has been turned off and thus you have 2 options left to load them.

Option 1: Eager Loading

dbContext.AuthorityUsers.Include(x => x.Authorities).SingleOrDefault(x => x.UserID == 65);

Note: Include is an extension method in the System.Data.Entity namespace so make sure you are using that namespace.

Option 2: Explicit Loading

var users = dbContext.AuthorityUsers.SingleOrDefault(x => x.UserID == 65);
dbContext.Entry(users).Collection(p => p.Authorities).Load(); 

Please see this article for more details.