code-first

Using Code-First Migrations in Entity Framework

Entity Framework Code-First includes a new feature for maintaining database changes in code files: Migrations.

Migrations are code files that describe what changes were made to the database schema at different points in time. You create the migrations when you need them, and the code will use those files (and a corresponding table in the target database) to keep the schema changes in sync with what the code expects. In other words, you can manage your database schema without ever writing any SQL.

For this demo, we'll be using the same sample project we used when creating a Code-First model using Attributes and when creating that same model using FluentAPI. Let's get started!

Enabling Migrations

Before we can use them, we must enable Migrations for a given project. Migrations are enabled and interfaced with via a command-line-like interface in Visual Studio called the Package Manager Console. In order to enable migrations, we must run the following command on that window:

PM> enable-migrations  

That creates a Migrations folder containing a new class called Configuration:

In the Configuration class, we'll see two methods: the constructor and Seed().

The constructor sets one particularly important property: AutomaticMigrationsEnabled. This property determines whether or not the database will automatically roll to the newest migration whenever the code is deployed and accessed. This can be pretty useful, but also fairly dangerous if you fail to set up your migrations properly.

The other method, Seed(), can seed the database with sample data should you need such. You can find a nice walkthrough of this over at asp.net.

Creating a Migration

Once we've got migrations enabled, we can create the first one. We'll do that via this command:

PM> add-migration InitialSetup  

This adds a file to our Migrations folder:

Note that the name of the file is simply a timestamp followed by the name of the migration. This naming is important, because Migrations keeps these files in chronological order.

Contents of a Migration

Inside the newly-created migration file we will find two methods: Up() and Down().

public partial class InitialSetup : DbMigration  
{
    public override void Up()
    {
        CreateTable(
            "dbo.Courses",
            c => new
                {
                    ID = c.Int(nullable: false, identity: true),
                    Name = c.String(nullable: false),
                    TeacherID = c.Int(nullable: false),
                })
            .PrimaryKey(t => t.ID)
            .ForeignKey("dbo.Teachers", t => t.TeacherID, cascadeDelete: true)
            .Index(t => t.TeacherID);

        CreateTable(
            "dbo.Students",
            c => new
                {
                    ID = c.Int(nullable: false, identity: true),
                    FirstName = c.String(nullable: false, maxLength: 100),
                    LastName = c.String(nullable: false, maxLength: 100),
                    DateOfBirth = c.DateTime(nullable: false),
                })
            .PrimaryKey(t => t.ID);

        ...
    }

    public override void Down()
    {
        DropForeignKey("dbo.Courses", "TeacherID", "dbo.Teachers");
        ...
        DropTable("dbo.Teachers");
        DropTable("dbo.StudentAddresses");
        DropTable("dbo.Students");
        DropTable("dbo.Courses");
    }
}

As you probably guessed, the Up() method is used when applying the migration to a database; it specifies what tables to create, as well as establishing keys, indexes, and relationships.

The Down() method is more interesting though, because it is used to rollback a migration. That's right, you can use Migrations to rollback changes made to your database. We'll see how to do this in just a second, but first we need to update the database.

Updating the Database

Once we've got the migration created, we can update the database by using the command

update-database  

There's also a few flags we can use for this command (there are more):

  • -script: Generates a SQL Script rather than updating the database.
  • -force: Specifies that we are to ignore possible data loss when making this update.
  • -verbose: Outputs the specific SQL statements to the PMC that are being run.

Running this command produces the following tables:

Now our database is up-to-date with our migrations. But how does it keep track?

Notice the __MigrationHistory table. In this table, Entity Framework will store each Migration that has been executed against the database. This is how EF knows which migration to run when executing update-database; it just looks for migrations that don't exist in this table.

Making a Schema Change

Let's say we wanted to add a new entity for Building. Each Building can host many Courses, so their relationship is Building 1-to-n Course. The model might look something like this:

public class Building  
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int BuildingID { get; set; }

    [Required]
    public string Name { get; set; }

    public virtual ICollection<Course> Courses { get; set; }
}

We'd also need to modify Course.cs:

public class Course  
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }

    [Required]
    public string Name { get; set; }

    public virtual ICollection<Student> Students { get; set; }

    public int TeacherID { get; set; }

    public int BuildingID { get; set; }

    [ForeignKey("TeacherID")]
    public virtual Teacher Teacher { get; set; }

    [ForeignKey("BuildingID")]
    public virtual Building Building { get; set; }
}

Finally, we'd need to make changes to CourseCatalogContext:

public class CourseCatalogContext : DbContext  
{
    public virtual DbSet<Student> Students { get; set; }
    public virtual DbSet<StudentAddress> StudentAddresses { get; set; }
    public virtual DbSet<Course> Courses { get; set; }
    public virtual DbSet<Teacher> Teachers { get; set; }
    public virtual DbSet<Building> Buildings { get; set; }
}

Once all of these changes are complete, we can create a new Migration for this change:

PM> add-migration Buildings  

Once the migration is created, we can update the database:

PM> update-database  

Now we have created the Buildings table.

Rolling Back a Migration

Let's pretend that the Building improvements didn't take, or weren't going to be needed after all, and we needed to roll them back. We can do so by using update-database and specifying which migration we want to roll back to using the TargetMigration flag:

update-database -TargetMigration InitialSetup  

This command runs the Down() method we talked about earlier to create the SQL script that will remove the database objects that were part of this migration. In our case, it would remove the Building table and the relationship to Course.

Whew! That was a lot to go through. But with Code-First Migrations enabled and ready, you can manage your database schema without ever needing to write a single line of SQL. Pretty neat, huh?

Don't forget to check out the sample project on GitHub, and happy coding!

Building a Code-First Model Using FluentAPI in Entity Framework

Now that we've created a Code-First model using Attributes, let's walk through the other manner by which we can create a model: FluentAPI.

The Model

Once again, we will be using the same CourseCatalog model:

To start off, let's create the classes necessary to represent these entities:

public class Student  
{
    public int ID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime DateOfBirth { get; set; }
    public virtual StudentAddress Address { get; set; }
    public virtual ICollection<Course> Courses { get; set; }
}

public class StudentAddress  
{
    public int StudentID { get; set; }
    public string StreetAddress { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string PostalCode { get; set; }
    public virtual Student Student { get; set; }
}

public class Teacher  
{
    public int ID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public virtual ICollection<Course> Courses { get; set; }
}

public class Course  
{
    public int ID { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Student> Students { get; set; }
    public int TeacherID { get; set; }
    public virtual Teacher Teacher { get; set; }
}

We can also go ahead and create a basic DbContext class:

public class CourseCatalogContext : DbContext  
{
    public virtual DbSet<Student> Students { get; set; }
    public virtual DbSet<StudentAddress> StudentAddresses { get; set; }
    public virtual DbSet<Course> Courses { get; set; }
    public virtual DbSet<Teacher> Teachers { get; set; }
}

Once we've got all of this in place, we can start binding the model to the database using FluentAPI.

Creating the Model using FluentAPI

FluentAPI uses a method-based syntax rather than the attribute-based syntax we saw in the previous post. This gives us a bit more control over the properties of the database that will get created, but can be a bit more difficult to read.

To get started, let's override the OnModelCreating method in CourseCatalogContext:

protected override void OnModelCreating(DbModelBuilder modelBuilder)  
{
    //FluentAPI mapping goes here
}

The DbModelBuilder class is what we can use to build our model rather than using attributes.

For each entity in this model, we need to define a few things:

  • On each entity, we need to define a key column.
  • For each property in an entity, we need to define the attributes of the corresponding column (string length, nullable, etc).
  • For each relationship, we need to define the multiplicity on each side (e.g. one-to-one, one-to-many, many-to-many, etc.)

Let's start with the Student entity. We have a key column on the ID property, which we can define with a call like this:

modelBuilder.Entity<Student>()  
            .HasKey(x => x.ID);

Notice the syntax here. Each call to an instance of DbModelBuilder needs to define the entity we are targeting, and then apply the change we need.

Remember that in addition to having the ID property be the key, we also need to specify that it is an IDENTITY column, which we can do by making an additional call and specifying the property ID:

modelBuilder.Entity<Student>()  
            .Property(x => x.ID)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

Next, we need to tackle the other properties of Student: FirstName and LastName. Both of these properties will need a maximum length of 100 characters and cannot be blank. Since we are defining characteristics of these properties, we can chain these methods like so:

modelBuilder.Entity<Student>()  
            .Property(x => x.FirstName)
            .IsRequired()
            .HasMaxLength(100);

modelBuilder.Entity<Student>()  
            .Property(x => x.LastName)
            .IsRequired()
            .HasMaxLength(100);

FluentAPI's chaining makes creating these characteristics much easier.

Using the methods we've already seen, we can set up the keys and property characteristics for the other three entities:

modelBuilder.Entity<StudentAddress>()  
            .HasKey(x => x.StudentID);

modelBuilder.Entity<StudentAddress>()  
            .Property(x => x.StreetAddress)
            .IsRequired();

modelBuilder.Entity<StudentAddress>()  
            .Property(x => x.City)
            .IsRequired();

modelBuilder.Entity<StudentAddress>()  
            .Property(x => x.State)
            .IsRequired();

modelBuilder.Entity<StudentAddress>()  
            .Property(x => x.PostalCode)
            .IsRequired();

modelBuilder.Entity<Teacher>()  
            .HasKey(x => x.ID);

modelBuilder.Entity<Teacher>()  
            .Property(x => x.ID)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

modelBuilder.Entity<Teacher>()  
            .Property(x => x.FirstName)
            .IsRequired()
            .HasMaxLength(100);

modelBuilder.Entity<Teacher>()  
            .Property(x => x.LastName)
            .IsRequired()
            .HasMaxLength(100);

modelBuilder.Entity<Course>()  
            .HasKey(x => x.ID);

modelBuilder.Entity<Course>()  
            .Property(x => x.ID)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

modelBuilder.Entity<Course>()  
            .Property(x => x.Name)
            .IsRequired();

Setting Up Relationships

The last part of the model definition is to set up the relationships between the entities. In our model, we have the following relationships:

  • Student 1-to-0..1 StudentAddress
  • Teacher 1-to-n Course
  • Student n-to-m Course

Let's start with Student and Student Address.

FluentAPI's naming conventions help us out when defining these relationships. Once we've targeted the entity we want to create a relationship for, we can call methods beginning with "Has" to define the relationship on that side. In the case of Student-StudentAddress, we'd use the method HasOptional, since a Student doesn't have to have a related StudentAddress.

modelBuilder.Entity<Student>()  
            .HasOptional(x => x.Address)

However, as with all relationships, there are two sides to the story and we still need to define the other side. In order to do this, we use methods starting with "With"; in our case, we want WithRequired because a StudentAddress must have a related Student. The final code that sets up the Student-to-StudentAddress relationship is:

modelBuilder.Entity<Student>()  
            .HasOptional(x => x.Address)
            .WithRequired(x => x.Student);

Now we can make the relationship between Teacher and Course. One teacher can have many courses, and any course must have a related Teacher. Therefore, we need the aptly-named methods HasMany and WithRequired:

modelBuilder.Entity<Teacher>()  
            .HasMany(x => x.Courses)
            .WithRequired(x => x.Teacher)

There's still a piece missing though; you may remember from the class definitions above that Course has a property TeacherID, which is a foreign key to the Teacher table. We need to explicitly define that property as a foreign key, and we do this by using the HasForeignKey method like so:

modelBuilder.Entity<Teacher>()  
            .HasMany(x => x.Courses)
            .WithRequired(x => x.Teacher)
            .HasForeignKey(x => x.TeacherID);

Two relationships down, one to go! The last one is a little more difficult, because it's a many-to-many relationship.

Remember that in this kind of relationship in a database, you need a mapping table that related the two tabled together. Entity Framework is smart enough to not count that table as an entity unto itself, rather it just reads it as a relationship between two other entities.

Our relationship between Student and Course can be created using HasMany and WithMany:

modelBuilder.Entity<Course>()  
            .HasMany(x => x.Students)
            .WithMany(x => x.Courses)

However, there's one more step, and that's to create the mapping table. We do this by calling Map(), and then expressing the two columns in the table as well as the name of the table itself:

modelBuilder.Entity<Course>()  
            .HasMany(x => x.Students)
            .WithMany(x => x.Courses)
            .Map(c => c.MapLeftKey("StudentID")
                       .MapRightKey("CourseID")
                       .ToTable("StudentCourses"));

Now we've got all tables, all properties, and all relationships defined! Our final OnModelBuilding method looks like this:

protected override void OnModelCreating(DbModelBuilder modelBuilder)  
{
    modelBuilder.Entity<Student>()
                .Property(x => x.ID)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

    modelBuilder.Entity<Student>()
                .HasKey(x => x.ID);

    modelBuilder.Entity<Student>()
                .Property(x => x.FirstName)
                .IsRequired()
                .HasMaxLength(100);

    modelBuilder.Entity<Student>()
                .Property(x => x.LastName)
                .IsRequired()
                .HasMaxLength(100);

    modelBuilder.Entity<Student>()
                .HasOptional(x => x.Address)
                .WithRequired(x => x.Student);

    modelBuilder.Entity<StudentAddress>()
                .HasKey(x => x.StudentID);

    modelBuilder.Entity<StudentAddress>()
                .Property(x => x.StreetAddress)
                .IsRequired();

    modelBuilder.Entity<StudentAddress>()
                .Property(x => x.City)
                .IsRequired();

    modelBuilder.Entity<StudentAddress>()
                .Property(x => x.State)
                .IsRequired();

    modelBuilder.Entity<StudentAddress>()
                .Property(x => x.PostalCode)
                .IsRequired();

    modelBuilder.Entity<Teacher>()
                .HasKey(x => x.ID);

    modelBuilder.Entity<Teacher>()
                .Property(x => x.ID)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

    modelBuilder.Entity<Teacher>()
                .Property(x => x.FirstName)
                .IsRequired()
                .HasMaxLength(100);

    modelBuilder.Entity<Teacher>()
                .Property(x => x.LastName)
                .IsRequired()
                .HasMaxLength(100);

    modelBuilder.Entity<Course>()
                .HasKey(x => x.ID);

    modelBuilder.Entity<Course>()
                .Property(x => x.ID)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

    modelBuilder.Entity<Course>()
                .Property(x => x.Name)
                .IsRequired();

    modelBuilder.Entity<Teacher>()
                .HasMany(x => x.Courses)
                .WithRequired(x => x.Teacher)
                .HasForeignKey(x => x.TeacherID);

    modelBuilder.Entity<Course>()
                .HasMany(x => x.Students)
                .WithMany(x => x.Courses)
                .Map(c => c.MapLeftKey("StudentID")
                            .MapRightKey("CourseID")
                            .ToTable("StudentCourses"));


    base.OnModelCreating(modelBuilder);
}

Lots of people will split these calls out into separate mapping files (this is what Entity Framework Power Tools does) as that is more maintainable for larger projects.

Now we're done! We've got our model and database setup. Don't forget to check out the sample project on GitHub, and feel free to let me know if this tutorial helped you in the comments!

There's still one more post in this series, and that's going to show how we can use Code-First Migrations to manage changes to our database schema, all without writing any SQL! Stay tuned for the last installment of Entity Framework for Beginners!

Happy Coding!

Building a Code-First Model Using Attributes in Entity Framework

In previous posts we have created a database-first model, created a model-first model using an EDMX, and created a code-first model from an existing database. In this post, we will create a code-first model from scratch using DataAnnotations; in a later post we will create the same model using FluentAPI, and in another post we will demonstrate how to manage changes to your database schema using Code-First Migrations.

For now, let's build a code first model using attributes!

Creating the Model

We want to create the same model we used in the Model-First demo, which looked like this:

Student

Let's begin by creating the Student class. The framework of the class looks like this:

public partial class Student  
{
    public int ID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime DateOfBirth { get; set; }
}

However, we will need to add some data annotations in order to tell Entity Framework about the properties ID, FirstName, LastName, and DateOfBirth.

Let's start with ID. We want that to be the key column for the generated table, and we want it to be a database-generated IDENTITY. We can specify both of those things using attributes:

public partial class Student  
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }

    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime DateOfBirth { get; set; }
}

