Code-First Development with Entity Framework (2015)

Chapter 4. Querying, Inserting, Updating, and Deleting Data

In this chapter, we will learn how to query data in your database using Entity Framework and LINQ. We will understand the details of query life cycle. We will see how to use eager and lazy loading. We will also study how to sort and filter data. You will learn the use of relationships in our queries. We will add, update, and delete data in the database using multiple approaches for each operation.

In this chapter, we will cover the following topics:

· How to use a method and the query syntax with LINQ

· How to filter and sort data in your queries

· Learn the pitfalls and advantages of lazy and eager loading

· Cover multiple approaches for data manipulation

The basics of LINQ

Language INtegrated Query (LINQ) is the language that we use with Entity Framework to construct and execute queries against a database. A query is a statement that retrieves data from one or more tables. LINQ has many query implementations. At the most basic level, .NET includes LINQ in an object's functionality that allows you to query in-memory collections. LINQ to entities is typically the name that is used when talking about LINQ in relation to Entity Framework. This technology uses Entity Framework in conjunction with a provider for a specific RDBMS to convert LINQ statements to SQL queries. Entity Framework takes care of materialization; the process of converting the results of SQL queries into collections of .NET objects or individual objects.

When you use LINQ to entities queries, you will find out that the SQL is executed against the database when you enumerate the query results. Entity Framework converts LINQ queries to expression trees and then command trees and then they are passed to the provider, which finally executes a SQL query against the database engine it supports. For example, if you step through the following code while you have SQL Profiler running, you will see that the query will be run against the database when you step through the second line, but not the first line of code, as shown in the following code snippet:

var query = context.People;

var data = query.ToList();

The same code in VB.NET looks as follows:

Dim query = context.People

Dim data = query.ToList()

It may not be obvious from the first reading of this code, but the ToList function is the function that causes the enumeration of the query results to occur. The Entity Framework provider for SQL Server is used in the implementation of the IQueryable interface in our user case. Hence, when the GetEnumerator function is called on IQueryable, which is one of the interfaces that DbSet<T> implements, a SQL query is constructed and run by Entity Framework and the SQL Server provider for Entity Framework.

LINQ supports two types of query syntax:

· The method syntax

· The query syntax

It is entirely up to you which syntax you want to use, as there is parity between both syntaxes. As the name implies, the query syntax looks similar to SQL queries from the language perspective. The method syntax, on the other hand, looks like typical function calls in C# or VB.NET.

All of the following examples do not include the code that creates and disposes of DbContext for brevity. The context is stored in the context variable. We are going to use a database with a structure very similar to the one in Chapter 3, Defining the Database Structure, for all of the examples in this chapter.

Filtering data in queries

Filtering refers to the process of narrowing down the results of your query base on a condition. Let's take a look at both syntaxes of LINQ using filtering as an example:

var query = from person in context.People

where person.HeightInFeet >= 6

select person;

var methodQuery = context.People.Where(p => p.HeightInFeet >= 6);

The purpose behind the query is to retrieve the people who are at least 6 feet tall. This means that we have a specific condition that all the data in our results must match. Both lines of code answer this question. The first line uses query syntax. There are distinct parallels to the SQL query syntax. We have the from, where, and select blocks of code. Their order is different from SQL, but they certainly serve the same purpose. When it comes to actual filtering, we are using the where keyword to specify the filter that is applied to a query variable called person, which we used in the from block of our query. The actual filter is using the ordinary C# or VB.NET syntax to compare two expressions; the property of a person object, and a constant for the minimum height. The second line of code uses the method syntax. As the name implies, we see method calls via extension methods on any list that implements IEnumerable. We do not see the select method call, as it is optional in queries based on the method syntax. We will see examples of the selectmethod in the next chapter. We do see the where method call that is applied to the collection, which is the source of the data. The same source was mentioned in the from block inside the query syntax solution to this filter problem. The same method syntax code in VB.NET looks only slightly differently because of the language differences between VB and C# for Lambda expressions. The query syntax code is virtually identical in both languages. The following is the VB.NET syntax:

Dim query = From person In context.People

Where person.HeightInFeet >= 6

Select person

Dim methodQuery = context.People.Where(Function(p) p.HeightInFeet >= 6)

We will continue to see both syntaxes in the following examples to provide a thorough explanation of the differences between the two. Typically, you would want to stick to the same syntax in order to have more consistent code, but you are free to pick the syntax you like better. If you are familiar with SQL and use it on a regular basis, you may find the query syntax a bit more intuitive. Ultimately, it does not matter which one you pick.

