.NET - LINQ to SQL - part 4

• Date:
• Author: Stefan Cruysberghs
.NET
LINQ to SQL
LINQ

In previous parts of this series of articles about LINQ to SQL I already covered the following topics :

• Querying
• View entities (Object Dumper)
• View SQL statements (Log & Visualizer)
• Local data shaping
• Non-mapped properties in partial entity classes
• Object Relation Diagram versus Class Diagram
• Data shaping with non-mapped properties
• Change tracking
• Bulk operations
• LINQPad
• Inheritance
• Sequence
• Variable assignment (Let)
• Eager loading / prefetching
• Compiling queries
• WCF services

Part 4 will cover :

• Visualizers
  • LINQ to SQL Visualizer
  • Expression Tree Visualizer
• Refresh
  • Refresh (re-execute) query
  • Refresh one object
• Dynamic Where-clause
  • Anonymous functions and IEnumerable
• Lambda expression trees and IQueryable
• PredicateBuilder class and combining expressions
• Predicate library
• Dynamic OrderBy-clause
  • Anonymous functions and IEnumerable
• Lambda expression trees and IQueryable
• Predicate library

 

Visualizers

Visualizers are Visual Studio plugins which allow developers to view objects and data using a specialized user interface while debugging. It seems that the LINQ to SQL Visualizer and Expression Tree Visualizer are not installed by default within Visual Studio 2008 RTM. Both visualizers are very handy when learning LINQ or debugging LINQ to SQL applications.

How to install ?

These visualizers are shipped as samples and their sources can be found in the C:\Program Files\Microsoft Visual Studio 9.0\Samples folder. The latest version of sources can also be downloaded at the Microsoft Visual Studio 2008 Samples website : http://msdn2.microsoft.com/nl-nl/bb330936(en-us).aspx

1) Download the ZIP file with C# samples and unzip it.

2) In the LinqSamples folder you will find the QueryVisualizer and ExpressionTreeVisualizer solutions. Open these solutions and build the libraries.

3) Copy the compiled assemblies (LinqToSqlQueryVisualizer.dll & ExpressionTreeVisualizer.dll) to the ..\Documents\Visual Studio 2008\Visualizers folder.

4) Restart Visual Studio.

 

How to use ?

1) Set a breakpoint and hover a LINQ to SQL query when debugging.

2) Clicking the magnify icon will open the LINQ to SQL Query Visualizer which will show you the generated SQL statement. When you press the Execute button, the SQL statement will be executed and the result of the query will be shown in a new QueryResult window.

3) The Expression Tree Visualizer can be used to examine an Expression (expression tree) object. QueryExpression, one of the Non-Public Members of the LINQ to SQL query, is such an object.

4) The Expression Tree Viewer window will popup and the expression tree will be visualized.

 

Refresh

I was looking for a way to re-execute a LINQ to SQL query to refresh my data in the user interface. It seems that there is a Refresh method of the DataContext class which can do this job.

There are 3 overloaded Refresh methods :

• Refresh(RefreshMode, IEnumerable) : Refreshes a collection of entity objects according to the specified mode.
• Refresh(RefreshMode, Object) : Refreshes an entity object according to the specified mode.
• Refresh(RefreshMode, array<>[]()[]) : Refreshes an array of entity objects according to the specified mode.

Each method needs a RefreshMode which can have 3 possible values :

• KeepCurrentValues : Forces the Refresh method to swap the original value with the values retrieved from the database. No current value is modified.
• KeepChanges : Forces the Refresh method to keep the current value that has been changed, but updates the other values with the database values.
• OverwriteCurrentValues : Forces the Refresh method to override all the current values with the values from the database.

This Refresh method looks really straight-forward and it works perfectly. Let's say, you have a executed a LINQ to SQL query and the results are shown in a ListBox in a WPF Window.

var employees = from emp in dc.Employees
                where emp.Country == "USA"
                select emp;

After a while the user executes the Refresh (F5) command. You only need one line of code to complete this job. Call the Refresh method of the DataContext and pass the LINQ to SQL query object which should be re-executed.

private void Refresh_Executed(object sender, RoutedEventArgs e)
{
  dc.Refresh(RefreshMode.OverwriteCurrentValues, employees);
}

