Understanding Entity Framework Code First Migrations

Code-First approach allows you to define model classes as per the Domain requirements via POCOs. Hence, you have complete control over the classes being written or Implemented. Code First Migrations allow you to create a new database or to update existing database based on your model classes by using Package Manager Console exist within Visual Studio 2013 or Visual Studio 2012.

Code First Database Initializers

Before understanding code first migrations, let's have a look at Code First Database Initializers provided by Entity Framework. When you follow EF code first approach, then you have three options for initializing database as given below–

1. CreateDatabaseIfNotExists

   This is the default database initializer class used by Code First. This class creates a database only if it doesn't exist. This initializer helps you to avoid any accidental deletion of the database.

2. DropCreateDatabaseWhenModelChanges

   This database initializer class drop the existing database and re-creates it if there is a mismatch between the model classes and table schema. This initializer is useful during starting phase of development and testing when models are changing often and there is no concern about the existing database records.

3. DropCreateDatabaseAlways

   This database initializer class always drop and creates a new database, whether it is already present or not with every run of the application. This initializer is useful during testing when you want to run the application with a fresh set of data.

Why Code First Migrations?

The above three database initializing approach become fail when you add new model classes and you want to update existing database without any deletion or change. To achieve this, you need to use EF code first migrations which allow you to update existing database with new model classes changes and your existing database remains same with your database records.

Note

You can also create custom database initializers by implementing the IDatabaseInitializer interface.

Code First Migrations in Entity Framework step by step

Model Classes

Suppose you have the following two entities within your application.

public class Category
{
 public int CategoryID { get; set; }
 public string Name { get; set; }
}
 
public class Product
{
 public int ProductID { get; set; }
 public string Name { get; set; }
 public decimal Price { get; set; }
 public int CatID { get; set; }
 
 public virtual Category Category { get; set; }
}

Mapping Details

DataContext class and Fluent API mapping details for above two entities are given below –

public class DataContext : DbContext
{
 public DataContext():base("DefaultConnection"){ }
 
 protected override void OnModelCreating(DbModelBuilder modelBuilder)
 {
 modelBuilder.Entity<Category>().HasKey(c => c.CategoryID);
 modelBuilder.Entity<Category>().Property(p => p.Name).HasColumnType("VARCHAR").IsRequired().HasMaxLength(50);
 
 modelBuilder.Entity<Product>().HasKey(p => p.ProductID);
 modelBuilder.Entity<Product>().Property(p => p.Name).HasColumnType("VARCHAR").IsRequired().HasMaxLength(50);
 modelBuilder.Entity<Product>().Property(p => p.Price).HasColumnType("DECIMAL").IsRequired();
 
 modelBuilder.Entity<Product>().HasRequired(m => m.Category).WithMany().HasForeignKey(c => c.CatID);
 
 }
 public DbSet<Product> Product { get; set; }
 public DbSet<Category> Category { get; set; }
}

Connection String

And, your database connection string is given below:

<connectionStrings>
 <add name="DefaultConnection" providerName="System.Data.SqlClient" connectionString="data source=SHAILENDRA\SQLEXPRESS;initial catalog=EFCodeFirst;persist security info=True;user id=sa;password=mypassword; App=EntityFramework" /> </connectionStrings>

Visual Studio Package Manager Console

To create the database for these two entities within your application, go to the Package Manager Console option as shown below:

Creating New Database

Running Commands

Run the following command to configure migrations within your project and for creating new database.

1. Enable migrations

   Enable-Migrations

2. Create migration

   Add-Migration MigrationsName

3. Create upgrade/downgrade script

   Update-Database

When you run above listed command on the Package Manager Console windows one by one then:

1. Firstly a Migrations folder will be added into your applications having Configuration.cs file for Migrations configuration setting.
   
   
2. Secondly a class file will be created having name suffix as MigrationsName followed by underscore and a unique generated number. This file will have all the entities to be created in the database.
3. Thirdly, a new database will be created having initial catalog name (initial catalog = YourDBName) as given in your connections string.
   
   

Updating Existing Database

Suppose you have added one more class named as Customer into your data model classes as given below:

Newly added Model Class

public class Customer
{
 public int CustomerID { get; set; }
 public string Name { get; set; }
 public string Address { get; set; }
}

Running Commands

Now for updating the database with new changes run the following commands as given below:

1. Create migration

   Add-Migration MigrationsName

2. Create upgrade/downgrade script

   Update-Database

When you run above listed command on the Package Manager Console windows one by one then

1. Firstly a class file will be created having name suffix as MigrationsName followed by underscore and a unique generated number. This file will have all the entities to be created or updated in the database.
   
2. Secondly, the existing database will be updated with new changes.
   

