SQL Server for C# Developers Succinctly®
CHAPTER 4
Entity Framework Database First
Everything in the previous chapter was a lot of typing, a little tedious, and not very easy to do. It’s not very hard to create an abstraction layer that automatically maps fields from your select statements to properties on C# objects, but it won’t be very pretty. To do something like that properly is a little harder, but fortunately a few such abstraction layers already exist. In this chapter we’ll take a look at Microsoft’s Entity Framework (EF), an Object Relational Mapper (ORM) that does everything we did in the previous chapter, but a lot easier.
An ORM, as the name implies, maps objects from a relational database to objects in your code. The Entity Framework has two modes of development. First, and most traditional, is that EF generates C# classes from an already-existing database. Second is that you code out your classes with properties, foreign key relations, and primary keys, and have EF generate the database for you. These two modes are called Database First and Code First, respectively. A third option exists where EF creates C# classes from an existing database, but pretends that you’ve used code first, allowing you to use Code First with an existing database. In this chapter I’m going to show you Database First and Code First. From there it shouldn’t be difficult to use the Code First from an existing database option as well.
To extensively discuss EF, we’d need a not-so-succinct book. In fact, I’ve got a book counting over 600 pages from an older version of EF when Code First wasn’t even an option. Add to that the LINQ queries you can write, for which you can read a completely different book, and the T4 Template technology that EF uses, and you’ll understand that I can really only scratch the surface of EF in this chapter. The goal of this chapter is not to make you an expert of EF, but to apply the lessons from the previous chapter to an ORM and discuss some of the finer “gotcha!” details of EF that will save you a lot of gray hairs later on.
Database First
Let’s start with Database First, as it is pretty straightforward. Create a new C# Console Application Project, save it, and install EntityFramework through NuGet. In the menu, go to Tools > NuGet Package Manager > Manage NuGet Packages for Solution. Browse and search for EntityFramework, select your project in the list of projects on the right side of the window, and install it. Now, add a new item to your project and choose ADO.NET Entity Data Model, and name it SuccinctlyExamplesModel.
The Entity Data Model Wizard presents you with a few options; select EF designer from database. In the next screen, you can set up your connection properties and pick a database. Pick the SuccinctlyExamples database you created. After you’ve created your connection, you get a tree view with database objects. Check the tables (dbo.Gender and dbo.Person), make sure you check Pluralize or singularize generated object names, and click Finish.
You’ll see a file called SuccinctlyModels.edmx added to your project. The edmx (Entity Data Model eXtension) file holds all your mappings from database to code. Right-clicking the edmx file and selecting Open with allows you to select an XML Editor and view your mappings in XML. You should never need the XML, but if you get deeper into EF, you’ll find yourself editing or inspecting it more than you’d like. For this tutorial, though, we’re not going to look at it. So if you double-click on the edmx file, you’ll get a diagram showing all the database objects you imported earlier. If you want to import more objects or update existing objects, simply right-click somewhere in the model and click Update Model from Database. Unfortunately, if you import a table, you will always get all properties, and an update will also update all already imported tables.
Figure 8: Entity Framework diagram
Notice that Gender has a People attribute and Person has a Gender attribute (if you named GenderId just Gender, the Gender attribute would’ve been named Gender1, yuck!). The thin line between the entities describes the relation. A Gender has 0 to n People (the * part, 0..n) and a Person has 0 or 1 Gender (the 0..1 part, 0 because it’s nullable). As you can see, these are called Navigation Properties. You can rename everything here if you like (for example, if you had Gender1, you could rename it to GenderEntity, or you could rename Gender to GenderId and Gender1 to just Gender). You may also remove properties from the model if they’re nullable, but if you ever want them back, you’ll have to add them manually. The best part is that these are now C# classes you can use.
Code Listing 28: Using your entities in C#
Person p = new Person(); p.FirstName = "Mark"; p.LastName = "Zuckerberg"; p.Gender = new Gender(); |
The generated code can be found when you expand the edmx file. This is where you will find two T4 files (Text Template Transformation Toolkit); they have the .tt extension. You can expand those as well. One will have your generated entities, and the other will have your Context class, the code representation of your database. The T4 files are templates that will generate your classes using the edmx file. Remember, you can edit the generated files, but once they’re regenerated, your changes will be overwritten. If you want to generate some additional code, your best bet is to edit the T4 files.
The connection string, as you might have guessed, is added to your app.config file. The EF connection string has some extra metadata included. It’s good practice to put your entities in a separate project and reference that project from other projects. The App.config or Web.config of the startup project will need this connection string, or you’ll get a runtime error. So be sure to copy it (including metadata).
Figure 9: T4 files
If you want to add some code to a single class, you can make use of the partial keyword. All your entities are created as partial classes. That means you can “extend” them in a different file.
Code Listing 29: A partial class
public partial class Person { public string FullName { get { // FirstName and LastName are defined // in this class, but in another file. return $"{FirstName} {LastName}"; } } } |
Let’s get some data from our database. The first thing we need is our context class, which inherits from DbContext. It has a property for each of our tables and a method for each of our stored procedures.
Code Listing 30: Use the DbContext
using (var context = new SuccinctlyExamplesEntities()) { // Access the database. List<Person> people = context.People.ToList(); } |
That’s how easy it is to fetch all people from your database and load them into memory!
IQueryable
Unfortunately, there’s a lot going on in that one line of code, so let’s elaborate. First of all, context.People doesn’t really do anything yet—it’s the ToList that forces database access. This is a VERY important detail. The context.People property is a DbSet<Person>, which in turn is an IQueryable<Person>. The IQueryable<T> interface looks like any collection (IEnumerable) on the outside, but acts very differently. When using IQueryable, a query is built for the data source, in this case SQL Server. The query is only executed when the IQueryable is enumerated, like when ToList or foreach is called. That allows you to create queries using Where, OrderBy, or Select without ever going to the database. If we had to access the database many times, you could imagine this becoming very slow indeed!
Let’s build a more advanced query.
Code Listing 31: An IQueryable<Person>
using (var context = new SuccinctlyExamplesEntities()) { // Don't access the database just yet... IQueryable<Person> query = context.People .Where(p => p.GenderId == 2) .OrderBy(p => p.LastName); // Access the database. List<Person> women = query.ToList(); } |
This also allows you to create queries based on conditions.
Code Listing 32: Build queries using conditionals
using (var context = new SuccinctlyExamplesEntities()) { bool orderResults = false; // Don't access the database just yet... IQueryable<Person> query = context.People .Where(p => p.GenderId == 2); if (orderResults) { query = query.OrderBy(p => p.LastName); } // Access the database. List<Person> women = query.ToList(); } |
As you can see, this is a lot less code than we used in Chapter 1. As an added bonus, the code reads a lot easier, too (well, once you get used to the LINQ syntax). Creating different queries, depending on your needs, becomes a breeze, as you can reuse parts of your queries (like object-oriented SQL queries!).
Another concern here is parameterization. Don’t worry, everything is parameterized just fine. It’s just that in the previous example there are no parameters. We query for the people with a GenderId of 2. The value 2 is a constant, so our query won’t have any parameters. If, somewhere else in the code, we query for a GenderId of 1, we’ll get a new query with a new plan. So suppose we want to parameterize this query. What would we need to do? Easy—create a variable!
Code Listing 33: A parameterized query
using (var context = new SuccinctlyExamplesEntities()) { // Not parameterized. context.People.Where(p => p.GenderId == 2).ToList(); int female = 2; // New, parameterized query. // Doesn't re-use the previous plan. context.People.Where(p => p.GenderId == female).ToList(); int male = 1; // Parameterized, uses same query plan as above. context.People.Where(p => p.GenderId == male).ToList(); } |
Of course there are a few downsides to IQueryables as well. You don’t have any influence on what queries are generated. Some queries are so monstrous that it really pays off to just write them yourself, and this is where some SQL knowledge really comes in handy. Sometimes EF just can’t generate clean SQL queries from your IQuerable, for example, when joining two completely unrelated tables. EF will generate a query and it will get you your data, but it won’t be fast or elegant. Speaking of performance, using EF comes with a performance penalty. If those few milliseconds matter (and often they don’t), use the techniques we discussed in Chapter 2.
Another thing you cannot do in an IQueryable is use any function or property that is not known to the data source. That makes sense, as the query will have to be translated to SQL. The following code, for example, will not work.
Code Listing 34: Invalid query
using (var context = new SuccinctlyExamplesEntities()) { Person sander = context.People.SingleOrDefault(p => p.FullName == "Sander Rossel"); } |
Unfortunately, it compiles just fine. Only at runtime, when actually compiling the query, will you get an error because there is no FullName column in your table, so be sure to always run your queries against your actual data source. You might be surprised that the following query will work as expected.
Code Listing 35: Valid code
using (var context = new SuccinctlyExamplesEntities()) { List<Person> peopleWithS = context.People .Where(p => p.FirstName.ToLower() == "sander") .ToList(); } |
The ToLower function is known in SQL Server, and the EF team made sure that this function translates correctly. In theory, the StartsWith function could be translated to SQL (using SQL’s LEFT) as well, but this is not supported. For some additional supported functions (such as LEFT, RIGHT, DIFFDAYS, etc.), check out the System.Data.Entity.DbFunctions and System.Data.Entity.SqlServer.SqlFunction classes. It’s also possible to add your own functions to EF, but this is outside the scope of this book.
There’s another pitfall to the code in the previous example. Consider the following code.
Code Listing 36: Lazy-loading
using (var context = new SuccinctlyExamplesEntities()) { List<Gender> genders = context.Genders.ToList(); foreach (Gender g in genders) { foreach (Person p in g.People) { Console.WriteLine($"{p.FirstName} is a {g.Description}."); } } } |
How often will this code access the database? The only correct answer is “I don’t know.” We first get all the possible genders, which is one database call. But then we loop through the genders and we’re accessing the People of that Gender. Guess what? The People are not yet loaded into our memory, so each time we access People, EF will access the database to get all people for that Gender.
In this case we have three genders, so we have the call to get all genders, and then an additional call for each gender to get the people, which is a total of four database calls. In many scenarios you really don’t know how many entities you’re getting, and before you know it, you’re doing thousands of database calls! This is called lazy-loading, and can be turned off in your EF diagram. However, with lazy-loading turned off, your People will not be loaded and it may seem you have no people at all. I’ve seen systems get very slow because of lazy-loading, but when used correctly, it’s pretty awesome. Luckily, EF does some caching for us, so once a Navigation Property is loaded, EF won’t access the database again. “Think before you query” is the message!
Let’s take a look at some cool extensions, which make working with IQueryable a lot easier. Working with dates is always a pain, so let’s create an OlderThan extension method. I must confess I got the SQL query from Stack Overflow and just converted it to LINQ. It’s accurate enough for the example.
Code Listing 37: OlderThan extension method
public static class Extensions { public static IQueryable<Person> OlderThan(this IQueryable<Person> q, int age) { return q .Where(p => (DbFunctions.DiffHours(p.DateOfBirth, DateTime.Today) / 8766) > age); } } |
The usage is simple and readable. The original query would be unreadable to anyone, but it’s clear what it does from the name, (OlderThan). It’s a win-win situation.
Code Listing 35: OlderThan usage
using (var context = new SuccinctlyExamplesEntities()) { List<Person> people = context.People.OlderThan(45).ToList(); } |
Remember that the function or property you’re using in an IQueryable must be present in the data source. That means creating an interface and using that on different types to reuse your extension method is only possible when the interface member has the same name as your entity member. So if you have multiple entities with a DateOfBirth and you want to reuse your extension method, that’s possible, but only when you implement the extension method as a generic function (after all, IQueryable works on entities, not on interface types). The generic must be a class, because a struct may never be an entity.
Code Listing 36: Using an interface
public interface IDateOfBirth { DateTime? DateOfBirth { get; set; } } public partial class Person : IDateOfBirth { } public static class Extensions { public static IQueryable<T> OlderThan<T>(this IQueryable<T> q, int age) where T : class, IDateOfBirth { return q .Where(p => (DbFunctions.DiffHours(p.DateOfBirth, DateTime.Today) / 8766) > age); } } |
If you have multiple entities with different property names (for example, Machine with ManufacturedDate), you’re out of luck. Maybe Expression Trees can help you there.
To get the query that is generated by EF and send to the database, you can use your DbContext.Database.Log property, which is an Action<string> and outputs any generated SQL to the delegate. You could, for example, log to the Console.
Code Listing 40: Output SQL to the Console
using (CodeFirstContext context = new CodeFirstContext()) { context.Database.Log = Console.Write; context.People.Where(p => p.GenderId == 1).ToList(); } Console.ReadKey(); |
Expression trees
Another detail in working with IQueryable is that it contains many extension methods that are also defined on IEnumerable. Or so it seems. The following code looks the same for the IEnumerable and the IQueryable, but only one of the two will make your application come to a grinding halt!
Code Listing 37: IEnumerable vs. IQueryable
using (var context = new SuccinctlyExamplesEntities()) { IQueryable<Person> peopleQuery = context.People; IEnumerable<Person> peopleEnumr = context.People; peopleQuery.Where(p => p.FirstName == "Sander").ToList(); peopleEnumr.Where(p => p.FirstName == "Sander").ToList(); } |
The two Where methods differ in input parameter, and in what they return. The IQueryable takes an Expression<Func<Person, bool>> as input and returns an IQueryable, whereas the IEnumerable takes a Func<Person, bool> as input and returns an IEnumerable.
Figure 10: IQueryable.Where
Figure 11: IEnumerable.Where
The Expression<Func<SomeType, bool>> is basically a representation of the Func<SomeType, bool> in an object graph. So instead of passing in a method like in the IEnumerable.Where, you pass in some object that represents the method. In this case the compiler creates the object graph, so you don’t need to worry about that.
In the previous example, that means the IEnumerable.Where enumerates over the collection, forcing a roundtrip to the database (after all, the IEnumerable variable is actually an IQueryable), and only after that does it execute the Where function. As a result, you’ll get ALL people from the database instead of just those with the FirstName “Sander”. So be very careful that you don’t accidentally break your IQueryable chain.
Just for fun, here’s the Expression<Func<Person, bool>> the compiler actually created from that lambda expression.
Code Listing 38: An Expression<Func<Person, bool>>
ParameterExpression parameter = Expression.Parameter(typeof(Person), "p"); Expression property = Expression.Property(parameter, typeof(Person).GetProperty("FirstName")); Expression constant = Expression.Constant("Sander"); Expression binary = Expression.Equal(property, constant); Expression<Func<Person, bool>> lambda = Expression.Lambda<Func<Person, bool>>(binary, parameter); peopleQuery.Where(lambda).ToList(); |
This means that when you want to add your own (extension) methods, you’ll have to keep in mind that you’re dealing with Expressions. And if you want to create some extra layers of abstraction, you might even need to create your own Expressions manually. Creating Expressions manually is a little too advanced for this book, but let’s take a look at an extension method.
What happens if you want to include, for example, additional select logic in your extension method? Just be sure to put in an Expression. Failing to do so will give no errors or warnings, but will call the method on IEnumerable, forcing a database call before your action executes.
Code Listing 39: Extension with selector
public static List<T> SelectOlderThan<T>(this IQueryable<Person> q, int age, Expression<Func<Person, T>> selector) { return q .Where(p => (DbFunctions.DiffHours(p.DateOfBirth, DateTime.Today) / 8766) > age) .Select(selector) .ToList(); } |
Usage is, again, pretty straightforward.
Code Listing 40: Usage of SelectOlderThan
var people = context.People.SelectOlderThan(45, p => new { Name = p.FirstName, DateOfBirth = p.DateOfBirth }); |
CRUD
Let’s check out the CRUD operations. You won’t believe how easy it is.
Code Listing 41: CRUD operations
using (var context = new SuccinctlyExamplesEntities()) { // Update Sander. Person sander = context.People.FirstOrDefault(p => p.FirstName == "Sander"); sander.FirstName = "John"; // Create Google CEO. Person sundar = new Person(); sundar.FirstName = "Sundar"; sundar.LastName = "Pichai"; sundar.DateOfBirth = new DateTime(1972, 7, 12); sundar.GenderId = 1; context.People.Add(sundar); // Delete Mark Zuckerberg. Person mark = context.People.FirstOrDefault(p => p.FirstName == "Mark"); context.People.Remove(mark); context.SaveChanges(); } |
That’s all there is to it! It’s so easy, you don’t really need further explanation.
Remember that I said “SELECT * …” can be very harmful to a system. What we’re doing now is basically that. If we update our model and Person gets 100 new fields, our system suddenly gets a lot slower. We can fix this using Select.
Code Listing 42: Using Select
using (var context = new SuccinctlyExamplesEntities()) { // Using an anonymous type. var satyaAnon = context.People.Where(p => p.FirstName == "Satya") .Select(p => new { Id = p.Id, FirstName = p.FirstName, LastName = p.LastName }).SingleOrDefault(); // Use the anonymous type here... // Using a known type. PersonModel satyaKnown = context.People.Where(p => p.FirstName == "Satya") .Select(p => new PersonModel { Id = p.Id, FirstName = p.FirstName, LastName = p.LastName }).SingleOrDefault(); // Use the known type here, // pass it to other functions, // or return it from this function. } |
Using this style of querying makes a difference for your update and delete statements as well. Since you don’t want to get the entire entity anymore, you’ll have to get the ID (given you don’t have it yet) and use that to do your updates and deletes. In most scenarios, you’ll have the IDs at your disposal, so the selects are not necessary.
Code Listing 43: CRUD without getting entire entities
using (var context = new SuccinctlyExamplesEntities()) { // Update Sander. // Get Sander's ID. var sander = context.People.Where(p => p.FirstName == "Sander") .Select(p => new { Id = p.Id }).SingleOrDefault(); // Create a new Person, but set the Id to an existing one. Person update = new Person(); update.Id = sander.Id; // Attach Person with the given Id. context.People.Attach(update); // Fields that are set after attach will be tracked for updates. update.FirstName = "John";
// Delete Mark Zuckerberg. // Get Mark's ID. var mark = context.People.Where(p => p.FirstName == "Mark") .Select(p => new { Id = p.Id }).SingleOrDefault(); Person delete = new Person(); delete.Id = mark.Id; context.People.Attach(delete); context.People.Remove(delete); context.SaveChanges(); } |
That was a quick overview of the Entity Framework. We’ve addressed creating a model, importing tables, creating queries, parameterization, lazy-loading, and CRUD operations. Unfortunately, as you’ve seen, there are many “gotchas” when working with EF (or any ORM, for that matter). I know these things because I’ve done them wrong in the past. I’ve seen entire teams, with years of experience, still fall for some of the pitfalls laid out in this chapter. Often have I heard “EF sucks because it is so slow,” but more often than not, this was the result of not parameterizing, not knowing when IQueryables are executed (or even just not knowing about IQueryable at all), lazy-loading, and selecting too much data (or just poor database design, but that’s another subject altogether).
- 1800+ high-performance UI components.
- Includes popular controls such as Grid, Chart, Scheduler, and more.
- 24x5 unlimited support by developers.