There is only one disadvantage. If you take a look at the executed SQL statements then you will see that for each object (=record) a SQL SELECT statement with where-clause will be executed. So if you have 10 items, 10 SQL statements will be executed.

SELECT [t0].[Country], [t0].[EmployeeID], [t0].[LastName], [t0].[FirstName], 
  [t0].[Title], [t0].[TitleOfCourtesy], [t0].[BirthDate], [t0].[HireDate], [t0].[Address], [t0].[City], 
  [t0].[PostalCode], [t0].[HomePhone], [t0].[Extension], [t0].[Photo], [t0].[Notes], 
  [t0].[ReportsTo], [t0].[PhotoPath], [t0].[Region]
FROM [dbo].[Employees] AS [t0]
WHERE [t0].[EmployeeID] = @p0

This could be acceptable when using the KeepChanges or KeepCurrentValues mode, but it is causes overhead when using the OverwriteCurrentValues mode. One SQL statement without a where-clause would give the same result but faster. Hopefully Microsoft will optimize this in the future.

You can also refresh one object of a collection. e.g. When doubleclicking in the ListBox you want to open a CRUD window to modify/edit the object. Just before opening the popup window, you can refresh the object with the actual data from the database. This can prevent optimistic concurrency problems afterwards.

private void listBox1_MouseDoubleClick(Object sender, MouseEventArgs e)
{
  if (listBox1.SelectedItem != null)
  {
    dc.Refresh(RefreshMode.KeepChanges, listBox1.SelectedItem);
 
    ShowEmployeeCRUDWindow((Employee)listBox1.SelectedItem, CRUD.Update);
  }
}

The same technique can be used to revert an object to its original state. In this case you need to use the OverwriteCurrentValues mode. This can be useful when pressing a Cancel button in a CRUD window.

dc.Refresh(RefreshMode.OverwriteCurrentValues, selectedEmployee);

 

Dynamic Where-clause

In .NET 3.5 there is a System.Linq.Dynamic namespace which allows you to use string expressions in LINQ queries. It can be used to create dynamic queries but I don't like it because it is not type-safe. So let me give you an introduction to anonymous functions, lambda expression trees and the PredicateBuilder class. These features can be used to create finder methods which can use dynamic strong-typed where-clauses as input parameters.

First let us create a DataProvider class which contains an instance of a DataContext to the Northwind SQL Server database. Add a method GetEmployees which returns an IEnumerable of Employees.

public class DataProvider
{
  NorthwindDataContext dc;
 
  public DataProvider()
  {
    dc = new NorthwindDataContext();
  }
 
  public IEnumerable<Employee> GetEmployees()
  {
    return dc.Employees;
  }
}

Call this GetEmployees method in a console application and display all objects of the collection.

var employees = new DataProvider().GetEmployees();

 
foreach (var emp in employees)
  Console.WriteLine("- {0} {1} {2} {3} {4} {5:d}", 
    emp.EmployeeID, emp.LastName, emp.FirstName, emp.Country, emp.City, emp.HireDate);

One SQL statement will be executed in the GetEmployees method.

SELECT [t0].[Country], [t0].[EmployeeID], [t0].[LastName], [t0].[FirstName], 
  [t0].[Title], [t0].[TitleOfCourtesy], [t0].[BirthDate], [t0].[HireDate], [t0].[Address], [t0].[City], 
  [t0].[PostalCode], [t0].[HomePhone], [t0].[Extension], [t0].[Photo], [t0].[Notes], 
  [t0].[ReportsTo], [t0].[PhotoPath], [t0].[Region]
FROM [dbo].[Employees] AS [t0]

In the foreach loop all employees will be displayed.

- 1 Davolio Nancy USA Seattle 1/05/1992
- 2 Fuller Andrew USA Tacoma 14/08/1992
- 3 Leverling Janet USA Kirkland 1/04/1992
- 4 Peacock Margaret USA Redmond 3/05/1993
- 5 Buchanan Steven UK London 17/10/1993
- 6 Suyama Michael UK London 17/10/1993
- 7 King Robert UK London 2/01/1994
- 8 Callahan Laura USA Seattle 5/03/1994
- 9 Dodsworth Anne UK London 15/11/1994

Where-clause of IEnumerable