Undo/Rollback a Migrations

You can also Undo/Rollback a specific migrations by using following commands:

1. Rollback to a specific migrations

   Update-Database -TargetMigration:MigrationsName

2. Rollback all migrations

   Update-Database -TargetMigration:0

What do you think?

I hope you will enjoy the tips while programming with Entity Framework. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

Tips to improve Entity Framework Performance

LINQ to Entity is a great ORM for querying and managing database. It offers a lot of things, so it is mandatory to know about performance of it. These are right up to a certain point as LINQ comes with its own penalties. There are some tips and tricks that we should keep in mind while desiging and query database using entity framework ORM. Here is a list of some tips that I would like to share with you.

1. Avoid to put all the DB Objects into One Single Entity Model

   Entity Model specifies a single unit of work, not all our database. If we have many database objects that are not connected to one another or these(log tables, objects used by batch processes,etc.) are not used at all. Hence these objects are consuming space in the memory and cause performance degrades. So try to make separate entity models of related database objects.

2. Disable change tracking for entity if not needed

   Whenever you retrieve the data only for reading purpose, not for modification then there is no need of object tracking. So disable object tracking by using MergeOption as below:

   
    NorthwindDataContext context = new NorthwindDataContext() context.tblCities.MergeOption = MergeOption.NoTracking; 
   
   

   This option allow us to turn off the object cache and unnecessary identity management of the objects.

3. Use Pre-Generating Views to reduce response time for first request

   When the object of ObjectContext is created first time in the application, the entity framework creates a set of classes that is required to access the database. This set of classes is called view and if your data model is large then creating the view may delay the web application response to the first request for a page. We can reduce this response time by creating view at compile time by using T4 template or EdmGen.exe command-line tool.

4. Avoid fetching all the fields if not required

   Avoid fetching not required fields from the database. Suppose I have table of Customer with 20 fields and I am interested only in three fields - CustomerID, Name, Address then fetch only these three fields instead of fetching all the fields of the Customer table.

   
    //Bad Practice
   var customer =
    (from cust in dataContext.Customers
    select cust).ToList();
    //Good Practice
   var customer =
    (from cust in dataContext.Customers
    select new {
    customer. CustomerID,
    customer.Name,
    customer.Address
    }). ToList (); 
   
   

5. Choose appropriate Collection for data manipulation

   In linq we have Var, IEnumerable, IQueryable, IList type collection for data manipulation. Each collection has its importance and performance impact on the query, so beware of using all these collection for data manipulation. For learning difference among all these collection refer my articles IEnumerable VS IQueryable, IEnumerable VS IList and Var VS IEnumerable.

6. Use Compiled Query wherever needed

   Make a query to compiled query if it is frequently used to fetch records from the database. This query is slow in first time but after that it boost the performance significantly. We use Compile method of CompiledQuery class for making compiled query.
   Suppose you required to retrieve customers details again and again based on city then make this query to compiled query like as

   
    // create the entity object
   NorthwindEntities mobjentity = new NorthwindEntities();
    //Simple Query
   IQueryable lstCus = from customer in mobjentity.tblCustomers
   where customer.City == "Delhi"
   select customer;
    //Compiled Query
   Func> compiledQuery
   = CompiledQuery.Compile>(
   (ctx, city) =>from customer in ctx.Customers
   where customer.City == city
   select customer); 
   
   

   In above query we are passing the string parameter city for filtering the records. For more about anonymous method.

7. Retrieve only required number of records

   When we are binding data to grid or doing paging, retrieve only required no of records to improve performance. This can achieved by using Take,While and Skip methods.

   
    // create the entity object
   NorthwindEntities mobjentity = new NorthwindEntities();
   int pageSize=10,startingPageIndex=2;
   List lstCus = mobjentity.tblCustomers.Take(pageSize)
    .Skip(startingPageIndex * pageSize)
    .ToList(); 
   
   

8. Avoid using Contains

   In LINQ, we use contains method for checking existence. It is converted to "WHERE IN" in SQL which cause performance degrades.

9. Avoid using Views

   Views degrade the LINQ query performance costly. These are slow in performance and impact the performance greatly. So avoid using views in LINQ to Entities.

10. Debug and Optimize LINQ Query

    If you want to debug and optimize your query then LINQ Pad is a great tool for this purpose. I am a big fan of LINQ Pad. It is very useful for query construction, debugging and optimization.

    
     IQueryable lstCus = from customer in mobjentity.tblCustomers
    where customer.City == "Delhi"
    select customer;
    lstCus.Dump(); 
    
    

    Dump method of LINQ Pad give the result of above query in the result window.

Summary

I hope you will enjoy these tips and tricks while programming with LINQ to Entity. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.