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.
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
After you execute this command, it will create a folder inside your project. “Migrations”
Open the Configuration.cs file, you need to manually update the context type
Now you are all set to update schemas, after you have updated the User POCO, type the following command in Package Manager Console.
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.
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.