Entity Framework (EF) marked a big step forward for application developers, but early versions were met with complaints that many important features were missing. Many of those big issues were addressed in EF 2. And when Code First came along, pretty much all the big issues were gone.
This is part one in a series on EF Code First basics. I’ll cover working with a single table, one-to-one, one-to-many, and many-to-many relationships. The series came out of my needs to understand how to setup and work with EF. Just a heads-up. There is A LOT to EF that I won’t cover. I recommend Julia Lerman’s great book, “Programming Entity Framework Code First”.
To get started, I created Visual Studio Console project named EFLab. When I experiment with something, I always put Lab in the solution name. Next, I used NuGet to add EF to the project. Because I’m working with VS 2012, EF 5.0 was automatically selected for me. Now it’s time to create the entity. To make things easy for the example, I created a single .cs file called Entities. But, I renamed the class. I intend to put all the code needed to make EF work inside this class.
using System.Data.Entity;
namespace EFLab
{
public class Person
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}
public class EFContext : DbContext
{
public DbSet<Person> People { get; set; }
}
}
There are two classes here. The first, Person, defines the entity and maps to the actual table in the database. Note that at this point, the database does not exist. We’re using Code First, which will create the database for us.
The second class, EFContext, inherits from DbContext that is defined in System.Data.Entity. This class allows us to work with the database and will provide all the CRUD code. Let’s now see how to insert a new row into the table. Here’s the code from Main().
static void Main(string[] args)
{
EFContext context = new EFContext();
Person person = new Person();
person.FirstName = "Bullwinkle";
person.LastName = "Moose";
context.People.Add(person);
context.SaveChanges();
}
First, we get an instance of the context object, then create and populate the Person object and finally add the person to the People collection and save the changes. But, we’re not quite ready to run the code yet. We need to setup the database connection. Here’s the entry from app.config.
<connectionStrings>
<add name="EFContext" providerName="System.Data.SqlClient"
connectionString="Server=(local);Database=EFLab;Integrated Security=True;"/>
</connectionStrings>
Note the name of the connection, EFContext. This is the same name as our context object. If these two match EF will automatically wire everything up for us. Now it’s time to run the code. Remember, we haven’t done anything with SQL Server.
After the console screen closes, it’s time to look at SQL Server to see what happened. I created a new data connection in the Server Explorer in Visual Studio. The first thing you notice is that EF has created a new database named EFLab, as specified in the connection string. Drilling down further, you see that a table named People was created. Where did this name come from? It has nothing to do with the context. EF has pluralizing rules built in. Usually, it just adds an s to the name of the entity, but it also understands how to do things like add “es” for pluralization and that Person should not be Persons, but rather People. Right-click on the table and select “Show Table Definition”.
Each property of the entity has been used to create a column in the table. EF will recognize things like Id or PersonId to be the key field and automatically make it an identity and primary key column. But what about the data? Again, right-click on People in the Server Explorer, but this time select Show Table Data.
Entity Framework inserted the data, just as we asked.
Now, we have some additional questions. If we run the code again, will the database and table be recreated or will the new row simply be added to the existing table? What about Read, Update, Delete? What happens when you change the schema? Let’s find out!
First, let’s just rerun the code, then look at the table data again. We find that there are now two entries for Bullwinkle Moose, each with a different Id. So, the first question is answered. Entity Framework just adds the record without recreating the database. It also tells us that if we create the entities for an existing database, EF will just use what’s there.
Now, let’s read, edit, then save. We have two entries for Bullwinkle Moose. Let’s edit the second one to be Rocky Squirrel. We can use the id to find the row in the table. Here’s the modified code from Main().
static void Main(string[] args)
{
EFContext context = new EFContext();
Person person = new Person();
person.FirstName = "Bullwinkle";
person.LastName = "Moose";
context.People.Add(person);
context.SaveChanges();
Person editPerson = context.People.Find(2);
editPerson.FirstName = "Rocky";
editPerson.LastName = "Squirrel";
context.SaveChanges();
}
When you look at the data afterwards, you’ll see the changes
The requested row has been changed and because the insert code is still run, another Bullwinkle Moose row was added. Let’s delete that one, but we don’t want to add another row, so comment out or delete the insert, and edit code.
static void Main(string[] args)
{
EFContext context = new EFContext();
Person deletePerson = context.People.Find(3);
context.People.Remove(deletePerson);
context.SaveChanges();
}
Run this code and the row will be deleted. But before we modify the schema, let’s see how to read data if you don’t know the Id of the row. There are a couple of good ways to do this. Before we look at them, let’s see some things that you may be tempted to try, but don’t work.
Person person1 = context.People.Find(p => p.FirstName == "Bullwinkle");
Person person2 = context.People.Where(p => p.FirstName == "Bullwinkle");
Person person3 = context.People.FirstOrDefault(p => p.FirstName == "Rocky");
The first one won’t work because Find takes an ID, not a lambda expression. The next two won’t work for two reasons. First, People is a collection and the code specifies returning a Person. Even if you change it to List<Person> it won’t work for the second reason.. it’s a LINQ expression. Let’s look at a couple of options that will work
var people1 = context.People.Where(p => p.FirstName == "Bullwinkle");
var people2 = context.People.FirstOrDefault(p => p.FirstName == "Rocky");
Now we’ll get back something that will work. But it still isn’t a Person object. You can add .ToList() to the end of the right-hand side of the line and have a ready-made, populated list.
So, we’ve seen how to create a database, insert, read, update, and delete data. But what about schema changes? How are they handled. Let’s start with changing the schema in the code. We’ll simply add a new column to the entity.
public class Person
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string State { get; set; }
}
and modify Main() to insert a new row.
static void Main(string[] args)
{
EFContext context = new EFContext();
Person person = new Person();
person.FirstName = "Peter";
person.LastName = "Peachfuzz";
person.State = "MN";
context.People.Add(person);
context.SaveChanges();
}
When you run the code, you get an exception.
Code First Migrations is new to EF 5 and is explained clearly in the MSDN documentation. But there is another way to deal with this. You can make the change manually. So, not only will you need to add the table to the entity, you’ll have to manually add it to the table too. But, you have to do one other thing. Delete the file that EF uses to track the schema.
Open SQL Server Management Studio (SSMS) and drill down into EFLab and then Tables. You’ll see the table, dbo.__MigrationHistory under System Tables.
System Tables don’t show up in Visual Studio, which is why you need to use SSMS. Delete the file. Once you’ve made the manual changes, you can run your program and Peter Peachfuzz will be inserted.
Remember that I mentioned that EF uses Id or TableId as the key field. But what if you have an existing table that doesn’t follow these rules? The solution is to add an attribute to the key field. So, it would change our model.
public class Person
{
[Key]
public int Identifier { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}
If you have a compound key, you must specify each column, and the order it appears in the key.
public class Person
{
[Key]
[Column(Order = 0)]
public string FirstName { get; set; }
[Key]
[Column(Order = 1)]
public string LastName { get; set; }
}
So, there you have it, the basics of working with Entity Framework. In part two of this series, we’ll look at one-to-many relationships.
