Entityframework 4.3 code first & Migration

I wasn’t a fan of code first when it was released with EF 4. It is lack of data schema migration support. Every time you have schema updates, the database needs to be recreated using the Initializer. With EF Migration shipped out with EF 4.3, It resolves my issue.

Here I created a sample to demonstrate the migration process of EF 4.3, to prove it works with all scenarios. I create the POCOs with three different kinds of relationships and variable types of properties.

image

The database diagrams show the facts as below,

User to Addresses => one to many relation
User to Profiles => one to one relation
User to Lessons => many to many relation

User POCO

public class User
{
public int Id { get; set; }
public string Name { get; set; }
public string Email { get; set; }

public byte Gender { get; set; }
public bool? IsMarried { get; set; }

//one to many relationship
public virtual ICollection<Address> Addresses { get; set; }

//one to one relationship
public virtual Profile Profile { get; set; }

//many to many relationship
public virtual ICollection<Lesson> Lessons { get; set; }
}

Profile POCO

    public class Profile
    {
        [Key]
        [ForeignKey("User")]
        public int UserId { get; set; }

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

        public virtual User User { get; set; }
    }

Address POCO

    public class Address
    {
        public int Id { get; set; }
        public string Street { get; set; }
        public string State { get; set; }

        public virtual User User { get; set; }
    }

Lesson POCO

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

        public virtual ICollection Users { get; set; }
    }

And create the Context class to inherit from DbContext, and add DbSets to it.

public class CodeFirstExampleContext :DbContext
{
public DbSet<User> Users { get; set; }
public DbSet<Address> Addresses { get; set; }
public DbSet<Profile> Profiles { get; set; }
public DbSet<Lesson> Lessons { get; set; }
}

Also make sure you have your connection string inside the web.config/ app.config file.

<connectionStrings>
<add name="CodeFirstExampleContext" connectionString="Data Source=(local)\SQLEXPRESS;Initial Catalog=CodeFirstExample;integrated security=True" providerName="System.Data.SqlClient" />
</connectionStrings>

Now, I can write some code to add some records using the model that I created. And when the execution tries to talk to the database, it will create the database for you.

I put some test code inside the HomeController, Index action method.

        public ActionResult Index()
        {
            Data.User user = new Data.User();
            user.Name = "superwalnut";
            user.Email = "superwalnut@test.com";
            user.Profile = new Profile()
            {
                FirstName = "kev",
                LastName = "wam"
            };
            user.Addresses = new List</pre>
<address>(){ new Address{ Street = "test st", State = "test state" } }; CodeFirstExampleContext context = new CodeFirstExampleContext(); context.Users.Add(user); context.SaveChanges(); return View(); } 

Run the application, your database should be automatically created based on your models.

Now let’s change the User model, add two more columns in.

        public byte Gender { get; set; }
        public bool? IsMarried { get; set; }

How do we update the schema???

No worries. (I am testing using EF4.3.1)

Open Package Manager Console, and type

PM> Enable-Migrations

After you execute this command, it will create a folder inside your project. “Migrations”

image

Open the Configuration.cs file, you need to manually update the context type

image

Now you are all set to update schemas, after you have updated the User POCO, type the following command in Package Manager Console.

PM> Add-Migration

It will compare the database schema and your current models and generates a class that contains update methods. You can give a proper name for the generated class. Such as “Added User Gender IsMarried”.

The file will look like “201203270149267_Added User Gender IsMarried.cs”

Now type command in Package Manager Console to update database.

PM> Update-Database

Your database is updated after execution.

With column default values

If you already have records in the database for table User, for the newly added columns Gender (byte) and IsMarried (bool?), they will be given a default value 0. If it is nullable, null is given.

If you want to set existing records with special default values, you need to write Sql scripts in the Migration class Up method. Like below,

Sql("Update User Set Gender = 1");

To checkout the sample project code, go to codeplex

http://codefirstmigration.codeplex.com/

You will need to “Enable Nuget Package Restore” by right clicking your solution file after you download the source from codeplex TFS.

image

Advertisements

EntityFramework 4.1 Extensions for batch Delete and Update

A great library that extends the functionality of EF. It provides the features to do batch updates, delete and future queries and audit log.

PM> Install-Package EntityFramework.Extended

Use the Package Manager Console in VS2010 to install it. And you can find the source in Github.

http://nuget.org/packages/EntityFramework.Extended

https://github.com/loresoft/EntityFramework.Extended

Make sure you import the Extensions namespace. Then you will see the extension methods of batch delete/update. Here are a few lines of code to demonstrate how to delete, update.

using EntityFramework.Extensions;

context.Users.Delete(u => u.FirstName == "firstname");

context.Tasks.Update(
    t => t.StatusId == 1, 
    t => new Task {StatusId = 2});
 

For future query usage, you can use an IQueryable as the filter for the update


var users = context.Users
   .Where(u => u.FirstName == "firstname");

context.Users.Update(
   users, 
   u => new User {FirstName = "newfirstname"})

Pretty cool is it! To read more documentations go to the github site. https://github.com/loresoft/EntityFramework.Extended