Nieuws
Foto's
Artikelen
Componenten
Applicaties
Kleinkunst

.NET - LINQ to SQL - deel 3

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

This article (part 3) contains following topics :

Next article (part 4) will cover :

 

Sequence

Sometimes it is handy to add a sequence to your query results. LINQ to SQL (DLINQ) does not support this, but there is an overloaded Select extension method in LINQ to Objects which allows you to number the results. This method can not be called in a query expression, but it is quite easy to write it as a lamba expression : 

NorthwindDataContext dc = new NorthwindDataContext();
 
var employees = dc.Employees.AsEnumerable().Select((emp, seq)   
  => new { Sequence = seq, FirstName = emp.FirstName, LastName = emp.LastName });
 
foreach (var emp in employees)
  Console.WriteLine("- {0} : {1} {2}", emp.Sequence, emp.FirstName, emp.LastName);

 

Variable assignment (let)

LINQ has a LET keyword which can be used for variable assignment within LINQ queries. This is even more powerful than an alias in a SQL statement because you can refer to the variable in every section of the query expression. 

NorthwindDataContext dc = new NorthwindDataContext();
 
var employees = from emp in dc.Employees
                let OrderCount = emp.Orders.Count
                let FullName = emp.FirstName + " " + emp.LastName
                where OrderCount > 100
                select new { emp.EmployeeID, FullName, OrderCount };
 
foreach (var emp in employees)
  Console.WriteLine("- {0} {1} {2}", emp.EmployeeID, emp.FullName, emp.OrderCount);

  Result : 

- 1 Nancy Davolio 123
- 3 Janet Leverling 127
- 4 Margaret Peacock 156
- 8 Laura Callahan 104

Executed SQL statement : 

SELECT [t3].[EmployeeID], [t3].[value], [t3].[OrderCount]
FROM (
  SELECT ([t2].[FirstName] + @p0) + [t2].[LastName] AS [value], [t2].[EmployeeID], [t2].[value] AS [OrderCount]
  FROM (    
    SELECT (      
      SELECT COUNT(*)
      FROM [dbo].[Orders] AS [t1]      
      WHERE [t1].[EmployeeID] = [t0].[EmployeeID]) AS [value],
    [t0].[EmployeeID], [t0].[LastName], [t0].[FirstName]   FROM [dbo].[Employees] AS [t0]
  ) AS [t2]
) AS [t3]
WHERE [t3].[OrderCount] > @p1

 

Eager loading / prefetching

In my first article I showed that LINQ to SQL uses deferred query execution and lazy loading for child entities. In the following example 8 SQL statements are being executed. 

NorthwindDataContext dc = new NorthwindDataContext();
 
var customers = from cust in dc.Customers
                where cust.Country == "UK"
                select cust;
 
foreach (var cust in customers)
{
  Console.WriteLine("{0}", cust.CompanyName);
  foreach (var ord in cust.Orders)
    Console.WriteLine(" - {0} {1}", ord.OrderDate, ord.Freight);
}
One SQL statement is executed to retrieve a collection of all customers. For each customer a new query will be executed to retrieve a collection of all orders of the customer. 
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address],
  [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[Country] = @p0
SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], 
  [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], 
  [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]
FROM [dbo].[Orders] AS [t0]
WHERE [t0].[CustomerID] = @p0

Accessing these lazy loaded properties (Orders) can cause performance issues because there are too many roundtrips to the database. A solution to improve the performance can be to include the child entities in the main LINQ to SQL query. There is also another and sometimes better technique which is called eager loading.

By setting the LoadOptions property of the DataContext, the loading behavior can be changed. Just create a DataLoadOptions object and specify which entities and associations should be prefetched. Assign this object to the DataContext. 

NorthwindDataContext dc = new NorthwindDataContext()
 
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Customer>(e => e.Orders);
dc.LoadOptions = options;
 
var customers = from cust in dc.Customers
                where cust.Country == "UK"
                select cust;
 