You can also combine multiple filter expressions in a single query. As you might have guessed, it is just a matter of ANDing the two filter criteria. We will demonstrate this by providing an example that is using a string-based filter as well as a Boolean filter, as shown in the following code snippet:

var query = from person in context.People

where

person.HeightInFeet >= 6 &&

person.FirstName.Contains("J") &&

person.IsActive

select person;

var methodQuery = context.People

.Where(p =>

p.HeightInFeet >= 6 &&

p.FirstName.Contains("J") &&

p.IsActive);

We are using the standard Contains method of the String class to perform filtering similar to the LIKE query in SQL. This signifies that the standard .NET code is converted to SQL by Entity Framework and the provider together. Boolean or date comparisons workexactly the same way. You can use any Boolean expressions for filtering. The code in VB.NET is very similar with the exception of a few keywords, as shown in the following code snippet:

Dim query = From person In context.People

Where

person.HeightInFeet >= 6 And

person.FirstName.Contains("J") And

person.IsActive

Select person

Dim methodQuery = context.People _

.Where(Function(p) p.HeightInFeet >= 6 And

p.FirstName.Contains("J") And

p.IsActive)

All your standard comparison operators work just fine in LINQ queries, as well as Entity Framework queries.

Tip

Remember that the case sensitivity of string comparison depends on the database engine and collation. SQL Server is case insensitive by default, whereas Oracle is case sensitive by default for Latin-based collation. This applies to the filtering and sorting of data. The ToUpper method of the String class is your friend for solving case sensitivity.

Sorting data in queries

Most of the time, as you retrieve your data from the database, you need to present it in a certain order. This order can include one or more fields and can be ascending, going from the smallest to the largest value, or descending, going from the largest to the smallest. You will find the sorting query syntax quite familiar, if you are accustomed to writing SQL queries. The following example will sort the person data on the last and first names of a person in ascending order. We are also going to combine filtering with sorting to illustrate how these two concepts work together in a single query, as shown in the following code snippet:

var query = from person in context.People

where person.IsActive

orderby person.LastName, person.FirstName

select person;

var methodQuery = context.People

.Where(p => p.IsActive)

.OrderBy(p => p.LastName)

.ThenBy(p => p.FirstName);

The first query uses the query syntax and the second one uses the method syntax. If you want to sort by multiple fields, then instead of using the Order By clause for both fields, you will need to use the ThenBy operator for the method syntax. Here is how the queries look in VB.NET:

Dim query = From person In context.People

Where person.IsActive

Order By person.LastName, person.FirstName

Select person

Dim methodQuery = context.People _

.Where(Function(p) p.IsActive) _

.OrderBy(Function(p) p.LastName) _

.ThenBy(Function(p) p.FirstName)

When the descending sort order is required, you will need to use OrderByDescending for the first field and ThenByDescending for subsequent fields in queries with the method syntax. If you are using the query syntax, you will need to follow the property name with thedescending keyword. You are free to combine the descending and ascending orders in a single query, as shown in the following code snippet:

var query = from person in context.People

where person.IsActive

orderby person.LastName descending, person.FirstName descending

select person;

Exploring LINQ functions

There are many LINQ functions that you will need to know in order to be proficient with queries in Entity Framework.

Element operations

Element operations allow you to select a single row. Sometimes they are enhanced to select null if a row that matches the target condition does not exist. Typically, you would combine element functions with a filter condition, though this is not necessary. As element functions work on sets of data, you will need to construct a query first and then apply an element function. If you are using the method syntax, you can combine both actions into a single statement. If you are using the query syntax, you would need to apply an element function to the entire query. For example, let's select a single record based on the last name of a person, as shown in the following code:

var query = from person in context.People

where person.LastName == "Doe"

select person;

var first = query.First();

var methodQuery = context.People.Where(p => p.LastName == "Doe");

first = methodQuery.First();

In the preceding example, we used the First() method to find a first matching row in the database. If you look at the type of the first variable, you will see that it is of the type person. Hence, we fetch an item from a set of persons. Here is the same example in VB.NET:

Dim query = From person In context.People

Where person.LastName = "Doe"

Select person

Dim first = query.First()

Dim methodQuery = context.People _

.Where(Function(p) p.LastName = "Doe")

first = methodQuery.First()

You can easily combine the last two lines of the example into a single statement, which is much more common. The First function has an overload that accepts an expression for the filter condition or the Where clause as shown in the following code line:

first = context.People.First(p => p.LastName == "Doe");

This can also be represented in VB.NET as follows:

first = context.People.First(Function(p) p.LastName = "Doe")

You will notice that the LINQ query syntax is a bit more verbose in the case of element operations. Hence, most people use the method syntax, as shown in the last example, for the purposes of finding a single row in the database. This typically results in a single line of code that you have to write.

