ApplicationUser has a list of ApplicationUser

Question

I have built a new Web Application that uses the template Visual Studio provides and included MVC and Web API. The default authorization mechanism is Identity and the database interaction is done using Entity Framework with Code-first method of creating the database.

I have three requirements:

1. A user can have a list of Children objects
2. I do not want to use a "relationship" object
3. All users already exist on the AspNetUsers table, because they all need to be able to login, so I do not want another table to maintain user data

In theory, multiple parents could have reference to multiple children, but for this example, we will just consider it a one-to-many relationship.

In my application, I need to have an ApplicationUser have a list of ChildUsers as a collection of ApplicationUser such as shown below.

public class ApplicationUser : IdentityUser
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string AddressLine1 { get; set; }
    public string AddressLine2 { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string PostalCode { get; set; }
    public string ShirtSize { get; set; }

    public ICollection<ApplicationUser> Children { get; set; }
}

I want these users to be accessible as shown above (a collection of ApplicationUser), not a collection of Relationship object that ties them together such as:

public class Relationship
{
    public String ParentId { get;set; }
    public String ChildId { get;set; }
}

Can a new table be created and exist on the database without having a code-first model for it to know how to create a relationship table?

What are available solutions to this problem?

Answer 1

After some research, and experimentation, I have found bits and pieces of guidance to arrive at a solution that works.

In order for an intermediate table to be created to maintain the relationship, the ApplicationDbContext OnModelCreating function needs to know what it should look like. I have told it to create a new table that is not bound to an object by using the modelBuilder shown in the code below. Unfortunately, I do not have the links to the articles that guided me to this.

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext()
        : base( "DefaultConnection", throwIfV1Schema: false )
    {
    }

    public static ApplicationDbContext Create()
    {
        return new ApplicationDbContext();
    }

    protected override void OnModelCreating( DbModelBuilder modelBuilder )
    {
        base.OnModelCreating( modelBuilder );
        modelBuilder.Entity<ApplicationUser>()
            .HasMany( p => p.ChildUsers )
            .WithMany()
            .Map( m =>
             {
                 m.MapLeftKey( "Father_Id" );
                 m.MapRightKey( "Son_Id" );
                 m.ToTable( "father_son_relation" );
             } );
    }
}

Additionally, when you need to add Children to the parent ApplicationUser, you will need to do some tweaking as you are about to insert so that it updates the database correctly. I definitely want the UserManager to do the creation of the user for me, but that means that when I go to add the user to my list of Children with the code below, it tries to add it again and throws an exception because it already exists.

var result = await UserManager.CreateAsync( user, model.Password );
var myUserId = User.Identity.GetUserId();
var users = AppDbContext.Users.Where( u => u.Id == myUserId ).Include( u => u.ChildUsers );
var u2 = users.First();
u2.ChildUsers.Add( user );
await AppDbContext.SaveChangesAsync();

After finding this question, I researched the EntityStates and found that adding the following line before calling SaveChanges resolved the exception and it no longer attempts to add it again.

AppDbContext.Entry( user ).State = EntityState.Unchanged;

TADA!!! Now to select them from the database using EF, you can then use the following code:

AppDbContext.Users.Where( u => u.Id == myUserId ).Include( u => u.Children ).First();

Since I am only getting one level of Children this will work ok, after that you risk circular references.

Comments and ideas to improve the code are welcome.