Introduction
In my first three articles on CodeProject.com, I have explained the fundamentals of Windows Communication Foundation (WCF), including:
From last month, I have started to write a few articles to explain LINQ, LINQ to SQL, Entity Framework, and LINQ to Entities. Followings are those articles I wrote or plan to write for LINQ, LINQ to SQL, and LINQ to Entities:
After finishing these five articles, I will come back to write some more articles on WCF from my real work experience, which will be definitely helpful to your real world work, if you are using WCF right now.
Q & A
Before going any further, let me answer a question raised by a reader of my previous article: what is the future of LINQ to SQL? Is it dead?
The answer is, yes, it is dead. Microsoft has officially announced that Entity Framework will be the preferred ORM from Microsoft. Actually, LINQ to SQL might be conisdred just an intermediate product for the C# team to prove the success of the then new language C#. But since it has been there for a while, and it is so easy to use, and a few people are already using it in production, I think it is still worth to learn or at least to understand it.
Overview
Now let's come back to LINQ to SQL. In the previous article, we learned some basic concepts and features of LINQ to SQL, such as querying and updating databases with tables and views, and changing loading behaviors with load options.
In this article, we will learn some advanced features of LINQ to SQL, such as Stored Procedure support, concurrency control, and transactional processing.
We will cover the following topics in this article:
- Calling a Stored Procedure
- Compiled query
- Direct SQL
- Dynamic query
- Inheritance support
- Concurrency control
- Transaction support
- Entity class validation
- Debugging LINQ to SQL programs
Calling a Stored Procedure
Calling a Stored Procedure is different from a table or a view, because a Stored Procedure can have input parameters, output parameters, and it can return multiple result-sets. It can also return different result-sets dynamically, making it even harder to interpret the results. The modeling of a Stored Procedure is also different from a table or view. In the following sections, we will see how to call a simple Stored Procedure, how to map the return result of a Stored Procedure to an entity class, and how to handle output parameters, return code, and multiple result-sets.
We will reuse the same application that we used in the previous article, and add more testing methods to the program. If you haven't read my previous article (LINQ to SQL: Basic Concepts and Features), please do so now, or you can just go to download the source code.
Calling a Simple Stored Procedure
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.
- Before we can call this Stored Procedure, we need to model it.
- Open the Northwind.dbml designer.
- From Server Explorer, expand the node Stored Procedures.
- Drag the Stored Procedure Ten Most Expensive Products to the right hand panel of the Northwind.dbml design surface.
This will add the method Ten_Most_Expensive_Products to the NorthwindDataContext class, and add a new class Ten_Most_Expensive_ProductsResult as the result data type of the Stored Procedure.
Now, from Program.cs, we can call this Stored Procedure like this:
var tenProducts = from p in db.Ten_Most_Expensive_Products() select p;
foreach (var p in tenProducts)
{
Console.WriteLine("Product Name: {0}, Price; {1}",
p.TenMostExpensiveProducts, p.UnitPrice);
}
Because we know exactly the return result of the Stored Procedure, we can also replace the var data type with the specific return type, as in the following code:
IEnumerable<Ten_Most_Expensive_ProductsResult>
tenProducts = from p in db.Ten_Most_Expensive_Products() select p;
foreach (Ten_Most_Expensive_ProductsResult p in tenProducts)
{
Console.WriteLine("Product Name: {0}, Price; {1}",
p.TenMostExpensiveProducts, p.UnitPrice);
}
The output will be like this diagram:
Mapping a Stored Procedure to an Entity Class
In the above example, LINQ to SQL creates a new type for the return result of the Stored Procedure. It actually just added the word “Result” after the Stored Procedure name to name the return data type. If we know the return result is a kind of entity, we can tell LINQ to SQL to use that entity as the return type instead of creating a new type.
For example, let’s create a Stored Procedure like this:
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 2008 and selecting Add New Stored Procedure from the context menu.
After the Stored Procedure has been created, drag and drop it onto the Product class on the Northwind.dbml design surface. Now LINQ to SQL will use the Product class as the return type of this Stored Procedure. The method for this Stored Procedure will be like this:
[Function(Name="dbo.GetProduct")]
public ISingleResult<Product> GetProduct([Parameter(Name="ProductID",
DbType="Int")] System.Nullable<int> productID)
{
IExecuteResult result = this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())), productID);
return ((ISingleResult<Product>)(result.ReturnValue));
}
From the signature of the method, we know the return type is Product.
Interestingly, if you drag and drop the same Stored Procedure to the right panel of the Northwind.dbml design surface, instead of the Product class, LINQ to SQL will automatically create a new class for the return type. The new method might be like this:
[Function(Name="dbo.GetProduct")]
public ISingleResult<GetProductResult> GetProduct1(
[Parameter(Name="ProductID", DbType="Int")]
System.Nullable<int> productID)
{
IExecuteResult result = this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())), productID);
return ((ISingleResult<GetProductResult>)(result.ReturnValue));
}
And the generated return type class GetProductResult is almost identical to the Product class, except there are no event handling methods.
Another difference between the method GetProduct and GetProduct1 is, the product you retrieve using GetProduct is within the DataContext, so any changes you make to it will be committed back to the database if you call db.SubmitChanges() later, while the product you retrieve using GetProduct1 is not within the DataContext, and thus won’t be committed back to the database if you call db.SubmitChanges() later.
Also, when a Stored Procedure is dropped to an entity class, LINQ to SQL will first check the return result of the Stored Procedure to make sure it is compatible with the target class. If not, you will get a warning message and the Stored Procedure won’t be mapped on the model. For example, if you drag and drop the Stored Procedure Ten Most Expensive Products to the Product class, you will see a dialog box like this:
Handling Output Parameter, Return Code, Multiple Shapes of a Single Result-set, and Multiple Result-sets
Now that we have a basic understanding of LINQ to SQL Stored Procedure processing, we will create a fairly complex Stored Procedure with an input parameter, output parameter, return code, multiple shapes of a single result-set, and multiple result-sets.
Creating a Complex Stored Procedure
Before we explain the LINQ to SQL comprehensive Stored Procedure support, we will need to create a complex Stored Procedure. We will create a Stored Procedure called GetCategoryDetails. The stored procedure will have an input parameter CategoryID to specify which category it is for, and an output parameter AveProductPrice to give the average price of all products in that category.
The first result-set of this Stored Procedure will give some information of the category depending on the value of another input parameter FullOrPartial. If FullOrPartial is true (1), this result-set will contain all columns of the Categories table for the requested category. Otherwise, it will contain only the CategoryID and CategoryName of the category.
The second result-set will contain all products for that category.
If the input parameter is not a valid category ID, it returns an error code of 10001, and stops; otherwise it returns 0 at the end of the Stored Procedure to indicate success.
This is the SQL to create this Stored Procedure:
CREATE PROCEDURE [dbo].[GetCategoryDetails]
@CategoryID int,
@FullOrPartial bit,
@AveProductPrice money OUTPUT
AS
SET NOCOUNT ON
if not exists (select 1
from categories
where categoryID = @categoryID)
return 10001
if @FullOrPartial = 1
select * from Categories
where categoryID = @categoryID
else
select categoryID, categoryName from Categories
where categoryID = @categoryID
select * from products
where categoryID = @categoryID
select @AveProductPrice = avg(UnitPrice)
from products
where categoryID = @CategoryID
return 0
Modeling the Stored Procedure
In order to call this complex Stored Procedure, we first need to add it into the Northwind.dbml model. Just drag and drop it from Server Explorer to the right hand panel of the Northwind.dbml design surface. If you have created it in SQL Management Studio and can’t see it from Server Explorer, try to refresh your Server Explorer.
the LINQ to SQL designer will create following method in the class NorthwindDataContext within the file Northwind.designer.cs:
[Function(Name="dbo.GetCategoryDetails")]
public ISingleResult<GetCategoryDetailsResult>
GetCategoryDetails([Parameter(Name="CategoryID",
DbType="Int")] System.Nullable<int> categoryID,
[Parameter(Name="FullOrPartial", DbType="Bit")]
System.Nullable<bool> fullOrPartial,
[Parameter(Name="AveProductPrice", DbType="Money")]
ref System.Nullable<decimal> aveProductPrice)
{
IExecuteResult result = this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())),
categoryID, fullOrPartial, aveProductPrice);
aveProductPrice = ((System.Nullable<decimal>)(result.GetParameterValue(2)));
return ((ISingleResult<GetCategoryDetailsResult>)(result.ReturnValue));
}
Note, the variable aveProductPrice is passed to the method call ExecuteMethodCall, but its actual value doesn’t come back after the call. The output value has to be retrieved using result.GetParameterValue.
And this class is also added for the return result (this is really the first result-set in the Stored Procedure):
public partial class GetCategoryDetailsResult
However, this is not what we want. The method GetCategoryDetails only returns one result-set, instead of two. We have to customize it for our needs.
Customizing the DataContext Class for the Stored Procedure
In the previous sections, we modeled the Stored Procedure with the LINQ to SQL designer, but the retuning result was not correct. In this section, we will customize it.
- Extend the class
NorthwindDataContext by adding a new class file called NorthwindDataContext.cs.
- Inside this new class file NorthwindDataContext.cs, add the following
using statements:
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Reflection;
Add this class inside the file NorthwindDataContext.cs for one of the return results:
public class PartialCategory
{
public int CategoryID;
public string CategoryName;
}
This class is in parallel to the class NorthwindDataContext. Next, we will use this class to define a new method.
Change the class definition to the following code (note: it should be changed to a partial class):
public partial class NorthwindDataContext
{
[Function(Name = "dbo.GetCategoryDetails")]
[ResultType(typeof(PartialCategory))]
[ResultType(typeof(Category))]
[ResultType(typeof(Product))]
public IMultipleResults GetWholeOrPartialCategoryDetails(
[Parameter(Name="CategoryID", DbType="Int")]
System.Nullable<int> categoryID,
[Parameter(Name="FullOrPartial", DbType="Bit")]
System.Nullable<bool> fullOrPartial,
[Parameter(Name="AveProductPrice", DbType="Money")]
ref System.Nullable<decimal> aveProductPrice)
{
IExecuteResult result = this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())),
categoryID, fullOrPartial, aveProductPrice);
aveProductPrice = ((System.Nullable<decimal>)(result.GetParameterValue(2)));
return ((IMultipleResults)(result.ReturnValue));
}
}
As you can see, we defined a method GetWholeOrPartialCategoryDetails to map the results of the Stored Procedure to different types.
We can also modify the generated method inside the file Northwind.designer.cs to meet our needs, but it is recommended not doing so, because if you modify it, and later on it is regenerated, you will lose all your changes.
Testing the Stored Procedure
Now inside the file program.cs, we can add this test method:
static void TestComplexStoredProcedure(int categoryID, bool wholeOrPartial)
{
decimal? avePrice = 0;
IMultipleResults result =
db.GetWholeOrPartialCategoryDetails(categoryID, wholeOrPartial, ref avePrice);
int returnCode = (int)result.ReturnValue;
if (returnCode == 0)
{
if (wholeOrPartial == true)
{
Category wholeCategory = result.GetResult<Category>().FirstOrDefault();
Console.WriteLine("Category name: {0}", wholeCategory.CategoryName);
Console.WriteLine("Category description: {0}", wholeCategory.Description);
}
else
{
PartialCategory partialCategory =
result.GetResult<PartialCategory>().FirstOrDefault();
Console.WriteLine("Category name: {0}", partialCategory.CategoryName);
}
Console.WriteLine("Average product price: {0}", avePrice);
IEnumerable<Product> products = result.GetResult<Product>();
Console.WriteLine("Total products in category: {0}", products.Count());
}
else
{
Console.WriteLine("No category is retrieved, return code : {0}", returnCode);
}
}
And inside the Main method, we call the above method thrice, like this:
TestComplexStoredProcedure (2, true);
TestComplexStoredProcedure (6, false);
TestComplexStoredProcedure (999, true);
The first call will return the full category info for category 2, including the category ID, name, description, and picture. The second call will return only partial information for category 6, including category ID and name. In both cases, it will return the products in the category and the average product price in that category. The third call will print an error message because there is no category with ID 999.
The output is like this:
Compiled Query
It is common in many applications to execute structurally similar queries many times. In such cases, it is possible to increase performance by compiling the query once and executing it several times in the application with different parameters. This result is obtained in LINQ to SQL by using the CompiledQuery class.
The following code shows how to define a compiled query:
Func<NorthwindDataContext, string, IQueryable<Product>> fn =
CompiledQuery.Compile((NorthwindDataContext db2, string category) =>
from p in db2.Products
where p.Category.CategoryName == category
select p);
var products1 = fn(db, "Beverages");
Console.WriteLine("Total products in category Beverages: {0}", products1.Count());
var products2 = fn(db, "Seafood");
Console.WriteLine("Total products in category Seafood: {0}", products2.Count());
As you can see, a compiled query is actually a function. The function contains a compiled LINQ query expression, and can be called just like a regular function.
Direct SQL
LINQ to SQL is part of the ADO.NET family of technologies. It is based on services provided by the ADO.NET provider model, so it is possible to mix LINQ to SQL code with existing ADO.NET applications. For example, you can create a DataContext using an existing ADO.NET connection.
In some cases, you might find that the query or submit changes facility of the DataContext is insufficient for the specialized task you may want to perform. In these circumstances, it is possible to use the DataContext to issue raw SQL commands directly to the database.
The ExecuteQuery() method lets you execute a raw SQL query and converts the result of your query directly into objects.
The ExecuteCommand() method lets you execute SQL commands directly on the database.
For example, the following code will retrieve all discontinued products, and update the price for one product:
var products = db.ExecuteQuery<Product>(
"SELECT ProductID, ProductName " +
"FROM Products " +
"WHERE Discontinued = 0 " +
"ORDER BY ProductName;"
);
Console.WriteLine("Total discontinued products :{0}", products.Count());
int rowCount = db.ExecuteCommand(
" update products "
+ "set UnitPrice=UnitPrice+1 "
+ "where productID=35");
if (rowCount < 1)
Console.WriteLine("No product is updated");
else
Console.WriteLine("Product price is updated");
Dynamic Query
Besides using LINQ syntax, we can also build queries at runtime dynamically using Expressions. For example, the following code will create two method expressions, one for the where clause and one for the order by clause.
ParameterExpression param = Expression.Parameter(typeof(Product), "p");
Expression left =
Expression.Property(param, typeof(Product).GetProperty("UnitPrice"));
Expression right =
Expression.Constant((decimal)100.00, typeof(System.Nullable<decimal>));
Expression filter = Expression.GreaterThanOrEqual(left, right);
Expression pred = Expression.Lambda(filter, param);
IQueryable products = db.Products;
Expression expr = Expression.Call(typeof(Queryable), "Where",
new Type[] { typeof(Product) }, Expression.Constant(products), pred);
expr = Expression.Call(typeof(Queryable), "OrderBy",
new Type[] { typeof(Product), typeof(string) }, expr,
Expression.Lambda(Expression.Property(param, "ProductName"), param));
IQueryable<Product> query = db.Products.AsQueryable().Provider.CreateQuery<Product>(expr);
foreach (var p in query)
Console.WriteLine("Product name: {0}", p.ProductName);
To build the first expression, we first create a left expression and a right expression, then use them to create a filter expression. The predicate expression is then created based on this filter expression.
The second expression takes the first expression as an argument, so it expands the first expression to include an order by expression.
The sentence with the method CreateQuery is the one that creates the query dynamically, according to the expressions we have created before this sentence. And of course, the query won’t get executed until the foreach statement is executed.
Before running this program, you need to add this using statement in the beginning:
using System.Linq.Expressions;
The output of the above code is like this:
Inheritance
The LINQ to SQL Object Relational Designer (O/R Designer) supports the concept of single-table inheritance as it is often implemented in relational systems. In the following sections, we will explore what single-table inheritance is, and how to use it with LINQ.
LINQ to SQL Single-Table Inheritance
In single-table inheritance, there is a single database table that contains fields for both parent information and child information. With relational data, a discriminator column contains the value that determines which class any given record belongs to.
For example, consider a Persons table that contains everyone employed by a company. Some people are employees and some people are managers. The Persons table contains a column named EmployeeType that has a value of 1 for managers and a value of 2 for employees; this is the discriminator column.
In this scenario, you can create a subclass of employees and populate the class with only records that have an EmployeeType value of 2. You can also remove columns that do not apply from each of the classes.
In our Northwind database, the Products table contains all products from 8 categories. Suppose all products share some common properties, and each category also has some unique properties by itself, we can then define a BaseProduct entity class for all common properties of the products, and define a unique child entity class for each category.
We assume all products have the following properties: ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder.
To simplify the example, we will define only two child entity classes in this example, one for beverage products and another for sea food products. We assume beverage products have one more property Discontinued and sea food products have one more property ReorderLevel.
Modeling the BaseProduct and Beverage Classes
We will first model these classes with the LINQ to SQL designer.
- Open Server Explorer, and drag the Products table to the Northwind.dbml design surface. Change the entity class name from
Product1 to BaseProduct, and delete its member properties Discontinued and ReorderLevel.
- Drag another instance of the Products table from Server Explorer to the Northwind.dbml design surface, change its name from
Product1 to Beverage. Click the association line between Category and Beverage and delete it. Then delete all of its properties except Discontinued.
- Now we need to set up the inheritance relationship between the
BaseProduct and Beverage classes. Open the Object Relational Designer Toolbox, and click the shape Inheritance.
- While the cursor is being changed, click the
Beverage class, then click the BaseProduct class to connect them together.
- Click the newly created association line between the
BaseProduct and Beverage classes, and set the required properties.
The properties of the association between BaseProduct and Beverage should be like this:
Modeling the Seafood Class
Next we need to model the Seafood class. This class will inherit from the BaseProduct class, with an extra property ReorderLevel.
- Drag another instance of the Products table from the Server Explorer to the Northwind.dbml design surface, change its name from
Product1 to Seafood.
- Click the association line between
Category and Seafood and delete it. Then delete all of its properties except ReorderLevel.
- Do the same thing as we did for the
Beverage class to set up the inheritance relationship between BaseProduct and Seafood class, except set the Derived Class Discriminator Value to 8. Actually, this is the only inheritance value you need to set for this class because all the other three properties (Inheritance Default, Base Class Discriminator Value, and Discriminator) have been set previously.
The finished model should be like this diagram:
The Generated Classes With Inheritance
Save the model and open the Northwind.designer.cs file, you will see three classes were added to the DataContext. The first class is the BaseProduct class, which has this signature:
[Table(Name="dbo.Products")]
[InheritanceMapping(Code="0", Type=typeof(BaseProduct), IsDefault=true)]
[InheritanceMapping(Code="1", Type=typeof(Beverage))]
[InheritanceMapping(Code="8", Type=typeof(Seafood))]
public partial class BaseProduct : INotifyPropertyChanging, INotifyPropertyChanged
Its class body is almost identical to the Product class, except without the properties ReorderLevel and Discontinued. The three inheritance mappings attributes are generated from the inheritance properties we set in the model.
So why don’t we just use the existing Product class as the base class?
Actually, it is OK, and realistically should be the preferred way to use the Product class as the base class. However, we have used the Product class in all our previous examples and if we delete two of its properties, some of those examples might not work. So we decided to create a new class from the same table for this example only.
The other two classes are for the derived classes, each has only one property:
public partial class Beverage : BaseProduct
public partial class Seafood : BaseProduct
Testing Inheritance
Now we can write a query to show the inheritance between BaseProduct and those two derived classes.
First, we can retrieve all the beverage products using the is operator, like this:
var beverages1 = from b in db.BaseProducts
where b is Beverage
select b;
Or we can use the OfType operator to retrieve the same products:
var beverages2 = from b in db.BaseProducts.OfType<Beverage>()
select b;
Console.WriteLine("Total number of beverage products: {0}", beverages1.Count());
Console.WriteLine("Total number of beverage products: {0}", beverages2.Count());
Run the program and you will see both queries return 12.
We can also use the as operator to search all products for beverages:
var beverages3 = from b in db.BaseProducts
select b as Beverage;
foreach (var b in beverages3)
{
if (b != null)
{
Console.WriteLine("Found a beverage: {0}, it is {1} discontinued",
b.ProductName, (b.Discontinued?"":"not"));
}
}
In the above code, if a product is not a beverage, it will return null.
In all the above three queries, Discontinued is a property of the returning item, which means it is of type Beverage. Also, all of the BaseProduct properties are available, because the returning item’s data type is a child of the BaseProduct type.
Similarly, we can retrieve all sea food products and use the property ReorderLevel, like this:
var seafood = from s in db.BaseProducts.OfType<Seafood>()
select s;
foreach (var s in seafood)
Console.WriteLine("Product name: {0} Reorder level: {1}",
s.ProductName, s.ReorderLevel);
The output is like this:
Handling Simultaneous (Concurrent) Updates
If two users are updating the same record at the same time, some conflicts will occur. There are normally three different ways to handle conflicts. 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, then process and update the record. At the same time, all other users have to wait for the lock to be released in order to start the updating process.
The third and the most used mechanism in an enterprise product is optimistic locking. A user doesn’t lock a record for update, but when it is ready to commit the changes, it will first check if any other user has updated the same record. If nobody else has ever 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 conflicts. Some possible options include overwriting the previous changes, discarding its own changes, and refreshing the record then reapplying (merging) the changes.
LINQ to SQL supports optimistic concurrency control in two ways. Next, we will explain both of them.
Detecting Conflicts Using the Update Check Property
The first way is using the Update Check property. At design time, this property can be set for a column to be one of these three values:
For a column, there are three values to remember: the original value before update, the current value to be updated, and the database value when the change is submitted. For example, if you fetch a product record from a database with a Unit Price of 25.00, and you update it to 26.00. After you fetch this product but before you submit your changes back to the database, somebody else may have updated this product’s price to 27.00. In this example, the original value of the price is 25.00, the current value to update 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 value and the database value will be compared. If they are different, a conflict is detected.
Now let’s look at those three settings. The first setting of the property Update Check is Always, which means the column will always be used for conflict detecting. Whenever a record is being changed, this column will always be checked to see if it has been updated by other users. If it has, it raises a conflict. This is the default setting of this property, so by default, all columns will be used for conflict detecting.
The second setting of Never means this column will never be used for conflict detecting. When a change is submitted to the database, it will not check the status of this column, so even if this column has been updated by other users, it won’t raise any error.
The third setting, WhenChanged, is in between. It will be used for conflict detecting, but only if the current process has changed its value. If the current process hasn’t changed its value, it won’t care if some other processes have updated its value.
Writing the Test Code
To show how to use these three settings, we can write the following code:
Console.WriteLine("First User ...");
Product product = (from p in db.Products
where p.ProductID == 2
select p).First();
Console.WriteLine("Original price: {0}", product.UnitPrice);
product.UnitPrice = 26;
Console.WriteLine("Current price to update: {0}", product.UnitPrice);
Console.WriteLine("Second User ...");
NorthwindDataContext db2 = new NorthwindDataContext();
Product product2 = (from p in db2.Products
where p.ProductID == 2
select p).First();
Console.WriteLine("Original price: {0}", product2.UnitPrice);
product2.UnitPrice = 26;
Console.WriteLine("Current price to update: {0}", product2.UnitPrice);
db2.SubmitChanges();
db2.Dispose();
Console.WriteLine("First User ...");
try
{
db.SubmitChanges();
}
catch (ChangeConflictException)
{
Console.WriteLine("Conflict is detected");
foreach (ObjectChangeConflict occ in db.ChangeConflicts)
{
MetaTable metatable = db.Mapping.GetTable(occ.Object.GetType());
Product entityInConflict = (Product)occ.Object;
Console.WriteLine("Table name: {0}", metatable.TableName);
Console.Write("Product ID: ");
Console.WriteLine(entityInConflict.ProductID);
foreach (MemberChangeConflict mcc in occ.MemberConflicts)
{
object currVal = mcc.CurrentValue;
object origVal = mcc.OriginalValue;
object databaseVal = mcc.DatabaseValue;
MemberInfo mi = mcc.Member;
Console.WriteLine("Member: {0}", mi.Name);
Console.WriteLine("current value: {0}", currVal);
Console.WriteLine("original value: {0}", origVal);
Console.WriteLine("database value: {0}", databaseVal);
}
}
}
In this example, we first retrieved product 2 and update its price from 19.00 to 26.00. Then we simulate another user to retrieve the same product, and also update its price to 26.00. The second user submits the changes first with no error, but when the first user tries to submit the changes, a conflict is detected because at that time the original value 19.00 is different from the database value 26.00. We can also use ChangeConflicts of the DataContext to get the list of conflicts.
Testing the Conflicts
Add the following using statements first:
using System.Data.Linq.Mapping;
using System.Reflection;
Run the program, and you will get an output as shown below:
Now open Northwind.dbml, click on the UnitPrice member of the Product class, change its UpdateCheck property to be Never, and run the program again; you won’t see the exception this time, because this column is not used for conflict detecting. The output is like this (you need to change its price back to 19.00 before you re-run the program):
Or you can open SQL Server Management Studio and add the column from there.
Modeling the Products Table with a Version Column
After saving the changes, drag the Products table from Server Explorer to the Northwind.dbml design surface, and keep the name Product1. Now, this table has a version controlling column LastUpdateVersion, with properties as shown below:
Note that its UpdateCheck Property is set to Never. Actually, all other members’ UpdateCheck properties have been set to Never, because for this class, the LastUpdateVersion column and only this column will be used for conflict detecting.
Open the file Northwind.designer.cs, and you will see the column LastUpdateVersion has the following attributes:
[Column(Storage="_LastUpdateVersion", AutoSync=AutoSync.Always,
DbType="rowversion NOT NULL", CanBeNull=false, IsDbGenerated=true,
IsVersion=true, UpdateCheck=UpdateCheck.Never)]
public System.Data.Linq.Binary LastUpdateVersion
Writing the Test Code
We can write similar code to test this new version controlling mechanism:
Console.WriteLine("First User ...");
Product product = (from p in db.Products
where p.ProductID == 3
select p).First();
Console.WriteLine("Original unit in stock: {0}", product.UnitsInStock);
product.UnitsInStock = 26;
Console.WriteLine("Current unit in stock to update: {0}", product.UnitsInStock);
Console.WriteLine("Second User ...");
NorthwindDataContext db2 = new NorthwindDataContext();
Product product2 = (from p in db2.Products
where p.ProductID == 3
select p).First();
Console.WriteLine("Original unit in stock: {0}", product2.UnitsInStock);
product2.UnitsInStock = 27;
Console.WriteLine("Current unit in stock to update: {0}", product2.UnitsInStock);
db2.SubmitChanges();
db2.Dispose();
Console.WriteLine("First User ...");
try
{
db.SubmitChanges();
}
catch (ChangeConflictException)
{
Console.WriteLine("Conflict is detected");
foreach (ObjectChangeConflict occ in db.ChangeConflicts)
{
MetaTable metatable = db.Mapping.GetTable(occ.Object.GetType());
Product entityInConflict = (Product)occ.Object;
Console.WriteLine("Table name: {0}", metatable.TableName);
Console.Write("Product ID: ");
Console.WriteLine(entityInConflict.ProductID);
foreach (MemberChangeConflict mcc in occ.MemberConflicts)
{
object currVal = mcc.CurrentValue;
object origVal = mcc.OriginalValue;
object databaseVal = mcc.DatabaseValue;
MemberInfo mi = mcc.Member;
Console.WriteLine("Member: {0}", mi.Name);
Console.WriteLine("current value: {0}", currVal);
Console.WriteLine("original value: {0}", origVal);
Console.WriteLine("database value: {0}", databaseVal);
}
}
}
Testing the Conflicts
This time we try to update UnitInStock for product 3. From the output, we can see a conflict is detected again when the first user submits changes to the database.
Transactions Support
In the previous section, we learned that simultaneous changes by different users can be controlled by using a version column or the UpdateCheck property. Sometimes the same user may have made several changes and some of the changes might not succeed, in which case we need a way to control the behavior of the overall update result. This is handled by transaction support.
LINQ to SQL uses same transaction mechanism as ADO.NET, i.e. using implicit transactions, or explicit transactions. It can also participate in an existing ADO.NET transaction to let the outsider code decide the result of the updates.
Implicit Transactions
By default, LINQ to SQL uses an implicit transaction for each SubmitChanges call. All updates between two SubmitChanges 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, so both updates will fail. Nothing will be written to the database.
Product prod1 = (from p in db.Products
where p.ProductID == 4
select p).First();
Product prod2 = (from p in db.Products
where p.ProductID == 5
select p).First();
prod1.UnitPrice += 1;
prod2.UnitPrice = -5;
db.SubmitChanges();
The output will be like this:
Explicit Transactions
Besides 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 succeed or fail.
For example, in the following code, 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 been really committed, as the transaction scope was still not close. We then try to update another product, which fails due to the same constraint as in the previous example. The final result is neither of those two products has been updated. Or we can say the first update has been rolled back.
using (TransactionScope ts = new TransactionScope())
{
try
{
Product prod1 = (from p in db.Products
where p.ProductID == 4
select p).First();
prod1.UnitPrice += 1;
db.SubmitChanges();
Product prod2 = (from p in db.Products
where p.ProductID == 5
select p).First();
prod2.UnitPrice = -5;
db.SubmitChanges();
}
catch (System.Data.SqlClient.SqlException e)
{
Console.WriteLine("Updates failed. Error Message: {0}", e.Message);
}
}
Note: TransactionScope is in the .NET assembly System.Transactions, so you need to first add a reference to System.Transactions, then add the following using statement to Program.cs:
using System.Transactions;
The output of the program is the same as in the previous example when implicit transaction is used.
If you start the program in debugging mode, after the first SubmitChanges is called, you can go to SQL Server Management Studio and query product 4’s price 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 dirty data which has not been committed. With this hint, you will see its price is added by 1. Then after the second SubmitChanges is called, an exception is thrown, and the transaction scope is closed. At this point, if you run the query again, you will see product 4’s price is rolled back to its original value.
Note after the first call to the method SubmitChanges, you shouldn’t use the following statement to query the price value of the product, otherwise you will not be able to get any result back; instead, you will be waiting forever as it is waiting for the transaction to get committed:
select UnitPrice from products where productID = 4
Participating in Existing ADO.NET Transactions
Because LINQ to SQL is part of the ADO.NET family, it can also participate in an existing ADO.NET transaction. No matter if the update is done in traditional ADO.NET code, or in LINQ to SQL, all of them will be committed at the same time, or rolled back if any of them fails.
In the following code, we will first update a product using a traditional ADO.NET connection, then update another product using LINQ to SQL. The second update will fail, making the whole transaction roll back.
string connString = "Server=your_db_name\\your_db_instance;initial " +
"cataLog=Northwind;user=your_user_name;pwd=your_password";
SqlConnection conn = null;
SqlCommand cmd = null;
try
{
conn = new SqlConnection(connString);
conn.Open();
NorthwindDataContext db2 = new NorthwindDataContext(conn);
SqlTransaction trans = conn.BeginTransaction();
try
{
using (cmd = new SqlCommand())
{
cmd.CommandText = "UPDATE Products SET UnitPrice " +
"= UnitPrice+1 WHERE ProductID = 4";
cmd.Connection = conn;
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
}
db2.Transaction = trans;
Product prod2 = (from p in db2.Products
where p.ProductID == 5
select p).First();
prod2.UnitPrice = -5;
db2.SubmitChanges();
db2.Dispose();
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine("Updates failed. Error Message: {0}",
e.Message);
}
}
catch (Exception e)
{
Console.WriteLine("Can not connect to database. Error: {0}",
e.Message);
}
finally
{
if (cmd != null)
cmd.Dispose();
if (conn != null)
conn.Dispose();
}
There are two things to note for the above code. First, we can’t re-use this connection string:
global::TestLINQToSQLApp.Properties.Settings.Default.NorthwindConnectionString
It is because the password is stripped out from this string.
Secondly, the following using statement has to be added to the beginning of the Program.cs file:
using System.Data.SqlClient;
The output of the program is still the same as in previous examples.
Adding Validations to Entity Classes
Validating data is the process of confirming that the values entered into data objects comply with the constraints in an object's schema, in addition to the rules established for your application. Validating data before you send updates to the underlying database is a good practice that reduces errors and the potential number of round trips between an application and the database.
The Object Relational Designer (O/R Designer) provides partial methods that enable users to extend the designer-generated code that runs during Inserts, Updates, and Deletes of complete entities, and also during and after individual column changes.
These validation methods are all partial methods, so there is no overhead at all if you didn’t implement them, because unimplemented partial methods are not compiled into IL.
You can implement a validation method in another partial class. In our example, we can add the following method to the existing NorthwindDataContext.cs file:
public partial class Product
{
partial void OnProductNameChanging(string value)
{
if (value.IndexOf("@") >= 0)
throw new Exception("ProductName can not contain @");
}
}
Note that this method should be inside the partial class Product, not inside NorthwindDataContext.
Now we can test it using the following code:
Product product = (from p in db.Products
where p.ProductID == 5
select p).First();
try
{
product.ProductName = "Name @ this place";
db.SubmitChanges();
}
catch (Exception e)
{
Console.WriteLine("Update failed. Reason: {0}", e.Message);
}
Run this program and you will get output like this:
You can implement any of the validation methods for any property, before or after the change.
Debugging LINQ to SQL Programs
Within Visual Studio 2008, when debugging a LINQ to SQL program, we can use the traditional Watch or QuickWatch window to inspect a variable. For example, after the following line is executed, we can right click on the products variable, and select QuickWatch … or Add Watch to see the content of this variable:
var products = from p in db.Products
where p.CategoryID == 1
select p;
The QuickWatch window will be like this:
We can also hover the mouse over the products variable and wait for the Quick Info popup window to appear, then inspect it on the fly. The popup Quick Info window will be like this:
From the watch window, we can inspect the return result of the variable, all of its properties, and even its children.
Note: this inspecting may trigger a real query to the database. For example, if you hover your mouse over db.Products and try to open the Results View of it, the database will be queried to get all the products. In an environment with a big database, this may cause some problems.
Summary
In this article, we learned some advanced features of LINQ to SQL. At this point, we have a good understanding of LINQ to SQL, so in the next article, we will apply these skills to the data access layer of our WCF service to connect to databases securely and reliably with LINQ to SQL.
The key points in this article include:
- LINQ to SQL fully supports Stored Procedures with return code, output parameters, and multiple result sets.
- Compiled query can increase performance of repeatedly executed LINQ queries.
- LINQ to SQL allows direct SQL queries to a database.
- Dynamic Queries can be built at runtime using Expressions.
- LINQ to SQL supports single-table inheritance by the use of a discriminator column.
- Concurrent updates can be controlled using the Update Check property or a Version column.
- By default, LINQ to SQL updates are within one implicit transaction.
- Explicit transaction can be defined for LINQ to SQL updates using
TransactionScope.
- LINQ to SQL updates can also participate in traditional ADO.NET transactions.
- Customized validation code can be added to LINQ to SQL entity classes.
- A debugging process may trigger a real query to the database.
Note: this article is based on chapter 11 of my old book "WCF Multi-tier Services Development with LINQ" (ISBN 1847196624). Since LINQ to SQL is now not preferred from Microsoft, this book has been upgraded to using LINQ to Entities in my new book "WCF 4.0 Multi-tier Services Development with LINQ to Entities" (ISBN 1849681147). Both books are hands-on guides to learn how to build SOA applications on the Microsoft platform, with the old one using WCF and LINQ to SQL in Visual Studio 2008, and the new one using WCF and LINQ to Entities in Visual Studio 2010.
With either book, you can learn how to master WCF and LINQ to SQL/LINQ to Entities concepts by completing practical examples and applying them to your real-world assignments. They are among the first of few books to combine WCF and LINQ to SQL/LINQ to Entities in a multi-tier real-world WCF Service. They are ideal for beginners who want to learn how to build scalable, powerful, easy-to-maintain WCF Services. Both books are rich with example code, clear explanations, interesting examples, and practical advice. They are truly hands-on guides for C++ and C# developers.
You don't need to have any experience in WCF or LINQ to SQL/LINQ to Entities to read either book. Detailed instructions and precise screenshots will guide you through the whole process of exploring the new worlds of WCF and LINQ to SQL/LINQ to Entities. These two books are distinguished from other WCF and LINQ to SQL/LINQ to Entities books by that, they focus on how to do it, not why to do it, in such a way so you won't be overwhelmed by tons of information about WCF and LINQ to SQL/LINQ to Entities. Once you have finished the books, you will be proud that you have been working with WCF and LINQ to SQL/LINQ to Entities in the most straightforward way.
You can buy either book from Amazon (search WCF and LINQ), or from the publisher's website at https://www.packtpub.com/wcf-4-0-multi-tier-services-development-with-linq-to-entities/book.
