In this article, I will walk through an example in an attempt to show Entity Framework Code First. In this Code First exercise, I will install SQL Server LocalDB, create a Visual Studio project, install Entity Framework and then create the database objects (including some test data) through the Visual Studio project using Entity Framework Code First.
To start off it is probably worth mentioning what the Entity Framework is and why it is important in software development and also what Code First means.
In the past, developers coded either directly from their application to a database. The application would either populate tables or call database server functions or procedures directly. If a database object changed or you needed to switch to another database then coding changes were required quite often ending up in a rebuild. Some people built separate business logic code or added other tiers to their applications so that not everything needed to be rebuilt when you had to make a structural change to say the database or switch servers. If you were to switch from say Oracle to SQL Server then this data tier or even the whole product would need to have a major overhaul. Quite a lot of developers, myself included, started to build a data abstraction layer so that switching servers or making changes did not affect the code (at least existing code would still run).
From memory, I think Microsoft created Data Objects around 2005, which was a first attempt to standardise their own database abstraction layer. A few years later and Entity Framework (EF) was born to replace Data Objects and included lots of powerful features.
The theory is that if I want to switch from one relational database provider then I do not have to rebuild my code, just a few configuration changes and I’m done. I’ve never tried to switch from say Oracle to SQL Server using EF myself.
What really got me excited was a few years back when Microsoft added the Code First approach to the Entity Framework. Code First means the developer writes the classes to handle all of the data interactions and storage that is required and Code First builds those structures for you in the database. Also, Code First allows reverse engineering of an existing database into appropriate Entity Framework classes although I don’t cover that here. You can add test data to your database within the deployment giving you the same data each time you run or install it.
LocalDB
In the examples, I’ll use SQL Server Express LocalDB as that was included in my Visual Studio 2017 installation but any SQL Server version will do the same thing.
If you are using SQL Server (2008 onwards) or SQL Server LocalDB then you do not need to download any drivers for Entity Framework.
SQL Server Management Studio will also be useful and I will use it in this article to verify the database objects have been created but it’s not essential to have (as of time of writing version 17.2 is 783 MB). If you need to download, then here are some links.
- Microsoft SQL Server Downloads (Express, Developer or Trial versions).
- SQL Server Management Studio (SSMS).
In Visual Studio I can create a SQL Server Express LocalDB database by right-clicking on Data Connections in Server Explorer and selecting Create New Database. Then entering “(localdb)\MSSQLLocalDB” as the server name and “PatTest” as my new database name when prompted (keep Windows Authentication too). I will not be using this database but this is just to explain how you can create a database from Visual Studio.
You can also see the new database by connecting to the “(localdb)\MSSQLLocalDB” server using SQL Server Management Studio.
Somethings to note about LocalDB:
- You can only have one connection at a time.
- Unlike other editions of SQL Server, LocalDB is not always running in the background. LocalDB runs when it is called.
- Over the years the server name for LocalDB has changed. If you are using earlier versions of LocalDB the server name variations are shown below.
- Visual Studio 2015/2017 uses server name “(localdb)\mssqllocaldb”
- Visual Studio 2012/2013 uses server name “(localdb)\v11.0”
Note that you do not need to use a particular Visual Studio project type or even the LocalDB. Any .NET project will do and any SQL Server version.
Next up, I will include the connection to the database in the Visual Studio project but before I do that I’m going to change the name of the data connection to “PatTest.dbo”.
As a side note, you can see the database objects within a Visual Studio project by using the “Browse In SQL Server Object Explorer” command.
I will edit the connection string in the Web.Debug.config file in this example. I’ll use that so I can use LocalDB for debugging and when I’m ready for release, I can use the connection string in the Web.Release.config file to specify the real production database.
In Web.Debug.config I’ve uncommented the connectionStrings block and changed the name attribute from “MyDB” to “PatTest” and switched the Data Source of the connectionString attribute to “PatTest.dbo” and the Initial Catalog to “PatTest”. Then Save.
However, I will not be using the connectionStrings or the PatTest database created above so the connectionStrings section can be commented out now. I will be using Entity Framework Code First to do it all.
Entity Framework
Entity Framework is not already included in Visual Studio so to start with, I’ll create a project and then I’ll open the Package Manager and install it. There are other ways to install it not covered here.
The Package Manager is hidden away in Visual Studio under the View menu by selecting Other Windows and Package Manager Console. Then you need to type the following and enter.
Install-Package EntityFramework
Code First Example
The database created will be used to host a simple relational database but with Code First, I am going to write the code to generate the tables in Visual Studio classes.
In this example I will create a simple work list. There will be User and Project objects. A Project is made up of Step objects and each Step is assigned to a User. I’ll add some classes to represent those objects.
Adding User.cs, Project.cs and Step.cs to represent the objects.
We’ll add DataAnnotations and some code to the User and Project classes. Near the top of each add:
using System.ComponentModel.DataAnnotations
The code that fits inside the curly brackets of each public class needs to be modified as follows:
public class User
{
[Key]
public Guid Id { get; set; }
public string Name { get; set; }
}
public class Project
{
[Key]
public Guid Id { get; set; }
public string Name { get; set; }
}
The Step class will have foreign key relationships so it requires DataAnnotation.Schema to be added near the top of the class code.
using System.ComponentModel.DataAnnotations
using System.ComponentModel.DataAnnotations.Schema
The Step class code will include an order that the steps should be completed in as well as the detail involved in the step. The code, including foreign keys is as follows:
public class Step
{
[Key]
public Guid Id { get; set; }
public string Name { get; set; }
public string Detail { get; set; }
public Int16 Order { get; set; }
[ForeignKey(“Project”)]
public Guid ProjectId { get; set; }
[ForeignKey(“Project”)]
public virtual Project Project { get; set; }
[ForeignKey(“User”)]
public Guid UserId { get; set; }
[ForeignKey(“User”)]
public virtual User User { get; set; }
}
Save the classes. Create a Models folder in eth project and drag them into it. Then create a Services folder and add another class to that called PatTestContext.cs.
The PatTestContext class inherits from DbContext so it requires Data.Entity to be added near the top of the class code.
using System.Data.Entity
The PatTestContext class code will have “: DbContext” added to the public class line to indicate its inheritance. Then an empty constructor and a “DbSet” for each object. The full class code is as follows:
public class PatTestContext : DbContext
{
public PatTestContext()
{
}
public DbSet<User> Users { get; set; }
public DbSet<Project> Projects { get; set; }
public DbSet<Step> Steps { get; set; }
}
Make sure the class is saved and then go back to the Package Manager to enable code first migrations and then add an initial migration.
Enable-Migrations
Add-Migration Initial
After you run the Enable-Migrations command you will have a new class created under a Migrations folder in your project called Configurations. The Add-Migration Initial command also created a class under this section suffixed “_Initial”.
This next step is not actually required but here I will add initial data to the objects. I will add one project, two users and a few steps. You do this by adding to the Seed method of the Configuration class. I will hard code the Guid’s for these. Here’s the code to add to the Seed method (overwriting the existing comments).
Guid firstProjectId = Guid.Parse(“1526695E-4B9D-4230-8576-CF4F1ED6D3D8”);
Guid firstUserId = Guid.Parse(“DD056DAD-65C0-4733-B7A8-0E828214D803”);
Guid secondUserId = Guid.Parse(“51BC82F3-851D-4D3B-9DAE-30E6452AC1B9”);
Guid firstStepId = Guid.Parse(“D0339A8F-AEF6-4828-A0F4-65043D3CC91E”);
Guid secondStepId = Guid.Parse(“45FD9A3A-9587-4718-8463-2F3FEFEC7761”);
Guid thirdStepId = Guid.Parse(“64A772AB-DC28-4BE1-AC0A-11280F454029”);
Guid fourthStepId = Guid.Parse(“E9D4CEE0-56DC-4845-91B8-955B8CAA48FE”);
context.Projects.AddOrUpdate(p => p.Id, new Project { Id = firstProjectId, Name = “Make Cake” });
context.Users.AddOrUpdate(u => u.Id, new User { Id = firstUserId, Name = “Mary Maker” },
;new User { Id = secondUserId, Name = “Tommy Tester” });
context.Steps.AddOrUpdate(s => s.Id,
new Step { Id = firstStepId, Name = “Ingredients”, Detail = “Gather all of the ingredients and mix thoroughly in a bowl”,
Order = 1, ProjectId = firstProjectId, UserId = firstUserId },
new Step { Id = secondStepId, Name = “Baking”, Detail = “Put mixture in a cake tin and place in oven at 180 degrees for 25 minutes, then cool for 30 minutes”,
Order = 2, ProjectId = firstProjectId, UserId = firstUserId },
new Step { Id = thirdStepId, Name = “Testing”, Detail = “Cut out and taste a small slice of cake – if it tastes good go to step 4, if bad go to step 1”,
Order = 3, ProjectId = firstProjectId, UserId = secondUserId },
new Step { Id = fourthStepId, Name = “Serving”, Detail = “Slice the cake into equal parts, one slice for each guest and prepare on a suitable plate”,
Order = 4, ProjectId = firstProjectId, UserId = firstUserId });
Save the Configuration class changes.
Now it’s time to update the database from the Package Modeller with the following command.
Update-Database -verbose
To check you can use Visual Studio Server Explorer to connect to the new database.
Or use SQL Server Management Studio to see the changes independently of Visual Studio. A simple query such as “SELECT * FROM Steps” will show the seeded data.
Next Steps
Of course, the next steps would be to create my interface and show the data on a page, provide editing and adding capabilities but this article has become long enough.
You can also reverse engineer an existing database to the Entity Framework Code First model but I will save that for another day.