Preface

My database roots are pure SQL. When I create a database, I use an Excel sheet for documentation purposes, which creates SQL statements to build up the tables.

Table relationships (parent / child) with foreign keys I do define by hand, like the good old craftsmen do. It’s simple for me, and having the appropriate SQL code from another project, it takes a few seconds to adapt it to the new tables.

But from time to time one should try something else, so I decided to use the Entity Framework (EF) Code First approach for a new project I started. This post was written to document the required steps for later re-use.

Starting Point

As a starting point, I built an ASP.NET MVC 5 application with EF 6 and “Individual User Accounts” authentication, using Visual Studio 2013 Update 1.

Project Settings

Because the key of the AspNetUsers table is a string containing a GUID, I wanted to add a UserProfile table to map the GUID to an integer. This integer value will be used by the application to map other data to specific users. From my point of view, an integer is much more readable than a GUID.

Adding the Model

Since I was using the Code First approach, I added a class called UserProfile to the models. To link the UserProfile to the user main table, it needs to contain a property of type ApplicationUser. This class is created by the ASP.NET MVC project template and maps to the database table dbo.AspNetUsers.

I also wanted to keep the date and time when a user was created, so the UserProfile class got a CreationDate property. And because it seems that EF Code First does not support database default values, this property is set by the default constructor of the class. As a result, UserProfile looks like this:

public class UserProfile
{
  public long Id { get; set; }

  [Required]
  public DateTime CreationDate { get; set; }

  public ApplicationUser User { get; set; }

  public UserProfile()
  {
    CreationDate = DateTime.Now;
  }
}

To make sure the UserProfile data will be read when the ApplicationUser is accessed, I added a UserProfile property to this class, which is located in the file Models/IdentityModels.cs.

public class ApplicationUser : IdentityUser
{
  public virtual UserProfile UserProfile { get; set; }
}

Try to Create the Database Tables

After creating the database itself using SQL Server Management Studio and changing the connection string in Web.Config, I used the Package Manager Console to create the database tables. The console can be opened via Tools / NuGet Package Manager / Package Manager Console (or use Quick Launch [Ctrl+Q]).

To enable database migration, I first ran Enable-Migrations, which failed.

The error message was “Unable to determine the principal end of an association between the types ‘OneToOneTableRelationship.Models.UserProfile’ and ‘OneToOneTableRelationship.Models.ApplicationUser’. The principal end of this association must be explicitly configured using either the relationship fluent API or data annotations.“.

What does that mean? In short, EF does not know how the UserProfile and ApplicationUser relationship should be organized. Which one is the parent, and which one is the child. There are two ways on how to define this: by the relationship fluent API (means coding) or data annotations (means attributes on class properties).

Declare the Principal End of Association

I decided to use the coding approach and added the method OnModelCreating to the context class ApplicationDbContext (can be found in Models/IdentityModels.cs).

To complete the database context, I also added a DbSet<UserProfile> to gain access to that table.

public class ApplicationDbContext 
  : IdentityDbContext<ApplicationUser>
{
  public DbSet<UserProfile> UserProfile { get; set; }

  public ApplicationDbContext()
    : base("DefaultConnection")
  {
  }

  protected override void OnModelCreating
    (
    DbModelBuilder modelBuilder
    )
  {
    modelBuilder.Entity<UserProfile>()
      .HasRequired<ApplicationUser>(profile => profile.User);

    base.OnModelCreating(modelBuilder);
  }
}

Create the Database Tables

Now that EF knows how to build up the relationship, I created the database tables with two steps at the Package Manager Console.

Add-Migration Initiallize

created the code for the initial table setup. This code is located at the Migration folder.

Update-Database

ran that code and created the database tables.

After the update, the database contains, beside others, these two tables:

AspNetUsers table UserProfiles table

Setting Up the Controller

When Visual Studio creates the controller class from the ASP.NET MVC project template, the class AccountController does not provide a DbContext property. A DbContext is passed as a parameter directly to the newly created UserManager instance by the default constructor. Unfortunately, UserManager does not provide access to the encapsulated DbContext.

To be able to access to the DbContext, I changed the AccountController to look like this:

[Authorize]
public class AccountController : Controller
{
  private ApplicationDbContext DbContext { get; set; }

  public AccountController()
  {
    DbContext = new ApplicationDbContext();

    UserManager 
      = new UserManager<ApplicationUser>(
          new UserStore<ApplicationUser>(DbContext));
    }

// removed: public AccountController(UserManager userManager)

// the rest was left unchanged

Fill the Child Table

Now I was ready to add a new entry to the UserProfile table when a new user registers. The method AccountController.Register was changed like this:

[HttpPost]
[AllowAnonymous]
[ValidateAntiForgeryToken]
public async Task Register(RegisterViewModel model)
{
  if (ModelState.IsValid)
  {
    var user = new ApplicationUser() 
    { 
      UserName = model.UserName, 
      UserProfile = new UserProfile()
    };

    using (TransactionScope scope 
      = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
    {
      var result 
        = await UserManager.CreateAsync(user, model.Password);

      scope.Complete();

      if (result.Succeeded)
      {
        await SignInAsync(user, isPersistent: false);
        return RedirectToAction("Index", "Home");
      }
      else
      {
        AddErrors(result);
      }
    }
  }

  // If we got this far, something failed, redisplay form
  return View(model);
}

Creating the user is wrapped by a database transaction to make sure the user will be created completely or not at all. Because UserManager.CreateAsync is async, the TransactionScope needs to be created with TransactionScopeAsyncFlowOption.Enabled. Please notice that this option is only available for .NET Framework 4.5.1 or above. To use the class TransactionScope, a reference to System.Transaction needs to be added to the project.

The call to UserManager.CreateAsync is doing all the magic. It creates the records in the tables AspNetUsers and UserProfile. It also makes sure that reference between the tables is set properly, means it sets UserProfile.User_Id to the correct value.

Summary

I took the following steps to have my custom user profile data connected to the ASP.NET user table:

Add the child model class
Add a property of the new type to the existing parent class
Declare the principal end of association, i.e. add a OnModelCreating to the DbContext to define which class is parent, and which is child
Create the database tables using the Package Manager Console
Update the controller to be able to store and retrieve the additional data

Consuming the data is easy. Just access the corresponding property like this:

DateTime creationDate = user.UserProfile.CreationDate;

Links

MSDN Data Developer Center: Code First to a New Database

Stack Overflow: What does principal end of an association means in 1:1 relationship in Entity framework

Stack Overflow: Get TransactionScope to work with async / await

Entity Framework: Loading Related Entities

Factory Blog

by Instance Factory - a project of the proccelerate GmbH

Create a 1:1 Table Relationship using Entity Framework