The GetEmployees method will return all employees in the database. Now we want to provide some functionality to filter this data. This can be done by calling the Where extension method of the local employees collection. e.g. We only want to display the employees of the USA.

var employees = new DataProvider().GetEmployees();
 
employees = employees.Where(e => e.Country == "USA");
 
foreach (var emp in employees)
  Console.WriteLine("- {0} {1} {2} {3} {4} {5:d}", 
    emp.EmployeeID, emp.LastName, emp.FirstName, emp.Country, emp.City, emp.HireDate);

Anonymous functions

To improve this, we need to pass our filter condition to the GetEmployees method of our DataProvider class. Therefore we need to create an anonymous function. An anonymous function has to be declared as a Func delegate. This Func delegate is supplied as a part of LINQ.

public delegate T Func<T, A>(A a);

Modify the console application :

Func<Employee, bool> condition = e => e.Country == "USA";
 
var employees = new DataProvider().GetEmployees(condition);
 
foreach (var emp in employees)
  Console.WriteLine("- {0} {1} {2} {3} {4} {5:d}", 
    emp.EmployeeID, emp.LastName, emp.FirstName, emp.Country, emp.City, emp.HireDate);

This anonymous function can be given as input parameter of the Where extension method in the GetEmployees method.

public IEnumerable<Employee> GetEmployees(Func<Employee, bool> condition)
{
  return dc.Employees.Where(condition);
}

Try it and you will see that it works fine. Take a look at the underlying SQL statement which has been executed. It is still the same as in example 1. All employees will be retrieved and afterwards the local IEnumerable collection will use in-memory filtering.

SELECT [t0].[Country], [t0].[EmployeeID], [t0].[LastName], [t0].[FirstName], 
  [t0].[Title], [t0].[TitleOfCourtesy], [t0].[BirthDate], [t0].[HireDate], [t0].[Address], [t0].[City], 
  [t0].[PostalCode], [t0].[HomePhone], [t0].[Extension], [t0].[Photo], [t0].[Notes], 
  [t0].[ReportsTo], [t0].[PhotoPath], [t0].[Region]
FROM [dbo].[Employees] AS [t0]

- 1 Davolio Nancy USA Seattle 1/05/1992
- 2 Fuller Andrew USA Tacoma 14/08/1992
- 3 Leverling Janet USA Kirkland 1/04/1992
- 4 Peacock Margaret USA Redmond 3/05/1993
- 8 Callahan Laura USA Seattle 5/03/1994

Lambda expression trees and where-clause of IQueryable

We would like to acquire that the SQL statement will use an extra where-clause to limit the amount of data which will be retrieved. Therefore we need the generic Expression<T> type which represents a lambda expression tree.

Modify the console application and add a second overloaded GetEmployees method :

Expression<Func<Employee, bool>> conditions = e => e.Country == "USA";
 
var employees = new DataProvider().GetEmployees(conditions);

public IEnumerable<Employee> GetEmployees(Expression<Func<Employee, bool>> conditions)
{
  return dc.Employees.Where(conditions);
}

This expression looks the same as the anonymous function, but the compiler will behave different. An expression generates IL that constructs a tree of expression objects. An expression tree is a concept which can be found in many functional languages. Internally all LINQ to SQL queries (IQueryable) will be converted to expression trees. On basis of these expression trees SQL statements will be generated.

If you run the application, you will see that the SQL statement has been extended with a where-clause. Of course this will make our application more performant because it reduces database traffic.

SELECT [t0].[Country], [t0].[EmployeeID], [t0].[LastName], [t0].[FirstName], 
  [t0].[Title], [t0].[TitleOfCourtesy], [t0].[BirthDate], [t0].[HireDate], [t0].[Address], [t0].[City], 
  [t0].[PostalCode], [t0].[HomePhone], [t0].[Extension], [t0].[Photo], [t0].[Notes], [t0].[ReportsTo], 
  [t0].[PhotoPath], [t0].[Region]
FROM [dbo].[Employees] AS [t0]
WHERE [t0].[Country] = @p0

- 1 Davolio Nancy USA Seattle 1/05/1992
- 2 Fuller Andrew USA Tacoma 14/08/1992
- 3 Leverling Janet USA Kirkland 1/04/1992
- 4 Peacock Margaret USA Redmond 3/05/1993
- 8 Callahan Laura USA Seattle 5/03/1994