foreach (var cust in customers)
{
  Console.WriteLine("{0}", cust.CompanyName);
  foreach (var ord in cust.Orders)
    Console.WriteLine(" - {0} {1}", ord.OrderDate, ord.Freight);
}
When using the first LINQ for SQL query, only one SQL statement will be executed. It will also fetch all orders of the selected customers. 
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], 
  [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax], [t1].[OrderID], 
  [t1].[CustomerID] AS [CustomerID2], [t1].[EmployeeID], [t1].[OrderDate], [t1].[RequiredDate], 
  [t1].[ShippedDate], [t1].[ShipVia], [t1].[Freight], [t1].[ShipName], [t1].[ShipAddress], [t1].[ShipCity], 
  [t1].[ShipRegion], [t1].[ShipPostalCode], [t1].[ShipCountry], (
    SELECT COUNT(*)
    FROM [dbo].[Orders] AS [t2]
    WHERE [t2].[CustomerID] = [t0].[CustomerID]
    ) AS [count]
FROM [dbo].[Customers] AS [t0]
LEFT OUTER JOIN [dbo].[Orders] AS [t1] ON [t1].[CustomerID] = [t0].[CustomerID]
WHERE [t0].[Country] = @p0
ORDER BY [t0].[CustomerID], [t1].[OrderID]

Be aware that there is no UnloadWith method. So you have to create another DataContext when you want to undo these loading options.

The eager loading technique will prefetch ALL orders of the selected customers. So when you only need a small subset of orders, this filtering will be done on the consumer caller side, not in the SQL statement. Following example will execute just the same SQL statement ! 

foreach (var cust in customers)
{
  Console.WriteLine("{0}", cust.CompanyName);
  foreach (var ord in cust.Orders.Where(o => o.Freight > 200).OrderByDescending(o => o.Freight))
    Console.WriteLine(" - {0} {1}", ord.OrderDate, ord.Freight);
}

Eager loading is a powerful feature but be careful prefetching all data when you only need a subset. In this example the best performance can be gained by creating one LINQ to SQL statement which will return an anonymous type with the UK customers and their orders with a freight higher then 100. 

NorthwindDataContext dc = new NorthwindDataContext();
 
var customers = from cust in dc.Customers
                where cust.Country == "UK"
                select new { CompanyName = cust.CompanyName, BigOrders = cust.Orders.Where(o => o.Freight > 100).OrderByDescending(o => o.Freight) };
 
foreach (var cust in customers)
{
  Console.WriteLine("{0}", cust.CompanyName);
  foreach (var ord in cust.BigOrders)
    Console.WriteLine(" - {0} {1}", ord.OrderDate, ord.Freight);
}
SELECT [t0].[CompanyName], [t1].[OrderID], [t1].[CustomerID], [t1].[EmployeeID],
  [t1].[OrderDate], [t1].[RequiredDate], [t1].[ShippedDate], [t1].[ShipVia], 
  [t1].[Freight], [t1].[ShipName], [t1].[ShipAddress], [t1].[ShipCity], [t1].[ShipRegion],
  [t1].[ShipPostalCode], [t1].[ShipCountry], (
    SELECT COUNT(*)
    FROM [dbo].[Orders] AS [t2]
    WHERE ([t2].[Freight] > @p0) AND ([t2].[CustomerID] = [t0].[CustomerID])
    ) AS [count]
FROM [dbo].[Customers] AS [t0]
LEFT OUTER JOIN [dbo].[Orders] AS [t1] ON ([t1].[Freight] > @p0) AND ([t1].[CustomerID] = [t0].[CustomerID])
WHERE [t0].[Country] = @p1
ORDER BY [t0].[CustomerID], [t1].[Freight] DESC, [t1].[OrderID]

 

Compiling queries

When you want to reuse some queries and performance is an important issue, there is a technique to compile a LINQ to SQL query. For example, you need the employees of a specific country. 

var employeesUK = from emp in dc.Employees where emp.Country == "UK" select emp;
var employeesUSA = from emp in dc.Employees where emp.Country == "USA" select emp;
Or the same with lamba expressions : 
var employeesUK = dc.Employees.Where(emp => emp.Country == "UK");
var employeesUSA = dc.Employees.Where(emp => emp.Country == "USA");

If you regularly consume the same query, you can eliminate the query parsing. Therefore you need to use the Compile method of the CompiledQuery class. Pass a DataContext, some extra arguments and a lamba expression. The method returns a delegate. 

