Entity Framework Code First Many to Many with Custom User and Mapping Table

Question

I am working with WebAPI, EF Code First and have a problem concerning many-to-many relationships:

I am working with a custom user inherited from "IdentityUser" who can have many Projects.

These Projects can now have multiple users. In Addition to this I want to store additional fields in a mapping table.

public class MyCustomUser : IdentityUser
{

    // blah blah...

    public virtual ICollection<Project> Projects { get; set; }
}

public class Project
{
    public int Id { get; set; }
    public string Title { get; set; }

    public virtual ICollection<MyCustomUser> Users { get; set; }
}

public class Users2Projects
{
    public int UserId { get; set; }
    public int ProjectId { get; set; }

    public virtual MyCustomUser User { get; set; }
    public virtual Project Project { get; set; }

    public string AdditionalField1 {get;set;}
    public string AdditionalField1 {get;set;}
}

The question is: How do I have to set the relations (FluentAPI) to insert a Project for a User which is already there? Because the user can use the application no matter if he has a Project or not.

So the User CAN have a Project, but a Project has to have at least one User. I am getting the User through the Owin "UserManager" "FindById" which returns "MyCustomUser". Creating a new Users2Project-Class, adding the new Project to it, adding the user to it fails because EF wants to store a new User. How can I solve this?

Kind Regards

Answer 1

You don't need to set anything with FluentAPI when you explicitly declare your many-to-many relationship entity. But you should remove the following lines from User and Project, respectively:

public virtual ICollection<Project> Projects { get; set; }

public virtual ICollection<MyCustomUser> Users { get; set; }

and add on both entities:

public virtual ICollection<Users2Projects> Users2Projects { get; set; }

To add a new Project for an existing User you can:

var relation = new Users2Projects() {
    User = existingUser, // reference retrieved from Context.
    Project = new Project() { Title = "Project Alpha" }
}

Context.Users2Projects.Add(relation);
Context.SaveChanges();

As far as I know, it is not possible to force the Project to have at least one User using a explicit relationship class on Entity Framework, so you would have to check that programmatically upon inserting a Project into the Context:

if (project.Users2Projects.Count() == 0)
    throw new Exception("Project must have at least one user.")

Context.Projects.Add(project);
Context.SaveChanges();

and upon deleting an User you do the check before:

var projectsToDelete = new List<Project>();

foreach (var relationship in user.Users2Projects)
{
    if (relationship.Project.Users2Projects.Count() <= 1)
        projectsToDelete.Add(relationship.Project);
}

Context.MyCustomUsers.Remove(user);
Context.Projects.RemoveRange(projectsToDelete);
Context.SaveChanges();

Answer 2

You are clearly missing a conceptual entity here although you actually already modeled it a bit as your Users2Projects mapping table. You may want to untangle a few concepts around the bounded contexts that exist in your application, and look again at your requirements

1. User administration (i.e. administration of users that are allowed to access your system) seems to be a different concern than project administration:

   Because the user can use the application no matter if he has a Project or not.

2. In project administration, for the assignment of a user to a project, it appears there is additional information specific to the project that needs to be captured as part of the ProjectMemberAssignment. Your Project appears to be the aggregate root for this bounded context, and should be made responsible for managing it's member assignments:

   public class Project
   {
       public virtual int Id { get; private set; }
       public string Title { get; set; }
   
       public void AssignMember(int userId, string someField, string someOtherField)
       {
           if (MemberAssignments.Any(x => x.UserId == userId))
               throw new InvalidOperationException("User already assigned");
           MemberAssignments.Add(new ProjectMemberAssignment(Id, userId, someField, someOtherField));
       }
   
       // Other behavior methods.
   
       public virtual ICollection<ProjectMemberAssignment> MemberAssignments 
       { 
           get;
           set; 
       } 
   }
   

Note that you can get the projects for a user using a simple query, without the need to maintain a many-to-many relationship in your model:

var user = ...;
var projectsForUser =
    from project in db.Projects
    where project.MemberAssignments.Any(x => x.userId == user.UserId)
    select project;