If you have installed the Expression Tree Visualizer, you can set a breakpoint to show the details of the expression tree. All nodes in an expression tree are derived from the abstract class Expression. As you can see, each node uses a specialised type. These types can be found in the System.Linq.Expressions namespace.

• BinaryExpression
• ConditionalExpression
• ConstantExpression
• InvocationExpression
• LambdaExpression
• ListInitExpression
• MemberExpression
• MemberInitExpression
• MethodCallExpression
• NewArrayExpression
• ParameterExpression
• TypeBinaryExpression
• UnaryExpression

 

PredicateBuilder class

Now we know how to pass a filter to our GetEmployees method, we would like to create where-clauses with more filter conditions. Creating more complex where-clauses means creating more complex expression trees. In LINQ there is not an easy approach to support AND- and OR-based predicates. Fortunately, Joseph Albahari created a nice builder class called PredicateBuilder.

The source and assembly of the PredicateBuilder class can be found on his website : http://www.albahari.com/nutshell/predicatebuilder.html

First you have to call one of the static methods True of False. They are simply convenient shortcuts for creating an Expression<Func<T,bool>> that initially evaluates to true or false. Then you can start calling the And and Or methods to add extra filter condition.

The PredicateBuilder just creates an Expression object so you do not have to change the GetEmployees method.

Example 1 : AND-operator

Retrieve all USA employees which were hired before 1993.

var conditions = PredicateBuilder.True<Employee>();
conditions = conditions.And(e => e.Country == "USA");
conditions = conditions.And(e => e.HireDate.Value.Year < 1993);
 
var employees = new DataProvider().GetEmployees(conditions);

This is the result :

SELECT [t0].[Country], [t0].[EmployeeID], [t0].[LastName], [t0].[FirstName], 
  [t0].[Title], [t0].[TitleOfCourtesy], [t0].[BirthDate], [t0].[HireDate], [t0].[Address], [t0].[City], 
  [t0].[PostalCode], [t0].[HomePhone], [t0].[Extension], [t0].[Photo], [t0].[Notes], 
  [t0].[ReportsTo], [t0].[PhotoPath], [t0].[Region]
FROM [dbo].[Employees] AS [t0]
WHERE ([t0].[Country] = @p0) AND (DATEPART(Year, [t0].[HireDate]) < @p1)

- 1 Davolio Nancy USA Seattle 1/05/1992
- 2 Fuller Andrew USA Tacoma 14/08/1992
- 3 Leverling Janet USA Kirkland 1/04/1992

Example 2 : AND-operator, associations and aggregates

This second example shows an expression to retrieve the USA employees which have created more then 100 orders. It shows that you can also add filter conditions to associated entities and use aggregate functions.

var conditions = PredicateBuilder.True<Employee>();
conditions = conditions.And(e => e.Country == "USA");
conditions = conditions.And(e => e.Orders.Count > 100);

SELECT [t0].[Country], [t0].[EmployeeID], [t0].[LastName], [t0].[FirstName], 
  [t0].[Title], [t0].[TitleOfCourtesy], [t0].[BirthDate], [t0].[HireDate], [t0].[Address], [t0].[City], 
  [t0].[PostalCode], [t0].[HomePhone], [t0].[Extension], [t0].[Photo], [t0].[Notes], 
  [t0].[ReportsTo], [t0].[PhotoPath], [t0].[Region]
FROM [dbo].[Employees] AS [t0]
WHERE ([t0].[Country] = @p0) AND (((
    SELECT COUNT(*)
    FROM [dbo].[Orders] AS [t1]
    WHERE [t1].[EmployeeID] = [t0].[EmployeeID]
    )) > @p1)

- 1 Davolio Nancy USA Seattle 1/05/1992
- 3 Leverling Janet USA Kirkland 1/04/1992
- 4 Peacock Margaret USA Redmond 3/05/1993
- 8 Callahan Laura USA Seattle 5/03/1994

Example 3 : OR-operator

This third example uses the OR operator to query all employees who live in Seattle or Tacoma. Start with calling the False method.