Let's also say that FirstName and LastName cannot be null and must be less than 100 characters. We can also do that using attributes:

public partial class Student  
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }

    [Required]
    [StringLength(100)]
    public string FirstName { get; set; }

    [Required]
    [StringLength(100)]
    public string LastName { get; set; }

    public DateTime DateOfBirth { get; set; }
}

StudentAddress

Now we have our Student class defined, let's define the class for StudentAddress:

public class StudentAddress  
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }

    [Required]
    public string StreetAddress { get; set; }

    [Required]
    public string City { get; set; }

    [Required]
    public string State { get; set; }

    [Required]
    public string PostalCode { get; set; }

    public int StudentID { get; set; }

    [ForeignKey("StudentID")]
    public virtual Student Student { get; set; }
}

Check out the ForeignKeyAttribute, which is used to specify a foreign key property in the current class (StudentAddress) that references the targeted class (Student).

We also need to add the following property to Student:

public virtual StudentAddress Address { get; set; }  

Note that the two relationship properties are marked virtual. This is so Entity Framework can implement Lazy Loading on this relationship; without the virtual keyword, EF would always load the related entities.

Teacher

Now we can define the Teacher class:

public class Teacher  
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }

    [Required]
    [StringLength(100)]
    public string FirstName { get; set; }

    [Required]
    [StringLength(100)]
    public string LastName { get; set; }
}

Course

Finally, we can define the Course class:

public class Course  
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }

    [Required]
    public string Name { get; set; }

    public virtual ICollection<Student> Students { get; set; }

    public int TeacherID { get; set; }

    [ForeignKey("TeacherID")]
    public virtual Teacher Teacher { get; set; }
}

Remember, though, that in order for the relationship to be completed on each side (Teacher 1-to-n Courses, and Courses m-to-n Students), we need to modify the Teacher and Student classes:

In the Teacher class, we add a collection of Courses they will be teaching:

public virtual ICollection<Course> Courses { get; set; }  

In the Student class, we add a collection of courses they will be attending:

public virtual ICollection<Course> Courses { get; set; }  

The Context

With all of our entity classes in place, it is now time to build the context. We'll call it CourseCatalogContext, and it will need to inherit from DbContext:

public class CourseCatalogContext : DbContext  
{
}

Now, all we need to do is add the DbSets for the entities:

public class CourseCatalogContext : DbContext  
{
    public virtual DbSet<Student> Students { get; set; }
    public virtual DbSet<StudentAddress> StudentAddresses { get; set; }
    public virtual DbSet<Course> Courses { get; set; }
    public virtual DbSet<Teacher> Teachers { get; set; }
}

Ta-da! We've got our context created and ready to use. Since we're using attributes, that's all we need to do in order to create our context; if we needed to implement this in an empty database, we could do using Code-First Migrations, which we will demo in a later post. For now, just sit back and enjoy the fact that you created your first Code-First model!

Don't forget to check out the sample project on GitHub, and happy coding!

Creating a Code-First Model from a Database in Entity Framework

In previous posts we've explored creating a database-first model and creating a model-first model. Both of those designs used the same thing: an EDMX file. In order to use that file, we had to use a designer. But what if we don't want to bother with a designer? Maybe we'd rather just work with code.

Well, good, because that's what we're going to do in this post. We're going to return to the Northwind database and use it to create a code-first schema.

Code-First is the design philosophy in EF that allows developers to work with simple C#/VB classes rather than an EDMX file. For those of us who think in classes rather than designers, this comes in handy.

As a reminder, here's what the full Northwind database schema looked like:

Also, you will need Entity Framework version 6.1 at a minimum to create a Code-First model from an existing database.

Enough talk. Show me the code!

First, right click on the folder where you want your model to go, select Add -> New Item, then select ADO.NET Entity Data Model.

Click Add, and select "Code First from Database", then click Next.

On this next screen, pick your connection information and name the connection string, then click Next.

On this last screen, you can now pick which entities you want to include in your model:

IMPORTANT: Note that Code-First does not currently support adding Stored Procedures as entities. You will instead need to call the sproc manually, as shown here

To build your model, click Finish. The model generated should look something like this:

Notice that there's no EDMX file, only CS files. This is, of course, the definition of Code-First: you only work with code files.

Let's take a loot at a few of these files. First, Customer.cs:

public partial class Customer  
{
    public Customer()
    {
        Orders = new HashSet<Order>();
        CustomerDemographics = new HashSet<CustomerDemographic>();
    }

    [StringLength(5)]
    public string CustomerID { get; set; }

    [Required]
    [StringLength(40)]
    public string CompanyName { get; set; }

    .........

    [StringLength(24)]
    public string Phone { get; set; }

    [StringLength(24)]
    public string Fax { get; set; }

    public virtual ICollection<Order> Orders { get; set; }

    public virtual ICollection<CustomerDemographic> CustomerDemographics { get; set; }
}

A few things to notice about this class:

  • The Customer class has two collection properties: Orders and CustomerDemographics. These are the related entities for this class (Navigation Properties).
  • Notice all the StringLength attributes? They're being used to correspond with the varchar(X) values in the database.
  • Also, see that Required attribute? That means that CompanyName cannot be null.

You'll notice by looking at the source on GitHub that there is a class for each entity, as well as an additional class called Northwind.cs. That class is the context, which is the class that encapsulates all of the other classes (just like the EDMX did). Let's take a look at it.

public partial class Northwind : DbContext  
{
    public Northwind()
        : base("name=NorthwindCodeFirst")
    {
    }

    public virtual DbSet<Category> Categories { get; set; }
    public virtual DbSet<CustomerDemographic> CustomerDemographics { get; set; }
    public virtual DbSet<Customer> Customers { get; set; }
    ...
    public virtual DbSet<Supplier> Suppliers { get; set; }
    public virtual DbSet<Territory> Territories { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        ...
    }
}

First, look at the constructor. It is passing the name of the connection string into a base constructor for the DbContext class.

Next, we see the class DbSet. DbSet represents the collection of all entities that can be queried from the database.

The most interesting part of all this is that these classes that EF generated for us are the same classes generated by the EDMX's code generation tools. The only thing that Code-First is really doing differently is allowing us direct access to these generated files, removing the designer.

Now you've got your model created! There's still one more part to this series, and that's creating a code-first model from scratch. We'll do that in the next installment of Entity Framework for Beginners.

Happy Coding!