Entity Framework: Database First Versus Code First

When working on an enterprise-class application, one of the paramount decisions that must be made at the onset is how you will persist your data. An equally important decision for the developer must also be answered: How will they interact with the data?

One of the premier technologies for doing just that, and also one we use here at IT Solutions, is known as Entity Framework (EF). This framework handles basic Create, Update and Delete functions as well as querying your database using LINQ. Using a framework in development yields tremendous productivity gains. Before such frameworks existed, developers had to represent a business need in code and also represent it in a database, essentially writing the same code twice.

EF is a category of framework known as an Object Relational Mapper (ORM). An ORM strives to handle the database representation for you, freeing you to focus on representing your business logic. Over the years, improvements to Entity Framework have made it as conceptually simple as possible to work with your data.

However, some choices have to be made, even within the framework before you begin, including how you want to model your database. When you first add an ADO.NET Entity Data Model to a project you are greeted with the following choices:

Option 1: “EF Designer from database”

This has been the traditional approach to using entity framework. Push button, receive ORM. The advantage being you don’t need to figure out how to represent your database objects in your project. Today this approach is usually paired with a SQL Database Project to maintain the source scripts of the database.

Option 2: “Empty EF Designer model”

Identical to option one, except instead of engineering the models from a database they are designed through the interface. This is somewhat cumbersome as the designer can be difficult to work with and changes to the database itself need to be made outside of the designer.

Option 3: “Empty Code First model”

Code First was introduced in version 4.0 of EF and has rapidly changed between then and now (the current version is 6.1.3. although as of this writing version 7 is nearing completion).

The mantra being write the code that you want to use to interact with your database First. This allows you the flexibility to define how you want to interact with your database. EF generates a supporting database schema for your code.

Option 4: “Code first from database”

This is identical to option three; except you’re using an existing database reverse engineered into classes.

We are going to focus on option one and option three. These approaches are fundamentally different; the other options are extensions of option one and three.

Using a database project leaves entity framework’s EDMX file as a push button solution for interacting with your database in code, however you cannot customize what that looks like. Code First gives you the option to customize the objects you work with in code but sacrifices the ease of a design surface such as SSMS to build the tables.

Let’s compare our options for developing a database with each of these approaches:

Now for some comments…

Database First really shines when you need to interact with a database without modifying it, such as when you do not own the database or are making a bridge between two systems. Stored procedures and views also function well under a Database First approach because they are static. Database First also allows views and tables in the same generated context; therefore, you can query across tables and views in LINQ (note: this can be achieved in Code First too, however some work is needed to get views into the context).

In order to update the Conceptual Entity Model (EDMX file) you have to update the database manually and then sync the EDMX to the database using the option below:

An alternative to this would be to modify the .EDMX and use the “Generate Database from Model” option to create a database creation script, which you can then run after you drop the database.

This is a somewhat cumbersome approach, as there is no real history of the database maintained. This can be mitigated with the use of a database project to maintain the source of the database. Then updating the model from the database is essentially the only thing that needs to be done in the EDMX. This also means the developer must have extensive knowledge of SQL Server.

When you can make changes to your objects in Code First, you create what is known as a migration: a single file representing the difference between your objects before and after your changes. Because these are separate files you can easily look at how the database has evolved and revert to a prior state without having to drop and recreate the database (migrations can also output SQL scripts).

Let’s compare the same thing created in Code First versus Database First:

Code First (written by a developer):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFModels.CodeFirst
{
public class Customer
{
public int Id { get; set; }
public string Name { get; set; }
public IList Products { get; set; }
}
}

Database First (generated from a table in a database):

 //------------------------------------------------------------------------------
//
//     This code was generated from a template.
//
//     Manual changes to this file may cause unexpected behavior in your application.
//     Manual changes to this file will be overwritten if the code is regenerated.
//
//------------------------------------------------------------------------------

namespace EFModels.DatabaseFirst
{
using System;
using System.Collections.Generic;

public partial class Customer
{
[System.Diagnostics.CodeAnalysis.SuppressMessage(“Microsoft.Usage”, “CA2214:DoNotCallOverridableMethodsInConstructors”)]
public Customer()
{
this.Products = new HashSet();
}

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

[System.Diagnostics.CodeAnalysis.SuppressMessage(“Microsoft.Usage”, “CA2227:CollectionPropertiesShouldBeReadOnly”)]
public virtual ICollection Products { get; set; }
}
}

As you can see the Database First model makes some assumptions about the model that you may or may not have wanted. In order to change these, you need to update settings in the EDMX on a per entity basis.

In Code First you can define conventions and attributes which dictate how your database is ultimately built. This gives you consistency for an otherwise hand-written schema. Sometimes this is valuable, and sometimes it really is just developer preference (meaning this specific example and choosing which approach to take as a whole).

Here at IT Solutions we work extensively with all four of these approaches in different contexts. We build new projects with Code First and Database First. We also customize existing applications by bolting on a Database First EDMX or representing it in a Code First model. We choose the right tool based on the needs of a solution.

If you are exploring web development tools and processes for an upcoming project, we’re happy to discuss your options and make recommendations based on your specific needs. If you’d like to learn more about our custom application development services and what it’s like to partner with a team of local developers like ITS, please contact us today.