var employeesByCountryQuery = CompiledQuery.Compile((NorthwindDataContext dctemp, string country)
  => from emp in dctemp.Employees where emp.Country == country select emp);
Consuming the query can be done by calling the Invoke method of the delegate : 
NorthwindDataContext dc = new NorthwindDataContext();
var employeesUK = employeesByCountryQuery.Invoke(dc, "UK");
var employeesUSA = employeesByCountryQuery.Invoke(dc, "USA");

 

WCF services

I don’t think LINQ to SQL is a good choice for big projects or complex service oriented architectures, but of course it can be used together with WCF services. LINQ to SQL supports serialization as XML by decorating your entities with DataContract and DataMember attributes during code generation. 

Serialization mode

Click on the designer surface in your .dbml file and set the Serialization Mode property of the DataContext to Unidirectional. When you take a look at the generated code in the designer.cs file then you will see that WCF attributes have been added.

[Table(Name="dbo.Employees")]
[DataContract()]
public partial class Employee : INotifyPropertyChanging, INotifyPropertyChanged
{
  [Column(Storage="_EmployeeID", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", 
    IsPrimaryKey=true, IsDbGenerated=true, UpdateCheck=UpdateCheck.Never)]
  [DataMember(Order=1)]
  public int EmployeeID
  {
  }
 
  [Column(Storage="_LastName", DbType="NVarChar(20) NOT NULL", CanBeNull=false, UpdateCheck=UpdateCheck.Never)]
  [DataMember(Order=2)]
  public string LastName
  {
  }
 
  [Column(Storage="_FirstName", DbType="NVarChar(10) NOT NULL", CanBeNull=false, UpdateCheck=UpdateCheck.Never)]
  [DataMember(Order=3)]
  public string FirstName
  {
  }
}

Now you can easily create WCF services and pass entities or collections of entities from client to server or the other way around. This small example shows some CUD operations.

[ServiceContract]
public interface IEmployeeService
{
  [OperationContract]
  IEnumerable<Employee> GetEmployees();
 
  [OperationContract]
  bool AddEmployee(Employee employee);
 
  [OperationContract]
  bool UpdateEmployee(Employee employee);
 
  [OperationContract]
  bool DeleteEmployee(Employee employee);
}

 

Adding and deleting entities

Implementing Add and Delete methods in a WCF service which uses LINQ to SQL can be done straight forward :

public void AddEmployee(Employee employee)
{
  using (NorthwindDataContext dc = new NorthwindDataContext())
  {
    dc.Employees.InsertOnSubmit(employee);
    dc.SubmitChanges();
  }
}
public void DeleteEmployee(Employee employee)
{
  using (NorthwindDataContext dc = new NorthwindDataContext())
  {
    Employee employeeToDelete = dc.Employees.SingleOrDefault(e => e.EmployeeID == employee.EmployeeID);
    if (employeeToDelete != null)
    {
      dc.Employees.DeleteOnSubmit(employeeToDelete);
      dc.SubmitChanges();
    }
  }
}

 

Attach() method and updating entities

Updating entities is a little bit trickier. The DataContext object is used to track all changes and updates which are made to objects in LINQ to SQL. If you are serializing and deserializing objects using (web) services then these entities will be detached from the DataContext and maybe the instance of the DataContext will be gone after the data has been send by the service.

When sending the entities back to the server, you have to call the Attach() method of the DataContext to re-attach them. The DataContext does not know the state of the entity and therefore you have to use one of these 3 techniques :

  1. Call Attach() and update properties individually before calling SubmitChanges().
  2. Call Attach() and pass an object with the old values.
  3. Call Attach() with a Boolean which indicates that the entity has been modified. In this case you have to make sure that the entity has a Timestamp member.

I prefer the third technique. So I have added a Timestamp field to the Employee table and now my Update method is quite simple :

public void UpdateEmployee(Employee employee)
{
  using (NorthwindDataContext dc = new NorthwindDataContext())
  {
    dc.Employees.Attach(employee, true);
    dc.SubmitChanges();
  }
}

It is also possible to use the same technique for a delete operation. The DataContext also has an AttachAll() method which can be used to attach a collection of entities.

Of course this example just shows the basics of using LINQ to SQL in WCF services. In a perfect solution you should resolve concurrency conflicts, handle exceptions, return validation results, ...