Many-to-many relationship in EF user instance is NULL

Question

I'm using .net core 2 mvc, I tried to build many-to-many relationship between Users and Steps. the relationship is doen but when I query for the record I get user = null.

Hier is my code:

(applicationUser model):

 public class ApplicationUser : IdentityUser
{
    public string Name { get; set; }
    public List<StepsUsers> StepUser { get; set; }
}

(Steps model):

public class Steps
{
    public int Id { get; set; }
    [Required]
    public string Name { get; set; }
    public List<StepsUsers> StepUser { get; set; }

}

StepsUsers model:

public class StepsUsers : IAuditable
{
    public int StepId { get; set; }
    public Steps Step { get; set; }

    public string UserId { get; set; }
    public ApplicationUser User { get; set; }
}

In DbContext I did this :

protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);           
        builder.Entity<StepsUsers>()
        .HasKey(s => new { s.StepId, s.UserId });

        builder.Entity<StepsUsers>()
        .HasOne(su => su.Step)
        .WithMany(s => s.StepUser)
        .HasForeignKey(su => su.StepId);

        builder.Entity<StepsUsers>()
        .HasOne(su => su.User)
        .WithMany(s => s.StepUser)
        .HasForeignKey(su => su.UserId);
    }
public DbSet<MyApp.Models.StepsUsers> StepsUsers { get; set; }

Now, when I query for an instance of StepsUsers with specific StepId I get all de fields correct except the User field is null

var stepUsers = await _context.StepsUsers.Where(s => s.StepId == id).ToListAsync();

I did the same code for another two tables and it works fine, I don't know why it is like this, any suggestion 1?

Answer 1

The cause of your problems is that your forgot to declare your To-many relations as virtual. Another improvement would be to declare them as virtual ICollection instead of List. After all, what would ApplicationUser.StepUser[4] mean?

If you configure a many-to-many relationship according to the entity framework conventions for many-to-many, you don't need to mention the junction table (StepsUsers). Entity framework will recognize the many-to-many and will create the junction table for you. If you stick to the code first conventions you won't even need the fluent API to configure the many-to-many.

In your design every ApplicationUser has zero or more Steps and every Step is done by zero or more ApplicationUsers.

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

    // every ApplicationUser has zero or more Steps:
    public virtual ICollection<Step> Steps {get; set;}

    public string Name {get; set;}
    ...
}

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

    // every Step is performed by zero or more ApplicationUsers:
    public virtual ICollection<ApplicationUser> ApplicationUsers {get; set;}

    public string Name {get; set;}
    ...
}

public MyDbContext : DbContext
{
    public DbSet<ApplicationUser ApplictionUsers {get; set;}
    public DbSet<Step> Steps {get; set;}
}

This is all entity framework needs to know to recognize that you configured a many-to-many relationship. Entity framework will create the junction table for you and the foreign keys to the junction table. You don't need to declare the junction table.

But how am I suppose to do a join if I don't have the junction table?

The answer is: Don't do the join. Use the collections instead.

If you want all ApplicationUsers that ... with all their Steps that ... you would normally do an inner join with the junction table, and do some group by to get the Application users. Ever tried method syntax to join three tables? They look hideous, difficult to understand, error prone and difficult to maintain.

Using the collections in entity framework your query would be much simpler:

var result = myDbContext.ApplicationUsers
    .Where(applicationUser => applicationUser.Name == ...)
    .Select(applicationUser => new
    {
         // select only the properties you plan to use:
         Name = applicationUser.Name,
         Steps = applicationUser.Steps
             .Where(step => step.Name == ...)
             .Select(step => new
             {
                 // again fetch only Step properties you  plan to use
                 Name = step.Name,
                 ...
             })
             .ToList(),
     });

Entity framework will recognize that joins with the junction table is needed and perform them for you.

If you want Steps that ... with their ApplicationUsers who ... you'll do something similar:

var result = myDbContext.Steps
   .Where(step => ...)
   .Select(step => new
    {
        Name = step.Name,
        ... // other properties
        ApplicationUsers = step.ApplicationUsers
            .Where(applicationUser => ...)
            .Select(applicationUser => new
            {
               ...
            })
            .ToList(),
    });

In my experience, whenever I think of performing a query with a of DbSets using entity framework, whether it is in a many-to-many, a one-to-many or a one-to-one relation, the query can almost always be created using the collections instead of a join. They look simpler, they are better to understand and thus better to maintain.