var conditions = PredicateBuilder.False<Employee>();
conditions = conditions.Or(e => e.City == "Seattle");
conditions = conditions.Or(e => e.City == "Tacoma");

SELECT [t0].[Country], [t0].[EmployeeID], [t0].[LastName], [t0].[FirstName], 
  [t0].[Title], [t0].[TitleOfCourtesy], [t0].[BirthDate], [t0].[HireDate], [t0].[Address], [t0].[City], 
  [t0].[PostalCode], [t0].[HomePhone], [t0].[Extension], [t0].[Photo], [t0].[Notes], 
  [t0].[ReportsTo], [t0].[PhotoPath], [t0].[Region]
FROM [dbo].[Employees] AS [t0]
WHERE ([t0].[City] = @p0) OR ([t0].[City] = @p1)

- 1 Davolio Nancy USA Seattle 1/05/1992
- 2 Fuller Andrew USA Tacoma 14/08/1992
- 8 Callahan Laura USA Seattle 5/03/1994

Example 4 : Multiple contains expression

An OR expression with multiple Contains extension methods can also be used to create an equivalent of the SQL IN operator.

var conditions = Northwind.PredicateBuilder.False<Employee>();
conditions = conditions.Or(e => e.City.Contains("on"));
conditions = conditions.Or(e => e.City.Contains("an"));
 
var employees = new DataProvider().GetEmployees(conditions);

SELECT [t0].[Country], [t0].[EmployeeID], [t0].[LastName], [t0].[FirstName], 
  [t0].[Title], [t0].[TitleOfCourtesy], [t0].[BirthDate], [t0].[HireDate], [t0].[Address], [t0].[City], 
  [t0].[PostalCode], [t0].[HomePhone], [t0].[Extension], [t0].[Photo], [t0].[Notes], 
  [t0].[ReportsTo], [t0].[PhotoPath], [t0].[Region]
FROM [dbo].[Employees] AS [t0]
WHERE ([t0].[City] LIKE @p0) OR ([t0].[City] LIKE @p1)

- 3 Leverling Janet USA Kirkland 1/04/1992
- 4 Peacock Margaret USA Redmond 3/05/1993
- 5 Buchanan Steven UK London 17/10/1993
- 6 Suyama Michael UK London 17/10/1993
- 7 King Robert UK London 2/01/1994
- 9 Dodsworth Anne UK London 15/11/1994

Example 5 : Combining OR and AND-operators

Combining the OR and AND-operators is quite easy. So let us extend the expression of the previous example and add an extra filter condition (HirdeDate <= 1993).

var conditions = Northwind.PredicateBuilder.False<Employee>();
conditions = conditions.Or(e => e.City.Contains("on"));
conditions = conditions.Or(e => e.City.Contains("an"));
conditions = conditions.And(e => e.HireDate.Value.Year <= 1993);
 
var employees = new DataProvider().GetEmployees(conditions);

SELECT [t0].[Country], [t0].[EmployeeID], [t0].[LastName], [t0].[FirstName], 
  [t0].[Title], [t0].[TitleOfCourtesy], [t0].[BirthDate], [t0].[HireDate], [t0].[Address], [t0].[City], 
  [t0].[PostalCode], [t0].[HomePhone], [t0].[Extension], [t0].[Photo], [t0].[Notes], 
  [t0].[ReportsTo], [t0].[PhotoPath], [t0].[Region]
FROM [dbo].[Employees] AS [t0]
WHERE (([t0].[City] LIKE @p0) OR ([t0].[City] LIKE @p1)) 
  AND (DATEPART(Year, [t0].[HireDate]) <= @p2)

- 3 Leverling Janet USA Kirkland 1/04/1992
- 4 Peacock Margaret USA Redmond 3/05/1993
- 5 Buchanan Steven UK London 17/10/1993
- 6 Suyama Michael UK London 17/10/1993

Example 6 : Predicate library

It is a common pattern to create a reusable predicate library in the data access layer. So it is a good idee to add static finder methods to your entities.

e.g. A HiredBefore method which will return an expression tree.

partial class Employee
{
  public static Expression<Func<Employee, bool>> HiredBefore(int year)
  {
    return PredicateBuilder.True<Employee>().And(e => e.HireDate.Value.Year < year);  
  }
}

