Traditional ways of software development focus on DB-first implementation methodology. But we are in the realm of latest developments and ORM models has taken us to a level that is was a complex ordeal some years back. Eventhough a thorough domain/ERD based approach provides some advantages, there are far more overall improvements to development and process methodology using a Code first methodology. Lets see how this concept can be applied in a ASP.NET Entity framework model setting and what its benefits are –

Doing Code First first

DB first approach creates the DB first and then asks EF to create the domain classes.

Ie. Domain classes <- EF <- DB

Code first approach goes the other away round, starting with Code.

Ie. Domain classes -> EF -> DB

Why Use Code First  ?

• It will increase productivity due to the fact that its faster to write code than designing DB
• When we manually created DB/tables we need to manually create the script to deploy changes to Database, but with Code first approach this process is completely automated and on-demand!!
• Allows to clearly version DB (w.r.t changes overtime) and migrate to any DB version that we want at any time – does that makes out lives much easier when something goes wrong and we want to revert or to have a ready made script available to deploy any version of DB at a given point in time!!
• It helps us to create integration test db super simple (3 LOC)

–          It could be used effectively not only with new system design but also with existing systems that we think are hard to change. Try new things every day – After all new advancements in information technology are created from vast amount of experience and wisdom.

Steps to perform CF migrations in ASP.NET

• On Nuget Packet manager, execute enable-migrations à this is only for first time use
• Make changes/additions in domain classes as you go through the application development history
• On all changes where you want to create a migration history, execute add-migration <name related to changes> on Nuget package manager after the domain class changes. This will add Migrations folder (if it does not already exist), add the migration classes (<some numbers>.cs) with specific Entity Framework (EF) code to migrate to those changes.
• Execute update-database command  so that EF will look at the latest added migration and updates the DB. It will create the folder App-Data (if not already created) and the mdf file within it which stores the DB – refresh it to see the changes after each migration.

Adding data to tables – Seeding the Database 

When you want to add data to the tables you never directly add data to the tables, instead go through the migration process –

• Create a new migration – add-migration AddMembershipTypeData
• Open the migration file and add the SQL statements –

public override void Up() {

Sql(“INSERT INTO CustomModels(Id, Size, Weight, Avatar) VALUES (1, 0, 0, 0)”);

Sql(“INSERT INTO CustomModels(Id, Size, Weight, Avatar) VALUES (2, 30, 1, 10)”);

Sql(“INSERT INTO CustomModels(Id, Size, Weight, Avatar) VALUES (3, 90, 3, 15)”);

Sql(“INSERT INTO CustomModels(Id, Size, Weight, Avatar) VALUES (4, 300, 12, 25)”);-

}

•   Execute update-database  to update DB 

By Following this CF process with planned creation of migrations will help us to extract the whole SQL for a fresh deployment or extract the SQL file from the last deployment till now.

Overriding Conventions

EF creates the column defs using some standard conventions. For example, it assign nullable for string columns and assign max varchar if we do not specify anything. We can override conventions using Data annotations as below –

[Required]

[StringLength(40)]

public String Avatar{ get; set; }

Above data annotation will ensure that the column Avataris required and has width of 40 when the migration is created and db update is done.

Querying Data

Define the Application context as private property, initialize it in the constructor and call Dispose method.

private ApplicationDbContext _context;

public ShopController()        {            _context = new ApplicationDbContext();        }

protected override void Dispose(bool disposing)        {            _context.Dispose();        }

Use the _context property to access the Model data –

//var shops= _context.Shops.ToList();

// if you want the resultset here,otherwise the query will be executed in view

     var shops = _context.Shops;

Eager Loading

Eager Loading is a way to load all the related entities. We use the Include extension method off of LINQ as below –

var shops = _context.Shops.Include(c => c.AvatarType).ToList();

Here, the related table AvatarType will be loaded with reference to the foreign key avatarTypeId.

LINQ Extension Methods

 _context.Shops.Where(m  =>     m.Id         ==        1)

 _context.Shops.Single(m           =>          m.Id      ==               1);

 _context.Shops.SingleOrDefault(m         =>         m.Id      ==               1);

 _context.Shops.ToList();