When you use the First function and you have multiple rows in the database that match the condition, only one row will be picked up and returned. If you have no rows that match the condition, an exception will be thrown. Hence, if you want to guard against such an exception, you can use the FirstOrDefault function instead of First. As we deal with entities, which are classes or reference types, their default is null. So, before you use the results of the FirstOrDefault execution, you need to test for a null value. Outside of this fact, the FirstOrDefault code is identical to First.

There are two more operations that are similar to First, called Single and SingleOrDefault. The only difference from First is that if you have more than one row that matches your condition, an exception is thrown. Feel free to write an example now, that is using theSingle function. LINQ has other element operators, such as Last and ElementAt, however, they do not make much sense in terms of Entity Framework, hence an exception will be thrown if you use them inside LINQ to entities queries. You can use them inside LINQ to objects queries. If you want to use the Last function with Entity Framework, simply use First and reverse the sorting order.

Quantifiers

Occasionally, you need to check whether you have at least one row that matches a condition, or all rows match a filter. This is where the Any and All operations come in. Together, they are referred to as quantifiers. As the name implies, these operators return a Boolean value. Both are applied to a query, hence the code is quite similar to the preceding First example. This similarity is shown in the code:

var hasDoes = (from person in context.People

where person.LastName == "Doe"

select person).Any();

hasDoes = context.People.Any(p => p.LastName == "Doe");

var allHaveJ = context.People.All(p => p.FirstName.Contains("J"));

There are a few things to notice here. First of all, we combined a query with a quantifier in a single statement to illustrate how you can cut down on the number of lines of code using the query syntax. Then, we performed the same check—whether there is at least one person with the last name of Doe in the database using the method syntax. Finally, we checked to make sure that all people have the letter J in their first names. Here is the same code using VB.NET:

Dim hasDoes = (From person In context.People

Where person.LastName = "Doe"

Select person).Any()

hasDoes = context.People.Any(Function(p) p.LastName = "Doe")

Dim allHaveJ = context.People.All(Function(p) p.FirstName.Contains("J"))

You can easily substitute the Any call with a standard query with a Where clause and check whether the result has any rows. However, you want to use Any for such a purpose instead, because it will result in a more efficient SQL statement. Typically, it will use theEXISTS syntax instead of the WHERE SQL syntax, thus short-circuiting the execution upon finding the first row.

Working with related entities

At times, we need to write queries that involve more than a single entity type. We are now going to take a look at how we can work with entities that are involved in relationships.

Filtering based on related data

Sometimes, we need to filter based on related data. For example, we want to find people who have phone numbers that start with "1". It is worth noting that this additional task is performed purely based on relationships between a person and phone entities, using the Phones property on the person class, as shown in the following code snippet:

var query = from person in context.People

where person.IsActive &&

person.Phones.Any(ph => ph.PhoneNumber.StartsWith("1"))

select person;

var methodQuery = context.People

.Where(p => p.IsActive &&

p.Phones.Any(ph => ph.PhoneNumber.StartsWith("1")));

Again, we use the plain string function, StartsWith in the preceding case, yet this will translate into LIKE '1%' in the where clause in the SQL Server case. We also use the familiar Any function to only find people with at least one phone number that starts with the number 1. We also (in a way) add the method syntax to the query syntax, giving us additional flexibility. Here is how this code looks in VB.NET:

Dim query = From person In context.People

Where person.IsActive And

person.Phones.Any(Function(ph) ph.PhoneNumber.StartsWith("1"))

Select person

Dim methodQuery = context.People _

.Where(Function(p) p.IsActive And

p.Phones.Any(Function(ph) ph.PhoneNumber.StartsWith("1")))

Lazy and eager loading

We previously talked about the difference between eager and lazy loading. Both concepts are approaches to loading-related entities. For example, you may use either approach to load phone numbers for a person. If you are trying to decide which approach you need to use in a particular situation, the following rule should answer this question for you. If you are not sure that you are going to need related entries, you can use lazy loading. If you know you will certainly need related data, then use eager loading. You will need to be careful when you decide to eagerly load many relationships. This can result in very complex queries and thus can have performance implications. You can encounter problems with lazy loading as well. For example, you want to retrieve 100 rows from thePeople table and then display phone numbers for each person. If you use lazy loading, as you start enumerating through a list of phone numbers, Entity Framework will issue a query to retrieve that data for each person's phones list. This processing will result in 101 queries issued against the database. One query will retrieve 100 people, then one more for each person to get phone numbers. It takes time to perform these actions, so you may be looking at an extra few hundred milliseconds when using lazy loading in this use case. You need to carefully decide which approach is right for each of your situations. By default, lazy loading is enabled in Entity Framework. You can turn it off for an instance of DbContext by accessing configuration options on DbContext after context is created, which is shown as follows:

context.Configuration.LazyLoadingEnabled = false;

It may be useful to you to know how lazy loading is implemented in Entity Framework. If you run the following code and break on the line that executes the foreach loop that looks on phone numbers and examine an instance of the person object, you will notice something interesting. The type of the person object is not really Person, but instead something like System.Data.Entity.DynamicProxies.Person_XXXXXX. Entity Framework dynamically created a class that inherits from Person in order to intercept property getter calls: in our case, the Phones property. Then, in the property getter, it dynamically issues a query to populate the Phones list. However, your code can just assume that the data will be automatically populated. This is shown in the following code snippet:

var query = from person in context.People

select person;

foreach (var person in query.ToList())

{

foreach (var phone in person.Phones)

{

Now, let's take a look at eager loading. You have to use the Include method in order to proactively load the related data you need. There is one parameter that this method takes, and it is the property expression that points to a related entities property. Let's implement the preceding code, but use eager loading, This is shown in the following code snippet:

var query = from person in context.People.Include(p => p.Phones)

select person;

foreach (var person in query)

{

There are two overloads of the include method. The one we used took a property expression. You can also use a string to describe the path of the relationship. This is shown in the following code snippet:

var query = from person in context.People.Include("Phones")

select person;

Ordinarily, you want to use the property expression method because you can take advantage of compile-time checking. The only time you need to use string-based overload is when your path cannot be described via a property expression. For example, if you want to load multiple levels of relationships. If Phones were to have types, your Include method may look like Include("Phones.PhoneType").

Here is how the code that uses eager loading looks in VB.NET:

Dim query = From person In context.People.Include(Function(p) p.Phones)

Select person

For Each person As Person In query

Console.WriteLine(person.LastName)

For Each phone As Phone In person.Phones

You noticed that we used the ToList call before running through the query when lazy loading is used. This is typically not necessary and really not even recommended. However, in the case of lazy loading, you have to follow this pattern. The problem is that when a lazy loaded property is populated by Entity Framework, a new data reader is created. However, we already have an open data reader that is reading in our primary, top-level query, which is the person query in our case. ADO.NET has a limitation, where only one open reader is allowed per database connection. As a result, if you remove the function ToList from the code that is using lazy loading, you will encounter an exception. A call to functions such as ToList or ToArray cause what is referred to as an immediate execution of the query to occur. This is in contrast to simply enumerating the results of a query, as in the eager loading example, when we use deferred query execution. SQL is executed when query results are enumerated in both cases, but with a slight difference.

Tip

You should consider the performance implications of using eager loading versus lazy loading any time you are retrieving related data.

Inserting data into the database

There are many ways to insert new data into your database. You can add new objects to the collection, as we did in previous chapters. You can also set the state to Added on each entity. If you are adding entities that contain child entities, the Added state is propagated to all the objects in the graph. In other words, Entity Framework assumes that you are attaching a new object graph if the root entity is new. The object graph term typically refers to a number of related entities that form a complex tree structure. For example, if we have a person object with a number of phone numbers contained in a list property on the Person class, we are dealing with an object graph, where the person entity is a root object. Phone entities are, in essence, children of that person object. Since we have seen a simple functionality, let's work through this complex addition scenario.

First, we will create a new person instance with phone numbers. Then, we will add this person instance to the context. Finally, we will call SaveChanges to commit the rows to the database as shown in the following code snippet:

var person = new Person

{

BirthDate = new DateTime(1980, 1, 2),

FirstName = "John",

HeightInFeet = 6.1M,

IsActive = true,

LastName = "Doe",

MiddleName = "M"

};

person.Phones.Add(new Phone { PhoneNumber = "1-222-333-4444" });

person.Phones.Add(new Phone { PhoneNumber = "1-333-4444-5555" });

using (var context = new Context())

{

context.People.Add(person);

context.SaveChanges();

}

There are a few differences from the code we saw previously. We create our objects before we initialize the context. This stresses the point that Entity Framework tracks whether entities in the context at the time are attached or added. You can, for example, have your person entity from the preceding example be a parameter to a method inside the Web API controller. Then, inside that function, you will just add the entity with the children to the context and save it, as shown. Here is how this code looks in VB.NET:

Dim person = New Person() With {

.BirthDate = New DateTime(1980, 1, 2),

.FirstName = "John",

.HeightInFeet = 6.1D,

.IsActive = True,

.LastName = "Doe",

.MiddleName = "M"

}

person.Phones.Add(New Phone() With {.PhoneNumber = "1-222-333-4444"})

person.Phones.Add(New Phone() With {.PhoneNumber = "1-333-4444-5555"})

Using context = New Context()

context.People.Add(person)

context.SaveChanges()

End Using

You can also add multiple entities at the same time, since DbSet has the AddRange method that will allow you to pass in a number of entities.

This is not the only way to insert new data, though it is simple, straightforward, and easy to read and understand. Another way is to directly set the entity state using the DbContext API, as shown in the following code snippet:

using (var context = new Context())

{

context.Entry(person2).State = EntityState.Added;

context.SaveChanges();

}

We did not include all the code, just the differences from the approach that is using the Add method on DbSet. Here is how the code looks in VB.NET:

Using context = New Context()

context.Entry(person2).State = EntityState.Added

context.SaveChanges()

End Using

The Entry method on DbContext returns an instance of the DbEntityEntry class. This class has a number of useful properties and methods that are needed for more advanced implementations and scenarios with Entity Framework. For example, you might useOriginalValues and DatabaseValues in order to handle conflict resolution during optimistic concurrency handling. There is also the GetValidationResult method that you can use to ensure that the data is valid from the perspective of the rules we specified in ourEntityTypeConfiguration classes for our entities. For example, if you have a required string property, you cannot leave it as null, and this method will provide you with an error. You can also use DbEntityEntry to inquire what the state of the object is instead of setting a new state. If you want to, try to get the state right after calling the Add method in the first insert example and ensure that the state for the new person is indeed Added. These are the states supported by the EntityState enumeration:

Updating data in the database

What does it mean to update data in the database? We want to replace one or more column values in a table's row with new values. Entity Framework will issue an update query when it knows that an entity has changed since it was first attached to DbContext, either by viewing a LINQ query that was enumerated, or via a call to Attach method of DbSet. The simplest way to find an entity you want to update is to use a query. Then, change one or more properties to new values and call SaveChanges. From the moment we query the data, Entity Framework will start tracking changes to each property. When SaveChanges is finally called, only changed properties will be included in the update SQL operation. When you want to find an entity to update in the database, you typically look for it based on the primary key value. We already saw how to use the Where method to achieve this. Entity Framework also has the Find method exposed on DbSet. This method takes one or more values as parameters that correspond to the primary key of the table mapped to thatDbSet. We use the column that has a unique ID as the primary key in our example, hence we only need a single value. If you use composite primary keys, consisting of more than one column, which is typical for junction tables, you will need to pass the values for each column that the primary key is comprised of in the exact order of the primary key columns. If you are following along with this exercise, open SSMS or the SQL Server Object Explorer window inside Visual Studio and find the ID of a person in the People table to practice on. In the following code, the value passed to the Find function is 1:

using (var context = new Context())

{

var person = context.People.Find(1);

person.FirstName = "New Name";

context.SaveChanges();

}

The same code in VB.NET looks as follows:

Using context = New Context()

Dim person = context.People.Find(1)

person.FirstName = "New Name"

context.SaveChanges()

End Using

If you trap the SQL query sent to the SQL Server when SaveChanges is called, it will look as follows:

UPDATE [dbo].[People]

SET [FirstName] = @0

WHERE ([PersonId] = @1)

This proves that indeed only the changes that are made explicitly are sent back to the database. For example, the last name was not updated, since it was not changed. If you look in SQL Profiler for the entire code block, you will see that the Find method also resulted in a query that is shown in the following code snippet:

SELECT TOP (2)

[Extent1].[PersonId] AS [PersonId],

[Extent1].[PersonTypeId] AS [PersonTypeId],

[Extent1].[FirstName] AS [FirstName],

[Extent1].[LastName] AS [LastName],

[Extent1].[MiddleName] AS [MiddleName],

[Extent1].[BirthDate] AS [BirthDate],

[Extent1].[HeightInFeet] AS [HeightInFeet],

[Extent1].[IsActive] AS [IsActive]

FROM [dbo].[People] AS [Extent1]

WHERE [Extent1].[PersonId] = @p0

Find was translated into the SingleOrDefault method call. That is why we selected the Top (2) rows. We want to make sure that there is only one entity that matches the primary key.

If you are writing a desktop Windows application, you may choose to use the approach of keeping the context around after a query is fired to issue updates. The entity must remain connected to the context from a query to the SaveChanges call timeline. You can find an entity, let the user make changes, and finally call SaveChanges. If you want to model this approach in our code, user interactions correspond to the person.FirstName = "New Name" line of code. If you are working on a web application, this approach does not work. You cannot keep the original context around or between two web server calls. You do not really need to take the overhead of finding an entity twice, once to show to the user and the second time to update. Instead, let's use the second approach from the insert examples and set the state, as shown in the following code snippet:

var person2 = new Person

{

PersonId = 1,

BirthDate = new DateTime(1980, 1, 2),

FirstName = "Jonathan",

HeightInFeet = 6.1m,

IsActive = true,

LastName = "Smith",

MiddleName = "M"

};

person2.Phones.Add(new Phone

{

PhoneNumber = "updated 1",

PhoneId = 1,

PersonId = 1

});

person2.Phones.Add(new Phone

{

PhoneNumber = "updated 2",

PhoneId = 2,

PersonId = 1

});

using (var context = new Context())

{

context.Entry(person2).State = EntityState.Modified;

context.SaveChanges();

}

You can imagine that the data we created initially, prior to instantiating the context, was submitted via a web call to our Web API controller. Then, once inside the controller, we create the context, set the state, and save changes. If you look at the results of this code inside the database, you might be surprised to find out that the person data was updated, but the phone data was not. This occurred because of a fundamental difference between the insert and update implementation inside Entity Framework. When you set the state to modified, Entity Framework does not propagate this change to the entire object graph. So, to make this code work properly, we need to add a little bit more code, as shown in the following code snippet:

using (var context = new Context())

{

context.Entry(person2).State = EntityState.Modified;

foreach (var phone in person2.Phones)

{

context.Entry(phone).State = EntityState.Modified;

}

context.SaveChanges();

}

All we had to do manually was set the state of each changed entity. Of course, if you have a new phone number in the collection, you can set its state to Added instead of Modified. There is one more important concept contained within the code. Whenever we use the state change approach, we must know all the columns' data, including the primary key for each entity. This is because Entity Framework assumes that when the state is changed, all the properties need to be updated. Here is how the code looks in VB.NET:

Dim person2 = New Person() With {

.PersonId = 1,

.BirthDate = New DateTime(1980, 1, 2),

.FirstName = "Jonathan",

.HeightInFeet = 6.1D,

.IsActive = True,

.LastName = "Smith",

.MiddleName = "M"

}

person2.Phones.Add(New Phone() With {.PhoneNumber = "updated 1", .PhoneId = 1, .PersonId = 1})

person2.Phones.Add(New Phone() With {.PhoneNumber = "updated 2", .PhoneId = 2, .PersonId = 1})

Using context = New Context()

context.Entry(person2).State = EntityState.Modified

For Each phone In person2.Phones

context.Entry(phone).State = EntityState.Modified

Next

context.SaveChanges()

End Using

If you capture SQL queries sent when this code is run, you will see three update queries—one for the person and one more for each of the two phone numbers.

It is also worth repeating that Entity Framework tracks the state of the entities once they are attached to the context. So, if you query the data, the context starts tracking your entities. If you are writing a web application, this tracking becomes an unnecessary overhead for query operations. The reason it is unnecessary is because you will dispose of the content, destroying the tracking as soon as the web request to get the data completes. Entity Framework has a way to reduce this overhead. For example:

using (var context = new Context())

{

var query = context.People.Include(p => p.Phones).AsNoTracking();

foreach (var person in query)

{

foreach (var phone in person.Phones)

{

}

}

}

If you put a breakpoint inside the loop and check the entity state, using context.Entry(person).State and context.Entry(phone).State expressions, you will see that the state is Detached for both entities. This means that this entity is not tracked by the context, thus reducing your overhead by using the AsNoTracking method.

The same code in VB.NET looks as follows:

Using context = New Context()

Dim query = From person In context.People.Include(Function(p) p.Phones).AsNoTracking()

Select person

For Each person As Person In query

For Each phone As Phone In person.Phones

Next

Next

End Using

We also combined turning off change tracking with eager loading. What if even in web environments you only want to update just the properties that are changed by a user? One big assumption is that you will have to track what is changed in your web application on the client. Assuming that this is accomplished, you can use yet another approach to accomplish the update operation. You can use the Attach method on DbSet. This method essentially sets the state to Unchanged and context starts tracking the entity in question. After you attach an entity, you can just set one of the changed properties at a time. You must know in advance which properties have changed. For example:

var person3 = new Person

{

PersonId = 1,

BirthDate = new DateTime(1980, 1, 2),

FirstName = "Jonathan",

HeightInFeet = 6.1m,

IsActive = true,

LastName = "Smith",

MiddleName = "M"

};

using (var context = new Context())

{

context.People.Attach(person3);

person3.LastName = "Updated";

context.SaveChanges();

}

This code will result in a query that only updates the LastName column and nothing else. The same code in VB.NET looks as follows:

Dim person3 = New Person() With {

.PersonId = 1,

.BirthDate = New DateTime(1980, 1, 2),

.FirstName = "Jonathan",

.HeightInFeet = 6.1D,

.IsActive = True,

.LastName = "Smith",

.MiddleName = "M"

}

Using context = New Context()

context.People.Attach(person3)

person3.LastName = "Updated"

context.SaveChanges()

End Using

Alternatively, instead of calling the Attach method, you can simply set the state by calling context.Entry(person3).State = EntityState.Unchanged. Just replace one line of code that calls Attach with this line and you are done.

Deleting data from the database

Interestingly enough, there is a lot of similarity in the approaches we used for updates and deletions. We can use a query to find data and then mark it for deletion by using the Remove method of DbSet. This approach actually has the same drawbacks as it does with the update, resulting in a select query in addition to the delete query. Nonetheless, let's take a look at how it is done:

using (var context = new Context())

{

var toDelete = context.People.Find(personId);

toDelete.Phones.ToList().ForEach(phone => context.Phones.Remove(phone));

context.People.Remove(toDelete);

context.SaveChanges();

}

This code deletes each child entity, phone in our case, and then deletes the root entity. You would have to know the primary key value for the entity you want to delete. The preceding code assumes that you have this value in the personId variable. In the case of a web application, this value will be submitted to the method that handles deletion. Alternatively, we could use the RemoveRange method to remove multiple entities in a single statement. Here is how the code looks in VB.NET:

Using context = New Context()

Dim toDelete = context.People.Find(personId)

toDelete.Phones.ToList().ForEach(Function(phone) context.Phones.Remove(phone))

context.People.Remove(toDelete)

context.SaveChanges()

End Using

There is one important difference between this code and the insert code. We have to manually delete each child record, by removing it from a corresponding collection. Code that is provided relies on lazy loading to work to populate the list of phones for a person. You can also rely on a cascade of delete operation instead, though some DBAs will frown at this practice.

Now, let's delete entities by setting a state on each entity. Again, we need to account for dependent entities. For example:

var toDeleteByState = new Person { PersonId = personId };

toDeleteByState.Phones.Add(new Phone

{

PhoneId = phoneId1,

PersonId = personId

});

toDeleteByState.Phones.Add(new Phone

{

PhoneId = phoneId2,

PersonId = personId

});

using (var context = new Context())

{

context.People.Attach(toDeleteByState);

foreach (var phone in toDeleteByState.Phones.ToList())

{

context.Entry(phone).State = EntityState.Deleted;

}

context.Entry(toDeleteByState).State = EntityState.Deleted;

context.SaveChanges();

}

You undoubtedly noticed something very different from any other data manipulation. In order to delete a person, we only need to set the primary key property and nothing else. For phones, we just needed to set the primary key property and parent identifier property. In the case of web applications, you need to submit all the identifiers, or you will need to resort to requerying child data to find the identifiers. Here is how this code looks in VB.NET:

Dim toDeleteByState = New Person With { .PersonId = personId }

toDeleteByState.Phones.Add(New Phone With {.PhoneId = phoneId1, .PersonId = personId })

toDeleteByState.Phones.Add(New Phone With {.PhoneId = phoneId2, .PersonId = personId })

Using context = New Context()

context.People.Attach(toDeleteByState)

For Each phone In toDeleteByState.Phones.ToList()

context.Entry(phone).State = EntityState.Deleted

Next

context.Entry(toDeleteByState).State = EntityState.Deleted

context.SaveChanges()

End Using

You can submit full entities for deletion as well. If you are sticking to strict guidelines for deletion for REST web services, those define that only an identifier should be submitted with a web request. So, you will need to decide for yourself which of the two approaches works better for your specific circumstances.

Working with in-memory data

Sometimes, you need the ability to find an entity in an existing context instead of the database. Entity Framework, by default, will always execute queries against the database when you create new context. What if your update involves calling many methods and you want to find what data was added by one of the previous methods? You can force a query to execute only against in-memory data attached to the context using the Local property of DbSet. For instance:

var localQuery = context.People.Local.Where(p => p.LastName.Contains("o")).ToList();

The same code in VB.NET looks as follows:

Dim localQuery = context.People.Local.Where(Function(p) p.LastName.Contains("o")).ToList()

What we also know is that the Find method searches local context first, prior to constructing the database query. You can easily confirm this by forcing another query to load the data you are looking for and then running a Find against one of the found entities. For instance:

var query = context.People.ToList();

var findQuery = context.People.Find(1);

If you run SQL Profiler along with this code, you will see that one query is executed against the database, which confirms that Find runs on in-memory data first. The same code in VB.NET looks as follows:

Dim query = context.People.ToList()

Dim findQuery = context.People.Find(1)

In-memory data also provides access to all the entities with their respective states via the DbChangeTracker object. It allows you to look at the entities and their DbEntityEntry objects as well. For example:

foreach (var dbEntityEntry in context.ChangeTracker.Entries<Person>())

{

Console.WriteLine(dbEntityEntry.State);

Console.WriteLine(dbEntityEntry.Entity.LastName);

}

In the preceding code, we get all the person entries that DbContext is tracking as entity entry objects. We can then look at the state of each object as well as an actual Person object that the entity entry belongs to.

Here is the same code in VB.NET:

For Each dbEntityEntry In context.ChangeTracker.Entries(Of Person)

Console.WriteLine(dbEntityEntry.State)

Console.WriteLine(dbEntityEntry.Entity.LastName)

Next

This API provides developers with rich capabilities to examine in-memory data at any time.

Self-test questions

Q1. Which of the following is NOT a syntax supported by LINQ?

1. Method.

2. SQL.

3. Query.

Q2. If you retrieve an entity via LINQ from a database, make changes to it and call SaveChanges, all properties are updated in the database, not only the changed ones, true or false?

Q3. In order to sort the data by multiple properties, you simply need to call OrderBy multiple times in LINQ that is using the method syntax, true or false?

Q4. How to add two filter conditions to a LINQ query with the query syntax?

1. Use multiple Where calls.

2. Use logical AND operator.

3. Issue two queries.

Q5. You want to add multiple new entities to DbSet. How can you accomplish this?

1. By calling the Add method and passing an instance of the class specified in the context property.

2. By calling AddRange and pass an enumerable of the target entity type.

3. By setting the state to Added using the context API on each new entity.

4. All of the above.

Q6. If you want to create a new entity with some child entities, also known as object graph, you must call Add on the parent and each child in order to persist the object graph, true or false?

Q7. If you set a state of an entity to modified, all columns in the corresponding table are updated when SaveChanges is called, true or false?

Q8. You need to call Add and then Remove on an entity in order to trigger a delete query to be issued when SaveChanges is called, true or false?

Q9. Which entity state does not result in a query against the database when SaveChanges is called?

1. Added.

2. Detached.

3. Deleted.

4. Modified.

Q10. Which property of DbSet gives you access to entities already loaded into the context from a database?

1. Memory.

2. Local.

3. Loaded.

Summary

In this chapter, you learned how to issue basic queries to retrieve entities from a database. LINQ was the driving force behind getting the data from the database through Entity Framework. There are two basic approaches to using LINQ: the method and query syntaxes. The query syntax is quite similar to SQL, whereas the method syntax may be more suitable for developers with more experience in C# or VB.NET than SQL. We saw how to use both approaches to filter data. We saw that the where clause can be used to combine multiple conditions using logical operators. The ordering of data can be done as well, using either the OrderBy and OrderByDescending methods or ascending or descending keywords. In order to support multiple orders by conditions, we can use the ThenBymethod. What we retrieved from the database were entities that were mapped to tables. We often do not need to think about this; simply assume that the Entity Framework persistence engine is taking care of these nitty-gritty details for us.

Lazy and eager loading are two basic concepts that are available to developers in order to access related entities from a single root entity. Using the Include method allows us to proactively load related data using a single call to the database. Foregoing this approach would result in many calls to the database, however, this is still a good thing in some cases, especially if you are not sure what related data you need. The bottom line is that it is important to recognize the drawbacks and benefits of eager versus lazy loading in order to write efficient and scalable code.

Once entities are retrieved via a query, Entity Framework starts tracking all the changes made to them. As a result, update queries only contain changed data and not all the properties from an entity. There are a few ways to update the data in the database. We could keep entities attached to the context, make changes, and call SaveChanges to persist the data. Alternatively, we can just set a state on an entity not attached to the context to modified and then save the changes. We saw that persisting an updated object graph requires us to set state on each entity in the graph. We did not have to do this in the case of new data. Marking the root object to be in a new state automatically puts all child entities in a new state as well, thus limiting the amount of work we have to do to insert new related entities. As an alternative to setting the state to new, we could simply add an entity to DbSet. Deleting the data was not much different. We could set the state to deleted, or attach an entity to context, then remove it from DbSet. Calling SaveChanges after that resulted in the delete query to be issued against the database. We will see many more advanced scenarios for querying data from a database in the next chapter. We will also look at several additional database modeling techniques.