Get Your Hands On Entity Framework Code First Workflow With New Database And Existing Database

Entity framework is an open source O/RM(Object-Relational Mapper) tool to access the database in object-oriented approach in .NET.

O/RM

It’s a persistence framework which is used to map the database table to the classes and objects. It generally queries database in object-oriented approach and provides the type safety during compiling.

Architecture of Entity Framework

Workflow in Entity Framework is of 3 types

1. Database first
   In database first, we design our tables using a visual designer or using SQL query. Then, the Entity Framework generates a domain class.This is the traditional approach many developers are following since the initial release of EF 1 and Visual Studio 2008.
   
   
2. Model first
   In model first, we use the visual designer in Visual Studio to model our class and their associates. It is just like UML diagram and based on this, EF generates a domain class and database. It was released with EF 4.0 and Visual Studio 2010.
   
   
3. Code first
   In code first, we create our domain class then the EF generates a database for us. The Code First was released with EF 4.1.1 and Visual Studio 2010.

How to choose your development workflow

Drawback of database first and model first workflow

In case of insert, update, and delete in database and model first, it requires XML parsing and de-parsing for validating the data and in code first, everything is in precompile format.

CODE FIRST WORKFLOW

In code first workflow, we generally describe a model using C# class then create a database from these classes.The code first approach is more flexible workflow than database workflow. In the latest Entity framework, we have only the code first workflow.

Advantage of code first

1. It works entirely in object-oriented approach and we do not have to worry about the structure of the database.
2. It provides full control over the code but in case of the database first, the entire code is autogenerated based on your database changes.
3. We can use it with an existing database also.
4. Full versioning of database.( i.e using rollback migration by targeting a particular migration in the past)

NOTE

Some developers have a misconception that code first can be used for green field project and not for existing databases and the database first approach gives you more control over the database.

Code first model overriding are of 2 types

• Data Annotation
  It is denoted by attribute in model class and it is generally used in simple project in which we make a property required,change table name using table attribute, adding primary key using key attribute, adding index using index attribute and setting length in a string using string length etc.
  
  
• Fluent API
  It is an overriding convention in databse context class and it is generally used in enterprise application to configure relationship between classes and we can do every thing what data annotation does by fluent api .

Code first with new database

Create a new project in visual studio then select visual studio solutions >>blank solution>>add a name as CodeFirstWithNewDB then click ok.



Add a class library to the solution and name it as DAL(Data Access Layer)



Add entity framework to DAL by right click DAL >>manage nuget package >>select browse tab and search entityframework and install it.



Add domain class Product.cs and Category.cs to DAL which help to generate database and add properties to the class which will map to the database column.In real time project we might have lots of class but only those class which are declared in DbContext class will generate the database.



Add these properties to Product.cs



Add these properties to Category.cs



These are some default rule followed by entity framework, 

1. Whatever the class name we declare,with the same name it will create the table name and when it will create the database table,entity framework follow the plural rule so if the class name is product then the table name will become products(i.e it will add ‘s’ or ‘es’ to the class name).
2. If the class property name contain Id or class name suffix Id,then that property by default behave like primary key here productId is a primary key in product class.
3. If primary key data type is integer then it will automatically behave as identity column.
4. All the string datatype in the property will map to the nvarchar(max) datatype in the database column and these are by default nullable type because string are by default nullable type in C#.
5. In case of navigation property,here we added a navigation property as category which is a parent table class of category class.So,whatever the primary key in the category class that will automatically map to categoryId property of the product class as a foreign key because the name of the navigation property match to the categoryid property and both property datatype also mapping(i.e as we know in primary key and foreign key relation the datatype should be same).
6. If we will not define any property with name categoryid in product class then it automatically create a column with the name product_categoryid in the database and that will map as foreign key of product table.

Note

We can override this default rules by data annotation and fluent api.

Now create a dbcontext class as DatabaseContext.cs which inherit from entity framework built in class DbContext and then create a constructor which inherite from base class constructor and to this base class constructor we pass the connectionstring name as NewDBConnection.Then add the class in dbset (dbset is a collection object that represent table in a database).

Now add a connection string in App.config file inside configuration tag



Now goto tools in visual studio then select nuget package manager and open pakage manager console.For migration we have to enable migrations for only one time in a project.



Then it will create a migrations folder with a configuration.cs inside it.



Then in package manger console enter add-migration command and give a name to the migration as initial and enter.



Then entityframework will create a migration with name as initial under migration folder in which two override method up and down are created.

Up method means upload the model change to the database and down method means to down grade the model change from the database.In case we made any mistake in database in that case we can rollback to the previous stage by using migration rollback which can be archieved by down method.

public partial class initial: DbMigration {
    public override void Up() {
        CreateTable("dbo.Categories", c => new {
            CategoryId = c.Int(nullable: false, identity: true),
                Name = c.String(),
        }).PrimaryKey(t => t.CategoryId);
        CreateTable("dbo.Products", c => new {
            ProductId = c.Int(nullable: false, identity: true),
                Name = c.String(),
                Description = c.String(),
                UnitPrice = c.Decimal(nullable: false, precision: 18, scale: 2),
                CategoryId = c.Int(nullable: false),
        }).PrimaryKey(t => t.ProductId).ForeignKey("dbo.Categories", t => t.CategoryId, cascadeDelete: true).Index(t => t.CategoryId);
    }
    public override void Down() {
        DropForeignKey("dbo.Products", "CategoryId", "dbo.Categories");
        DropIndex("dbo.Products", new [] {
            "CategoryId"
        });
        DropTable("dbo.Products");
        DropTable("dbo.Categories");
    }
}

