Entity Framework part 2: Migrations and Relationships

03/09/2013 Tallmaris programming, 1

In a previous post we had a brief introduction to Entity Framework 5 and Code First Migrations. We created a Task Model for our ToDo application and added a relevant Table to the DB, seeding it with data.

In this post, we will add fields to the existing model and we will add another model, the User, with a one-to-many relationship with Task, meaning that each user may have 0 or more Tasks, while each Task will have one user only. You can google around for a bit more details on what is a one-to-many relationship.

Adding a Column

Let’s first add the Deadline field to our existing Task model:

    public class Task
    {
        public int TaskId { get; set; }
        public string Description { get; set; }
        public bool Completed { get; set; }

        public DateTime? Deadline { get; set; }
    }

I made it nullable because I am thinking that some tasks may not have a deadline.

If you now run:

PM> Add-Migration AddedDeadlineToTask

you should get a second migration file which looks like this:

    public partial class AddedDeadlineToTask : DbMigration
    {
        public override void Up()
        {
            AddColumn("dbo.Tasks", "Deadline", c => c.DateTime());
        }
        
        public override void Down()
        {
            DropColumn("dbo.Tasks", "Deadline");
        }
    }

If you get an error about pending updates, make sure you run Update-Database before adding another migration. If you are used to the “ruby” way this sounds counter intuitive; basically when you run Add-Migration, the cmdlet will look for all changes in the DbContext and in the referenced Models, comparing them with the actual Database and generating the migration code. This means that if you want to create your migrations step by step, you should be sure to run Update-Database in between each migration.

Go ahead and run the Update-Database command. To see what happens more clearly you may use the -Verbose flag:

PM> Update-Database -Verbose
  Using StartUp project 'AwesomeToDoApp'.
  Using NuGet project 'AwesomeToDoApp'.
  Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
  Target database is: 'MyToDoDB' (DataSource: TALLMARIS-PC, Provider: System.Data.SqlClient, Origin: Configuration).
  Applying code-based migrations: [201308011525057_AddedDeadlineToTask].
  Applying code-based migration: 201308011525057_AddedDeadlineToTask.
  ALTER TABLE [dbo].[Tasks] ADD [Deadline] [datetime]
  [Inserting migration history record]
  Running Seed method.

You can see above the SQL command being sent to the Database. Also you will notice a cryptic: [Inserting migration history record]. When EF upgrades your Database, it stores in a table all the migrations it has run; think of it as a versioning mechanism. The table is stored in the “System_Tables” section and it’s called __MigrationHistory, you can query it normally:

Every migration has an Id and a binary serialized representation of the current model; this is how EF knows what to put in the migrations file and how it can detect if the model has changed in code but not in the database.

Adding a Relationship between User and Tasks

Back to our code, let’s create a User model and link it to the Tasks:

    public class User
    {
        public int Id { get; set; }
        public string UserName { get; set; }

        public virtual ICollection<Task> Tasks { get; set; } 
    }

Let’s add the set to the context:

    public class ToDoDbContext : DbContext
    {
        // ...
        // other code
         
        public DbSet<User> Users { get; set; }
    }

Let’s run Add-Migration CreatedUserModel and let’s see the result:

    public override void Up()
    {
        CreateTable(
            "dbo.Users",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    UserName = c.String(),
                })
            .PrimaryKey(t => t.Id);
        
        AddColumn("dbo.Tasks", "User_Id", c => c.Int());
        AddForeignKey("dbo.Tasks", "User_Id", "dbo.Users", "Id");
        CreateIndex("dbo.Tasks", "User_Id");
    }

As you may notice, EF has automatically added another column to the Tasks table, as a Foreign Key to our User table.

Creating Constraints

Another thing you may notice is that the UserName column for the user is just a string field, which means that is is also nullable!

Of course we don’t want that. To fix it, you may go two ways here: either edit the migration file or annotate the User model accordingly and re-run the migration. I prefer the latter, since it will also add some nice validation, which may be also useful for UI purposes, but I will show you both.

The fix to the migration file is all in this line:

  //..
  UserName = c.String(nullable: false, maxLength: 128),
  //..

To obtain the same with annotations, you need to do the following in the User class:

     [Required, StringLength(128)]
     public string UserName { get; set; }

Then run the migration again, but specify the existing migration timestamp, this way we are not creating a second file but overwriting the existing one:

PM> Add-Migration 201308011544088_CreatedUserModel -Force

The migration file in the end will look exactly the same. As a bonus, you may want to add this to the Migration file:

CreateIndex("dbo.Users", "UserName", unique: true);

This will make the UserName unique in the DB. Sadly there is still no annotation for doing this in code.

Update the Database again (Update-Database -Verbose) and look at the generated SQL to check everything is in order. If you open your DB in SQL Management Studio you should see the two tables and the relationship we just created.

One question lingers: what if you had Tasks in the DB prior to the creation of the User?

First of all the update operation would have failed when trying to add the column to the Tasks table, of course. The answer to this is not an easy one and it really depends on what environment you are running in and what are your deployment strategies. In general, if you are in a development environment you could simply delete all the tasks and go ahead with the update, you can even put this in your migration file, before creating the foreign key:

  //..
  Sql("DELETE FROM dbo.Tasks");
  //..

If that’s not feasible, you may need to split the migration in steps:

1. Create the User, with no tasks linkage (no Tasks property on the User model)
2. Seed the data with a dummy User
3. Add the Tasks property back in
4. Add the column to the Tasks with a default value: AddColumn("dbo.Tasks", "User_Id", c => c.Int(defaultValue: 1));

Adding a Many-to-many relationship

As with one-to-many, simple many-to-many relationships are easily taken care of by EF without having to worry much. Let’s add the typical Tag model, which we will assign to the Task:

    public class Tag
    {
        public int Id { get; set; }
        public string Name { get; set; }

        public virtual ICollection<Task> Tasks { get; set; }
    }

    public class Task
    {
        // existing properties
        // ...
        public ICollection<Tag> Tags { get; set; }
    }

Run the Add-Migration command again and look at the migration. You will see that EF created a bridging table for you with two foreign keys, as it should be. Neat, right?

You may wonder why the Tags table has been created even if the DbContext does not have a DbSet property for them. This is because the EF Migrator does not only look at the DbContext, but it also dives down into the relationship between models (navigational properties) and recognises that it has to create a table for them. If you want of course you may still add a Tags DbSet to the Dbcontext.

Many-to-many relationships sometimes have extra properties associated with them. For example let’s assume you want, in a clinical application, associate a patient with a drug: this is a typical many-to-many relationship (patients can have more than one drug prescribed and a drug can be prescribed to different patients), but you need to take into account dosage as well, since different patients may have the same drugs but in different doses. The scenario is similar to the above one, but in this case you will have to create the bridging table yourself (as a model) and instruct EF on how the binding works:

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

        public int PatientId { get; set; }
        public int DrugId { get; set; }

        public virtual Patient Patient { get; set; }
        public virtual Drug Drug { get; set; }

        public int Dose { get; set; }
    }

In both your Patient and Drug models you simply create a navigational property:

    // ... other properties
    public virtual ICollection<PatientPrescription> Prescriptions { get; set; }

In your DbContext class, override the OnModelCreating method:

     protected override void OnModelCreating(DbModelBuilder modelBuilder)
     {
         modelBuilder.Entity<Patient>()
                     .HasMany(pv => pv.Prescriptions)
                     .WithRequired(pr => pr.Patient)
                     .HasForeignKey(pr => pr.PatientId);

         modelBuilder.Entity<Drug>()
                     .HasMany(d => d.Prescriptions)
                     .WithRequired(pr => pr.Drug)
                     .HasForeignKey(pr => pr.DrugId);

         base.OnModelCreating(modelBuilder);
     }

The automatic migration will create the tables for you and automatically assign the foreign keys to the relevant columns.

It is worth noting that you don’t need a navigational property at both ends of the relationship; I tend to leave it only on the root of my Model hierarchy: in this case for example you could leave the Prescriptions property on the Patient and just take it off the Drug model… just remember to remove the relevant configuration code from the OnModelCreating method.

This idea comes from the assumption that you will be more often interested in knowing with Drugs a Patient has taken rather than the opposite… To use DDD terminology you are defining the Patient as your aggregate root, and the Prescription and Drug as Models which are part of that aggregate but not roots themselves. This, however, deserves a discussion on its own.

When I am saying “more often” above is because of course you can already foresee a request for a report answering something like “how many patients have taken that drug?” but that will not be the normal use scenario of your CRUD operations on your Models; also, in that report, you can easily get your answer without having a navigational property.

That should be everything for today, we will look into something a lot more interesting next episodes: how to run the update against multiple database, what strategy to use when deploying and how to run some integration testing on your data context.

asp.net, C#, entityframework, migrations

Continuous Integration with a Custom NuGet server Showing an SVG as image