WCF Multi-layer Services Development with Entity Framework Fourth Edition (2014)
Chapter 8. LINQ to Entities – Advanced Concepts and Features
In the previous chapter, we learned some basic concepts and features of LINQ to Entities such as querying and updating database tables and views and changing loading behaviors using the Include method.
In this chapter, we will learn some advanced features of LINQ to Entities such as stored procedure support, concurrency control, and transactional processing. After this chapter, we will rewrite the data access layer of our WCF service to utilize the LINQ to Entities technology.
In this chapter, we will cover the following topics:
· Calling a stored procedure
· Concurrency control
· Transaction support
Calling a stored procedure
Calling a stored procedure is different from querying a table or a view because a stored procedure can't be called without the proper preparation. A function import has to be added for the stored procedure and its result set has to be mapped. The modeling of a stored procedure is also different from modeling a table or a view. In the following sections, we will learn how to call a simple stored procedure, map the returned result of a stored procedure to an entity class, and create a new entity for the result set.
We will reuse the same application that we used in the previous chapter and add more methods to the program.
Mapping a stored procedure to a new entity class
First, we will try to call a simple stored procedure. In the sample database, there is a stored procedure called Ten Most Expensive Products. We will call this stored procedure to get the top ten most expensive products.
Adding a stored procedure to the model
Before we can call a stored procedure, we need to add it to the Entity Framework model. Perform the following steps:
1. Open the Northwind.edmx designer.
2. Right-click on an empty space on the designer surface and select Update Model from Database…:
3. From the Update Wizard window, on the Choose Your Database Objects and Settings page, make sure that the Add tab is selected. Then, expand the dbo node under Stored Procedures and Functions and check Ten Most Expensive Products.
4. Make sure that the Import selected stored procedures and functions into the entity model option is checked.
5. Click on the Finish button.
After you save the Northwind.edmx diagram, it will add the Ten_Most_Expensive_Products method to the NorthwindEntities class and add a new class, Ten_Most_Expensive_Products_Result, as the result data type of the stored procedure.
Querying a stored procedure
Now, from Program.cs, we can call this stored procedure as follows:
var tenProducts = from p in
NWEntities.Ten_Most_Expensive_Products()
select p;
foreach (var p in tenProducts)
{
Console.WriteLine("Product Name: {0}, Price: {1}",
p.TenMostExpensiveProducts, p.UnitPrice);
}
The SQL statement is pretty straightforward, as follows:
exec [dbo].[Ten Most Expensive Products]
The output will look like the one shown in the following screenshot:
Mapping a stored procedure to an existing entity class
In the preceding example, LINQ to Entities created a new type for the return result of the stored procedure. It actually just added the word, Result, after the stored procedure name to create the name of the return data type. If we know that the return result is a kind of entity, we can tell LINQ to Entities to use that specific entity as the return type, instead of creating a new type.
For example, let's create a stored procedure as follows:
Create PROCEDURE [dbo].[GetProduct]
(
@ProductID int
)
AS
SET NOCOUNT ON
Select * from Products where ProductID = @ProductID
You can create this stored procedure in Microsoft SQL Server Management Studio or by right-clicking on the Stored Procedures node in the Server Explorer of Visual Studio and selecting Data Connections | Northwind.dbo | Add New Stored Procedure from the context menu.
After the stored procedure has been created, follow these steps to add it to the entity data model and import a new function:
1. Open the Northwind.edmx designer.
2. Right-click on an empty space on the designer surface and select Update Model from Database….
3. From the Update Wizard window, on the Choose Your Database Objects and Settings page, make sure that the Add tab is selected. Then, expand the dbo node under Stored Procedures and Functions and check GetProduct.
4. This time, make sure that the Import selected stored procedures and functions into the entity model option is not checked.
5. Click on the Finish button.
As we didn't check the Import selected stored procedures and functions into the entity model option, the stored procedure has not been imported in the entity model for us. The reason is that we don't want to create a new result type for this stored procedure; instead, we will map this stored procedure to an existing entity, as described in the following steps:
1. On the designer surface, right-click on an empty space and select Add New from the context menu and then select Function Import….
2. In the Add Function Import window, type in GetProduct in the Function Import Name field and select GetProduct as the stored procedure name from the drop-down list.
3. Select Entities as Returns a Collection Of and choose Product as the entity from the drop-down list.
4. Click on the OK button.
5. Click on the Save button to save the model so that the new function for the new stored procedure can be created in the context class.
6. Now LINQ to Entities will use the Product class as the return type of this stored procedure.
To call this method, you can write a statement as follows:
var getProduct = NWEntities.GetProduct(1).FirstOrDefault();
The complete method for the stored procedure should be as follows:
static void TestStoredProcedure()
{
using(var NWEntities = new NorthwindEntities())
{
IEnumerable<Ten_Most_Expensive_Products_Result> tenProducts =
from p
in NWEntities.Ten_Most_Expensive_Products()
select p;
Console.WriteLine("Ten Most Expensive Products:");
foreach (Ten_Most_Expensive_Products_Result p in tenProducts)
{
Console.WriteLine("Product Name: {0}, Price; {1}",
p.TenMostExpensiveProducts, p.UnitPrice);
}
// map a stored procedure to an entity class
var getProduct = NWEntities.GetProduct(1).FirstOrDefault();
Console.WriteLine("\nProduct name for product 1: {0}",
getProduct.ProductName);
}
}
If you run the program, you should have an output, as shown in the following screenshot:
Interestingly, you can add another function for the same stored procedure but with a different function name (GetProduct1), and for the new function, you can check the Complex radio button to create a new type (GetProduct1_Result) for the result of the stored procedure, instead of using the Product class. LINQ to Entities will automatically create a new class for the return type.
The generated return type class, GetProduct1_Result, is almost identical to the Product class.
A big difference between the GetProduct and GetProduct1 methods is that the product you retrieved using GetProduct is managed by DbContext. Any changes you made to it will be committed back to the database if you call SaveChanges() later. However, the product you retrieved using GetProduct1 is not managed by DbContext and thus won't be committed back to the database if you call SaveChanges() later.
Handling simultaneous (concurrent) updates
If two users are updating the same record at the same time, a conflict will occur. There are normally three different ways to handle this conflict. The first method is to let the last update win, so no controlling mechanism is needed. The second one is to use a pessimistic lock, in which case, before updating a record, a user will first lock the record and then process and update the record. At the same time, all other users will have to wait for the lock to be released in order to start the updating process.
The third and most common mechanism in an enterprise product is the optimistic locking mechanism. A record is not locked for update when the data is retrieved, but when the application is ready to commit the changes, it will first check to see whether any other user has updated the same record since that data was retrieved. If nobody else has changed the same record, the update will be committed. If any other user has changed the same record, the update will fail and the user has to decide what to do with the conflict. Some possible options include overwriting the previous changes, discarding their own changes, or refreshing the record, and then reapplying (merging) the changes.
LINQ to Entities supports optimistic concurrency control in two ways. Next, we will learn both of them.
Detecting conflicts using a data column
The first way to detect conflicts is to use a regular data column. We can use the Concurrency Mode property for this purpose.
The Concurrency Mode property
During the designing stage, the Concurrency Mode property can be set for a data column to be one of the following two values: Fixed and None (default).
For a data column, there are three values to remember:
· The original value before update
· The current value to be updated to
· The database value when the change is submitted
For example, consider the case where you fetch a product record from the database with a UnitPrice of 25.00 and update it to 26.00. After you fetch this product, but before you submit your changes back to the database, somebody else has updated this product's price to 27.00. In this example, the original value of the price is 25.00, the current value to be updated to is 26.00, and the database value when the change is submitted is 27.00.
When the change is submitted to the database, the original and database values are compared. If they are different, a conflict is detected.
Now, let's look at these two settings. The first setting of the Concurrency Mode property is Fixed, which means that the column will be used for conflict detection. Whenever this column is being changed, its current and database values will be checked to see whether it has been updated by other users. If it has been, a conflict will be raised.
The second setting, None, means that the column will not be used for conflict checking. When a change is submitted to the database, the application will not check the status of this column. Therefore, even if this column has been updated by other users, it won't raise an error. This is the default setting of this property. So, by default, no column will be used for conflict detection.
Adding another entity data model
To test the concurrency of Entity Framework, we need to add a second entity data model to the project for the same database. The reason is that with Entity Framework, each database record has a unique entity key within the entity data model. All entity instances of the same database record will share the same entity key in the data model even if the entities are created within different object contexts.
To understand why this will stop us from testing the concurrency support of Entity Framework, let's first list the steps that we will perform to test the concurrency control.
By performing the following steps, we will test the concurrency control of Entity Framework:
1. Retrieve a product from the database.
2. Update its price in memory.
3. Retrieve the same product from the database.
4. Update its price in memory again.
5. Submit the changes made in step 4 to the database.
6. Submit the changes made in step 2 to the database.
With the concurrency control, the commit in step 6 should fail because the product price has been changed in the database after it has been retrieved. However, if we use the same entity data model, the product that is retrieved in step 1 will be cached. So, in step 3, the product object from the cache will be returned. Thus, the update in step 4 will be based on the update in step 2. The commit to the database in step 5 will actually contain both changes in step 2 and step 4. Therefore, the commit to the database in step 6 will not fail because it really has nothing to change in the database.
That's why we need to add another entity data model to the project, so we can have two independent entity objects pointing to the same record in the database. The following are the steps to add this new entity data model:
1. From the Solution Explorer, right-click on the TestLINQToEntities project and select Add | New Item.
2. Select Visual C# Items | ADO.NET Entity Data Model as the template and change the item name to Northwind1.edmx.
3. Select EF Designer from database as Model Contents.
4. Select the existing Northwind connection as Data Connection and keep the default entity name as NorthwindEntities1.
5. Choose the Products table as Database Objects and keep the default model namespace as NorthwindModel1.
6. Click on the Finish button to add the model to the project.
7. In the Northwind1.edmx designer, select the Product entity.
8. Change Entity Set Name to Product1s.
9. Change entity's Name to Product1.
10.Save the model.
Note
Steps 8 and 9 are essential because there is already a public class with the name of Product in our project. If you leave it unchanged and try to build/run the solution, you will find that your Northwind1.designer.cs file is empty because the designer can't generate it due to the name conflicts. In this case, you need to delete the new entity data model and re-add it to generate the new entity model designer file.
11.Open the original entity data model, NorthwindEntities.
12.Move the Product entity to a slightly different location on the designer.
13.Save the original entity data model, NorthwindEntities.
Note
When you save the new entity data model, your original Product class might disappear because Visual Studio thinks you are going to move the Product entity from the old model to this new model, even though we have renamed it to Product1. This is why we re-save the original model to regenerate the Product class.
Writing the test code
Now that we have a new entity data model added to the project, we can write the following code to test the concurrency control of Entity Framework:
using(var NWEntities = new NorthwindEntities())
{
// first user
Console.WriteLine("First User ...");
var product = (from p in NWEntities.Products
where p.ProductID == 2
select p).First();
Console.WriteLine("Original price: {0}", product.UnitPrice);
product.UnitPrice += 1.0m;
Console.WriteLine("Current price to update: {0}",
product.UnitPrice);
// process more products
// second user
Console.WriteLine("\nSecond User ...");
using(var NWEntities1 = new NorthwindEntities1())
{
var product1 = (from p in NWEntities1.Product1s
where p.ProductID == 2
select p).First();
Console.WriteLine("Original price: {0}",
product1.UnitPrice);
product1.UnitPrice += 2.0m;
Console.WriteLine("Current price to update: {0}",
product1.UnitPrice);
NWEntities1.SaveChanges();
Console.WriteLine("Price update submitted to database");
}
// first user is ready to submit changes
Console.WriteLine("\nFirst User ...");
NWEntities.SaveChanges();
Console.WriteLine("Price update submitted to database");
}
In this example, we will first retrieve product 2 and increase its price by 1.0. Then, we will simulate another user to retrieve the same product and increase its price by 2.0. The second user will submit the changes first with no error. When the first user tries to submit the changes and the price has already been changed by the second user, the update will still be saved to the database without any problem. This is because, by default, the concurrency control is not turned on, so the later change will always overwrite the previous change.
Testing the conflicts
Now run the program. You will get an output, as shown in the following screenshot:
From this screenshot, we know both updates have been submitted to the database without any problem. If you query the database, you will find that the price of product 2 is now 20, not 21, because the first user's update overwrote the second user's update.
Turning on concurrency verification
Now, open Northwind.edmx, click on the UnitPrice member of the Product entity, and change its Concurrency Mode property to Fixed, as shown in the following screenshot:
Make sure that you open the Northwind.edmx model, not the new Northwind1.edmx model, because the second user within the new Northwind1.edmx model will submit to the database first, meaning that there will be no conflict for this update.
Run the program again. You will see an exception this time because the price column is now used for conflict detection. If you query the database, you will find the price for product 2 is now 22 because it hasn't been overwritten by the first user's update, which would have updated its price to 21 if it hadn't failed due to the concurrent conflict.
Note
If you look at the autogenerated SQL statement for this update through SQL Profiler, you will find that another where clause is added to the SQL statement and that Entity Framework just watches the number of records affected.
The output is as shown in the following screenshot:
To resolve this conflict, we can add an exception handling block around the first user's update.
First, you need to add a using statement to the Program.cs file for the concurrency exception type:
using System.Data.Entity.Infrastructure;
Then, change the saving part as shown in the following code snippet:
// first user is ready to submit changes
Console.WriteLine("\nFirst User ...");
try
{
NWEntities.SaveChanges();
Console.WriteLine("Price update submitted to database");
}
catch (DbUpdateConcurrencyException e)
{
Console.WriteLine("Conflicts detected. Refreshing ...");
var entry = e.Entries.Single();
entry.OriginalValues.SetValues(entry.GetDatabaseValues());
NWEntities.SaveChanges();
Console.WriteLine("Price update submitted to database after refresh");
}
Here, we are just forcing to let the first user win, but in a real-world situation, you might want to give users a warning; let them refresh and then redo the changes, or merge the changes in your code.
The complete method should be as follows:
static void TestSimultaneousChanges()
{
using(var NWEntities = new NorthwindEntities())
{
// first user
Console.WriteLine("First User ...");
var product = (from p in NWEntities.Products
where p.ProductID == 2
select p).First();
Console.WriteLine("Original price: {0}", product.UnitPrice);
product.UnitPrice += 1.0m;
Console.WriteLine("Current price to update: {0}",
product.UnitPrice);
// process more products
// second user
Console.WriteLine("\nSecond User ...");
using(var NWEntities1 = new NorthwindEntities1())
{
var product1 = (from p in NWEntities1.Product1s
where p.ProductID == 2
select p).First();
Console.WriteLine("Original price: {0}", product1.UnitPrice);
product1.UnitPrice += 2.0m;
Console.WriteLine("Current price to update: {0}",
product1.UnitPrice);
NWEntities1.SaveChanges();
Console.WriteLine("Price update submitted to database");
}
// first user is ready to submit changes
Console.WriteLine("\nFirst User ...");
try
{
NWEntities.SaveChanges();
Console.WriteLine("Price update submitted to database");
}
catch (DbUpdateConcurrencyException e)
{
Console.WriteLine("Conflicts detected. Refreshing ...");
var entry = e.Entries.Single();
entry.OriginalValues.SetValues(entry.GetDatabaseValues());
NWEntities.SaveChanges();
Console.WriteLine("Price update submitted to database after refresh");
}
}
}
Run the program now and you will get an output, as shown in the following screenshot:
From this output, we know that the first user's update failed due to the concurrency conflict. However, after the refresh, it won the conflict; so, the final price in the database should be 23, which means that the second user's update has been overwritten by the first user's update.
Note
With this mechanism, only the involved column is protected for concurrent updates. All other columns can still be updated by multiple users or processes without causing conflicts. For example, if you change the previous code to update the UnitsInStock property, you won't get a concurrency exception because the Concurrency Mode property of UnitsInStock is not set to Fixed and the concurrency setting of UnitPrice doesn't check the UnitsInStock column in the database.
Detecting conflicts using a version column
The second and more efficient way to provide conflict control is to use a version column in a table. If you add a column RowVersion of the timestamp type in a table and when you add this table to the entity model, the RowVersion column will be marked as a concurrency control version property.
Version numbers are updated every time the associated row is updated. Before the update, if there is a column of the timestamp type, LINQ to Entities will first check this column to make sure that the record has not been updated by any of the other users. This column will also be synchronized at the same time as the data row is updated. The new values are visible after SaveChanges finishes.
Adding a version column
Now, let's try this in the Products table. First, we need to add a new column called RowVersion, which is of the timestamp type. You can add it within SQL Server Management Studio, as shown in the following screenshot:
Modeling the Products table with a version column
After saving the changes, we need to refresh our data model to see these changes in the data model. Follow these steps to refresh the model:
1. From Visual Studio, open the Northwind.edmx entity designer, right-click on an empty space, and select Update Model from Database…. Click on the Refresh tab and you will see Products in the refresh list.
2. Click on the Finish button and save the model.
Now, a new property, RowVersion, has been added to the Northwind.edmx data model. However, its Concurrency Mode property is set to None now, so you need to change it to Fixed. Note that its StoreGeneratedPattern value is set to Computed, which is to make sure this property will be refreshed every time after an update. The following screenshot displays the Concurrency Mode and StoreGeneratedPattern properties of the new RowVersion entity property:
Writing the test code
We can write similar code to test this new version-controlling mechanism:
static void TestVersionControl()
{
using(var NWEntities = new NorthwindEntities())
{
// first user
Console.WriteLine("First User ...");
var product = (from p in NWEntities.Products
where p.ProductID == 3
select p).First();
Console.WriteLine("Original unit in stock: {0}", product.UnitsInStock);
product.UnitsInStock += 1;
Console.WriteLine("Current unit in stock to update: {0}",
product.UnitsInStock);
// process more products
// second user
Console.WriteLine("\nSecond User ...");
using(var NWEntities1 = new NorthwindEntities1())
{
var product1 = (from p in NWEntities1.Product1s
where p.ProductID == 3
select p).First();
Console.WriteLine("Original unit in stock: {0}",
product1.UnitsInStock);
product1.UnitsInStock += 2;
Console.WriteLine("Current unit in stock to update: {0}",
product1.UnitsInStock);
NWEntities1.SaveChanges();
Console.WriteLine("update submitted to database");
}
// first user is ready to submit changes
Console.WriteLine("\nFirst User ...");
try
{
NWEntities.SaveChanges();
}
catch (DbUpdateConcurrencyException e)
{
Console.WriteLine("Conflicts detected. Refreshing ...");
var entry = e.Entries.Single();
entry.OriginalValues.SetValues(entry.GetDatabaseValues());
NWEntities.SaveChanges();
Console.WriteLine("update submitted to database after refresh");
}
}
}
Testing the conflicts
This time, we tried to update UnitsInStock for product 3. From the output, we can see a conflict was detected again when the first user submitted changes to the database, but this time, the versioning is controlled by a version column, not by the unit in the stock column itself:
Transaction support
In the previous section, we learned that simultaneous changes by different users can be handled by using a version column or the Concurrency Mode property. Sometimes, the same user might have made several changes and some of the changes might not succeed. In this case, we need a way to control the behavior of the overall update result. This is handled by transaction support.
LINQ to Entities uses the same transaction mechanism as ADO.NET, that is, it uses implicit or explicit transactions.
Implicit transactions
By default, LINQ to Entities uses an implicit transaction for each SaveChanges call. All updates between two SaveChanges calls are wrapped within one transaction.
For example, in the following code, we are trying to update two products. The second update will fail due to a constraint. However, as the first update is in a separate transaction, it has been saved to the database and it will stay in the database:
static void TestImplicitTransaction()
{
using(var NWEntities = new NorthwindEntities())
{
var prod1 = (from p in NWEntities.Products
where p.ProductID == 4
select p).First();
var prod2 = (from p in NWEntities.Products
where p.ProductID == 5
select p).First();
prod1.UnitPrice += 1;
// update will be saved to database
NWEntities.SaveChanges();
Console.WriteLine("First update saved to database");
prod2.UnitPrice = -5;
// update will fail because UnitPrice can't be < 0
// but previous update stays in database
try
{
NWEntities.SaveChanges();
Console.WriteLine("Second update saved to database");
}
catch (Exception)
{
Console.WriteLine("Second update not saved to database");
}
}
}
The output will look as shown in the following screenshot:
Explicit transactions
In addition to implicit transactions, you can also define a transaction scope to explicitly control the update behavior. All updates within a transaction scope will be within a single transaction. Thus, they will all either succeed or fail.
For example, in the following code snippet, we first start a transaction scope. Then, within this transaction scope, we update one product and submit the change to the database. However, at this point, the update has not really been committed because the transaction scope is still not closed. We then try to update another product, which fails due to the same constraint as in the previous example. The final result is that neither of these two products are updated in the database:
static void TestExplicitTransaction()
{
using(var NWEntities = new NorthwindEntities())
{
using (var ts = new TransactionScope())
{
try
{
var prod1 = (from p in NWEntities.Products
where p.ProductID == 4
select p).First();
prod1.UnitPrice += 1;
NWEntities.SaveChanges();
Console.WriteLine("First update saved to database, but not committed.");
// now let's try to update another product
var prod2 = (from p in NWEntities.Products
where p.ProductID == 5
select p).First();
// update will fail because UnitPrice can't be < 0
prod2.UnitPrice = -5;
NWEntities.SaveChanges();
ts.Complete();
}
catch (Exception e)
{
// both updates will fail because they are within one // transaction
Console.WriteLine("Exception caught. Rollback the first update.");
}
}
}
}
Note that TransactionScope is in the .NET assembly System.Transactions. Therefore, first you need to add a reference to System.Transactions and then add the following using statement to the Program.cs file:
using System.Transactions;
Note
As best practice, you should always call Complete() within a transaction scope to commit the transaction, even if there are only select statements within the scope. If you want to roll back the transaction, there is no specific rollback method, but rather you just don't call Complete() and let the scope get disposed.
The output of the program is shown in the following screenshot:
If you start the program in the debugging mode, after the first SaveChanges is called, you can go to SQL Server Management Studio and query the price of product 4 by using the following statement:
select UnitPrice from products (nolock) where productID = 4
The nolock hint is equivalent to READUNCOMMITTED and it is used to retrieve uncommitted data. With this hint, you can see that its price has been increased by the first change. Then, after the second SaveChanges is called, an exception is thrown and the transaction scope is closed. At this point, if you run the preceding query again, you will see that the price of product 4 is rolled back to its original value.
Note
After the first call to the SaveChanges method, you shouldn't use the following statement to query the price value of the product:
select UnitPrice from products where productID = 4
If you do so, you will not get back a result. Instead, you will be waiting forever as it is waiting for the transaction to be committed.
This also brings up a big trade-off of using explicit/distributed transactions: deadlocks. We will cover more about distributed transactions in Chapter 10, Distributed Transaction Support of WCF.
Summary
In this chapter, we learned advanced features of LINQ to Entities, how to use stored procedures, transaction support, and concurrency control for LINQ toEntities. At this point, you should have a good understanding of LINQ to Entities.
In the next chapter, we will apply these skills to the data access layer of our WCF service to connect to databases securely and reliably with LINQ to Entities.