var employees = new DataProvider().GetEmployees(Employee.HiredBefore(1993));

 

 

Dynamic OrderBy-clause

Creating dynamic strong-typed OrderBy-clauses can be done in almost the same way as the dynamic Where-clauses.

Anonymous functions & IEnumerable

For local sorting on an IEnumerable you need to create an anonymous function (Func delegate) which returns an IComparable. This function can be passed to the LINQ OrderBy extension method.

Func<Employee, IComparable> order = e => e.LastName;
var employees = new DataProvider().GetEmployees(expression).OrderBy(order);

Combining OrderBy-clauses is more difficult because you need to call the ThenBy extension method for the second, third, fourth, ... order expression.

Func<Employee, IComparable> order1 = e => e.Country;
Func<Employee, IComparable> order2 = e => e.LastName;
var employees = new DataProvider().GetEmployees(expression).OrderBy(order1).ThenBy(order2);

Expressions and IQueryable

Expressions are required when you want to sort an IQueryable which will modify the executed SQL statement. So create an expression and pass it as a second parameter to the GetEmployees method.

var conditions = PredicateBuilder.True<Employee>();
conditions = conditions.And(e => e.Country == "USA");
 
Expression<Func<Employee, IComparable>> order = e => e.LastName;
 
var employees = new DataProvider().GetEmployees(conditions, order);

Create a third overloaded GetEmployees method :

public class DataProvider
{
  NorthwindDataContext dc;
 
  public DataProvider()
  {
    dc = new NorthwindDataContext();
    dc.Log = Console.Out;
  }
 
  public IEnumerable<Employee> GetEmployees()
  {
    return dc.Employees;
  }
 
  public IEnumerable<Employee> GetEmployees(Expression<Func<Employee, bool>> conditions)
  {
    return dc.Employees.Where(expression);
  }
 
  public IEnumerable<Employee> GetEmployees(Expression<Func<Employee, bool>> conditions, 
    Expression<Func<Employee, IComparable>> order)
  {
    return dc.Employees.Where(expression).OrderBy(order);
  }
}

A SQL ORDER BY condition will be added when executing the LINQ query :

SELECT [t0].[Country], [t0].[EmployeeID], [t0].[LastName], [t0].[FirstName], 
  [t0].[Title], [t0].[TitleOfCourtesy], [t0].[BirthDate], [t0].[HireDate], [t0].[Address], [t0].[City], 
  [t0].[PostalCode], [t0].[HomePhone], [t0].[Extension], [t0].[Photo], [t0].[Notes], 
  [t0].[ReportsTo], [t0].[PhotoPath], [t0].[Region]
FROM [dbo].[Employees] AS [t0]
WHERE [t0].[Country] = @p0
ORDER BY [t0].[LastName]

- 8 Callahan Laura USA Seattle 5/03/1994
- 1 Davolio Nancy USA Seattle 1/05/1992
- 2 Fuller Andrew USA Tacoma 14/08/1992
- 3 Leverling Janet USA Kirkland 1/04/1992
- 4 Peacock Margaret USA Redmond 3/05/1993

Predicate library

Now you can extend your reusable predicate library with some extra sorting methods.

partial class Employee
{
  public static Expression<Func<Employee, bool>> HiredBefore(int year)
  {
    return PredicateBuilder.True<Employee>().And(e => e.HireDate.Value.Year < year);  
  }
 
  public static Expression<Func<Employee, IComparable>> SortOnLastName()
  {
    Expression<Func<Employee, IComparable>> order = e => e.LastName; 
    return order; 
  }
}

var employees = new DataProvider().GetEmployees(Employee.HiredBefore(1993),Employee.SortOnLastName());

I did not try to combine OrderBy-clauses or to change the order with Descending and Ascending. I'm afraid that you have to pass a list with IComparable expressions and a kind of OrderType (Ascending, Descending) to the GetEmployees method. Hopefully in the near future there will be a powerful query builder for LINQ which can handle this.

Conclusion

These powerful expressions trees are introduced in C# 3.0 and they are essential when using LINQ to SQL. Expressions are the best technique to create dynamic where clauses. There is just one demerit; they can't be serialized and this makes it difficult to use them in WCF services.

If you have any remarks or suggestions, please let me know.