Now open package manager console and add a command update-database –verbose to update the database and –verbose is used to view the sql statement that applied to database.

Next step is to open sql server management studio and refresh database,then we can see OrderManagment database is created,then expand tables to see the categories,products table and _migrationhistory table which track the the migration history of the database changes.

Now to add another table(i.e admin table) to the existing database, we have to create a class as Admin.cs in DAL

Next  we have to add this class as Dbset in DbContext class

Next goto the package manager console to add migration for admin table.

Note that every time we create a migration it will add to the _MigrationHistory table in the database and using that migration history table we can track the changes.

After that a migration folder will be created in DAL as AddAdminTable migration name and then entity framework automatically add a command in up method to create a admin table .

public partial class AddAdminTable: DbMigration {
    public override void Up() {
        CreateTable("dbo.Admins", c => new {
            AdminId = c.Int(nullable: false, identity: true),
                Name = c.String(),
                Email = c.String(),
        }).PrimaryKey(t => t.AdminId);
    }
    public override void Down() {
        DropTable("dbo.Admins");
    }
}

Finally in package manager console run the update database command.

Now check the sql server management studio for the admin table.

Next step everyone might be thinking how to populate the table in code first.In the code first there is a method called Sql method and with this method we can run any sql query in the database that why code first workflow has the full control over the database.

Now to populate the admin table we have to add a blank migration and name it as PopulateAdminTable in package manager console.

Now goto migration folder in DAL and in the PopulateAdminTable migration class Up method add the sql method to insert data to the table and here we are not entering data for AdminId as it is a identity column.

Goto package manager console and update the database.

Now goto to sql server management studio and refresh the database check the admin table which is now populate with data.

Now how to add a column Brand in the product table?

Goto the product class add a property Brand as follows

public string Brand { get; set; }

Then open the package manager console and add migration command with a name AddBrandColumnToProductTable.

And entity framework automatically generate this migration class with addcolumn query in up method.

public partial class AddBrandColumnToProductTable: DbMigration {
    public override void Up() {
        AddColumn("dbo.Products", "Brand", c => c.String());
    }
    public override void Down() {
        DropColumn("dbo.Products", "Brand");
    }
}

 And finally update the databse.

Then check in products table a brand column is added.

  

Note

1. It is a good practice to add migration with a meaning full name.
2. Add small changes to the database and update the database accordingly because by doing so we can downgrade the changes by targeting the migration name in the _MigrationHistory table. (Just it will act like Git version control in which we commit some changes with a name and if we want some rollback to past changes then we can archieved by revert command).

Depending on workflow if we want to rollback to previous migration then goto to the _migrationHistory table and select and copy the migrationid to which you want to rollback here we want to rollback to _PopulateAdminTable

 for this open package manager console run the following command.

Here it will revert the _AddBrandColumnToProductTable and drop the Brand column in products table.To see the changes just refresh the _MigrationHistory

and now refresh Products table to see the changes.

But in Product class we have to manually delete the Brand property as entity framework will not delete for you.

Code first with an existing database

Here we built a model with existing database once the model is generated then use the code first workflow for the subsequent changes.Instead of changing database first and then updating the model we can change model using code and then use migration to bring the database uptodate.

Advantage of existing database 

1. When we switch to code first workflow we get full versioning of database.
2. Required less time to design the database.

Create a new project in visual studio then select visual studio solutions >>blank solution>>add a name as CodeFirstWithExistDB then ok

Add a class library to the solution by right click solution>> add >>add new project>>select visual c# >> select class library(.net framwork) and name it as DAL(Data Access Layer)

Then right click DAL >>add>> new item>>select data>>select ADO.Net Data Model>>Name as DatabaseContext>>add

A Entity Data Model Wizard will appear >> select code first from database ,click next

Now we want to specify connection string so,click new connection - choose the data source - Microsoft sql server>>continue - enter the connection property like server name,choose the database name from the dropdown(here we are using the same previous created database i.e OrderManagment) the click ok



Now it will add a connection string in the app.config with name DatabaseContext



Click next.Then choose the database object by expanding the tables folder,select all the tables except _MigrationHistory then click finish.



Here we are not include the _MigrationHistory table because it will use by entityframework internally so no need to use it.

Now it will add these class in our project



Now to add a column Password to the admin table,Just create a property Password in admin class

public string Password { get; set; }

Now enable migrations in the DAL



Then add a migration  and name as AddPasswordColumnToAdminTable to create add column command in up method



Then finally run update database in package manager console to add this change in database.



Finally check the admin table in the sql server management studio



And that all for this article..

Next article coming soon.

Summary

In this article we learnt how to create code first workflow with new database and existing database.I have tried my best to make it simple for beginner and experienced professionals to implement in there real time application as its give more flexibility and versioning than traditional database first workflow.

Sharing knowledge is the fuel to your growth engine...