.NET - LINQ to SQL - deel 1

• Datum:
• Auteur: Stefan Cruysberghs
• Deze pagina is enkel in het Engels beschikbaar
.NET
LINQ to SQL
LINQ

After reading all articles on Scott Guthrie's blog and some Microsoft documents about LINQ to SQL, formerly known as DLINQ, I started experimenting with an object model of the Northwind database. Some of my examples can be useful for others so I decided to published them all in this article. Please make sure to read the blog of Scott Guthrie (http://weblogs.asp.net/scottgu/) before starting with LINQ to SQL.

All examples on this page are originally created with Visual Studio 2008 Beta 2 and .NET 3.5 'Orcas' release. In December 2008 this article has been updated because some names of methods and collections have been changed in Visual Studio 2008 RTM.

• Querying
• View entities (ObjectDumper)
• 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

In part 2, 3 and 4 I will cover following topics :

• LINQPad
• Inheritance
• Sequence
• Variable assignment (Let)
• Eager loading / prefetching
• Compiling queries 
• WCF services
• Visualizers
• Refresh
• Dynamic Where-clause (Anonymous functions, Expressions & PredicateBuilder)
• Dynamic OrderBy-clause

Querying

I really like querying with LINQ to SQL. It is easy, powerful, very intuitive and the query statements are much smaller and easier to read then their SQL equivalents. It is very easy to add parameters, return anonymous types, use associations, add grouping & aggregates, ... Some examples :

Top 10 of most ordered products

The Take() operator yields a given number of elements and skips the remaining.

NorthwindDataContext dc = new NorthwindDataContext();

// Top 10 of most ordered products
var result1 = (from prod in dc.Products
       orderby prod.Order_Details.Sum(od => od.Quantity) descending
       select new
       {
        prod.ProductID,
        prod.ProductName,
        Ordered = prod.Order_Details.Sum(od => od.Quantity)
       }).Take(10);

This LINQ to SQL query will result in the following SQL statement :

SELECT TOP 10 [t2].[ProductID], [t2].[ProductName], [t2].[value] AS [Ordered]
FROM (
    SELECT [t0].[ProductID], [t0].[ProductName], (
        SELECT SUM(CONVERT(Int,[t1].[Quantity]))
        FROM [dbo].[Order Details] AS [t1]
        WHERE [t1].[ProductID] = [t0].[ProductID]
        ) AS [value]
    FROM [dbo].[Products] AS [t0]
    ) AS [t2]
ORDER BY (
    SELECT SUM(CONVERT(Int,[t3].[Quantity]))
    FROM [dbo].[Order Details] AS [t3]
    WHERE [t3].[ProductID] = [t2].[ProductID]
    ) DESC

 

All employees who created an order for a French customer

// All employees who created an order for a French customer
var result2 = from emp in dc.Employees
              where emp.Orders.Any(ord => ord.Customer.Country == "France")
              select emp;

SELECT [t0].[EmployeeID], [t0].[LastName], [t0].[FirstName], [t0].[Title], [t0].[TitleOfCourtesy],
  [t0].[BirthDate], [t0].[HireDate], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], 
  [t0].[Country], [t0].[HomePhone], [t0].[Extension], [t0].[Photo], [t0].[Notes], [t0].[ReportsTo], [t0].[PhotoPath]
FROM [dbo].[Employees] AS [t0]
WHERE EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[Orders] AS [t1]
    LEFT OUTER JOIN [dbo].[Customers] AS [t2] ON [t2].[CustomerID] = [t1].[CustomerID]
    WHERE ([t2].[Country] = 'France') AND ([t1].[EmployeeID] = [t0].[EmployeeID])
    )

 

All orders which have a customer and a supplier of the same country

// all orders which have a customer and a supplier of the same country
// 1 customer per order, a supplier per product of an order line
var result3 = from ord in dc.Orders
              where ord.Order_Details.Any(od => od.Product.Supplier.Country == ord.Customer.Country)
              orderby ord.Customer.Country ascending, ord.OrderDate descending
              select new
              {
                ord.OrderID,
                ord.OrderDate,
                Country = ord.Customer.Country,
                CustomerCompanyName = ord.Customer.CompanyName,
                SupplierCompanyName = ord.Order_Details.First(od => od.Product.Supplier.Country == ord.Customer.Country).Product.Supplier.CompanyName
              };

SELECT [t0].[OrderID], [t0].[OrderDate], [t1].[Country], [t1].[CompanyName], (
    SELECT [t11].[CompanyName]
    FROM (
        SELECT TOP 1 [t6].[ProductID]
        FROM [dbo].[Order Details] AS [t6]
        INNER JOIN [dbo].[Products] AS [t7] ON [t7].[ProductID] = [t6].[ProductID]
        LEFT OUTER JOIN [dbo].[Suppliers] AS [t8] ON [t8].[SupplierID] = [t7].[SupplierID]
        WHERE ([t8].[Country] = [t1].[Country]) AND ([t6].[OrderID] = [t0].[OrderID])
        ) AS [t9]
    INNER JOIN [dbo].[Products] AS [t10] ON [t10].[ProductID] = [t9].[ProductID]
    LEFT OUTER JOIN [dbo].[Suppliers] AS [t11] ON [t11].[SupplierID] = [t10].[SupplierID]
    ) AS [value]
FROM [dbo].[Orders] AS [t0]
LEFT OUTER JOIN [dbo].[Customers] AS [t1] ON [t1].[CustomerID] = [t0].[CustomerID]
WHERE EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[Order Details] AS [t2]
    INNER JOIN [dbo].[Products] AS [t3] ON [t3].[ProductID] = [t2].[ProductID]
    LEFT OUTER JOIN [dbo].[Suppliers] AS [t4] ON [t4].[SupplierID] = [t3].[SupplierID]
    LEFT OUTER JOIN [dbo].[Customers] AS [t5] ON [t5].[CustomerID] = [t0].[CustomerID]
    WHERE ([t4].[Country] = [t5].[Country]) AND ([t2].[OrderID] = [t0].[OrderID])
    )
ORDER BY [t1].[Country], [t0].[OrderDate] DESC

 

All employees from local list of IDs

LINQ to SQL has a Contains() method which is the equivalent of the SQL IN operator. The Contains method is quite handy when you want to pass a local list of values to a LINQ to SQL query.

List<int> employeeIDs = new List<int> { 2, 4, 6, 8 };
 
var employees = from emp in dc.Employees
                where employeeIDs.Contains(emp.EmployeeID)
                select emp;

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

 

Create 2 groups of Products depending the UnitsInStock

var GroupedProducts = from p in dc.Products.OrderBy(o => o.UnitsInStock)
                      group p by new { StockStatus = p.UnitsInStock > 50 } into gr
                      select gr;

A function which checks if a given customer does exist

bool bln1 = DoesCustomerExist("MAISD");
bool bln2 = DoesCustomerExist("UNKNOWN");
 
public DoesCustomerExist(string customerId)
{
  NorthwindDataContext dc = new NorthwindDataContext();
  return dc.Customers.Any(c => c.CustomerID == customerId);
}

 

A function to retrieve a given customer

If you call the Single() method, you need to catch the InvalidOperationException: Sequence contains no elements. Using the SingleOrDefault() method can be an easier solution.

Customer customer1 = GetCustomer("MAISD");
Customer customer2 = GetCustomer("UNKNOWN");
Customer customer3 = GetCustomer2("MAISD");
Customer customer4 = GetCustomer2("UNKNOWN");
 
// Get customer or return empty customer entity
public Customer GetCustomer(string customerId)
{
  NorthwindDataContext dc = new NorthwindDataContext();
  try
  {
    Customer customer = dc.Customers.Single(c => c.CustomerID == customerId);
    return customer;
  }
  catch (InvalidOperationException) // "Sequence contains no elements" exception
  {
    return new Customer();
  }
}
 
// If you like to return null, rather than generate an exception, then use SingleOrDefault().
public Customer GetCustomer2(string customerId)
{
  NorthwindDataContext dc = new NorthwindDataContext();
  Customer customer = dc.Customers.SingleOrDefault(c => c.CustomerID == customerId);
  return customer;
}

 

View entities (ObjectDumper)

If you would like to display the whole content of an entity in a console application, you can use the small utility library called ObjectDumper. This component iterates through the properties using reflection and dumps the values to the console window. In Visual Studio Beta 2 this solution is included in the LinqSamples\ObjectDumper folder in the CSharpSamples.zip file which can be found in C:\Program Files\Microsoft Visual Studio 9.0\Samples\.

foreach (var emp in employees)
  ObjectDumper.Write(emp);

 

View SQL statements

If you would like to see the SQL statements which are being generated internally, then there are a several solutions.

(1) If using a DataContext in a console application, then you can easily connect the DataContext.Log to a TextWriter like Console.Out :

NorthwindDataContext dc = new NorthwindDataContext();
dc.Log = Console.Out;

(2) If you would like to send the output to the Debugger Output Window, then you should take a look at the blog of Kris Vandermotten (http://www.u2u.info/Blogs/Kris/Lists/Posts/Post.aspx?ID=11). He has written a nice DebuggerWriter class.

NorthwindDataContext dc = new NorthwindDataContext();
dc.Log = new DebuggerWriter();

(3) You can also install the LINQ to SQL Visualizer which allows you to hover over LINQ to SQL expressions in the Visual Studio Debugger. More information can be found on the website of Scott Guthrie : http://weblogs.asp.net/scottgu/archive/2007/07/31/linq-to-sql-debug-visualizer.aspx

 

Local data shaping

A great feature of LINQ is to convert the LINQ to SQL query, which is executed on the database, to a local IEnumerable. This allows you to add some extra procedural logic by using functions in a local LINQ to Objects query. To convert the LINQ to SQL (=IQueryable) query to an IEnumerable, you need to call the AsEnumerable() method. In previous betas of Orcas this was called ToSequence().

public string FormatAddress(string address, string city, string region, string postalCode, string country)
{
  if (country.Equals("UK") )
    return address + ", " + city + ", " + postalCode;
  else if (country.Equals("USA"))
    return address + ", " + city + ", " + region + ", " + postalCode;
  else 
    return address + ", " + postalCode + " " + city;
}
 
// LINQ for SQL query on database
var employeesServer = from emp in dc.Employees
                    select emp;
 
// Local LINQ query
var employeesLocal = from emp in employeesServer.AsEnumerable()
                   select new
                   {
                     EmployeeID = emp.EmployeeID,
                     FirstName = emp.FirstName,
                     LastName = emp.LastName,
                     FormattedAddress = FormatAddress(emp.Address, emp.City, emp.Region, emp.PostalCode, emp.Country)
                   };

You can also do the same in just one query :

var employeesLocal2 = from emp in dc.Employees.AsEnumerable()
                      select new
                      {
                        EmployeeID = emp.EmployeeID,
                        FirstName = emp.FirstName,
                        LastName = emp.LastName,
                        FormattedAddress = FormatAddress(emp.Address, emp.City, emp.Region, emp.PostalCode, emp.Country)
                      };

It is also possible to declare a lambda expression. An expression can be invoked in a LINQ to SQL query by calling the Compile() and Invoke() method. Make sure you also use the AsEnumerable() method.

Expression<Func<Employee, string>> FormattedAddress = (e) => e.Address + " " + e.PostalCode + " " + e.City;
 
var employeesServer2 = from emp in dc.Employees.AsEnumerable()
                     select new
                     {
                       EmployeeID = emp.EmployeeID,
                       FirstName = emp.FirstName,
                       LastName = emp.LastName,
                       FormattedAddress = FormattedAddress.Compile().Invoke(emp)
                     };

 

Non-mapped properties in partial entity classes

Instead of using local functions or lambda expressions, you can also implement your own non-mapped properties to the partial class of the LINQ to SQL entities. Not only you can create read-only computed fields, but also properties with get and set methods which can access other mapped properties.

For example, you can hide some mapped fields by making them private in the object relational designer. Subsequently you can create your own public non-mapped properties which do some data conversion.

In the next example I have added a read-only FullAddress property which does some string formatting with the mapped address properties. CountryName is a new property which can be used to get or set the full name of a country.

namespace Northwind
{
  partial class Employee
  {
    public string FullAddress 
    {
      get
      {
        if (this._Country != null)
        {
          if (this._Country.Equals("UK"))
            return "(UK) " + this._Address + ", " + this._City + ", " + this._PostalCode;
          else if (this._Country.Equals("USA"))
            return "(USA) " + this._Address + ", " + this._City + ", " + this._Region + ", " + this._PostalCode;
          else
            return "(" + this._Country + ") " + this._Address + ", " + this._PostalCode + " " + this._City;
        }
        else
          return "";
      }
    }
 
    public string CountryName
    {
      get
      {
        if (this._Country != null)
        {
          if (_Country.Equals("UK"))
            return "Great Britain";
          else if (_Country.Equals("USA"))
            return "United States";
          else
            return "Unknown";
        }
        else
          return "";
      }
      set
      {
        if (value.Equals("Great Britain"))
          _Country = "UK";
        else if (value.Equals("United States"))
          _Country = "USA";
        else
          _Country = "";
 
      }
    }
  }
}

Show values of non-mapped FullAddress property of partial class

NorthwindDataContext dc = new NorthwindDataContext();
 
var employees = from emp in dc.Employees
              where emp.LastName.Contains("a")
              select emp;
 
// Show values of local FullAddress property of partial class
foreach (var emp in employees)
  Console.WriteLine("{0} {1} - {2}", emp.FirstName, emp.LastName, emp.FullAddress);

Nancy Davolio - (USA) 507 - 20th Ave. E. Apt. 2A, Seattle, WA, 98122
Margaret Peacock - (USA) 4110 Old Redmond Rd., Redmond, WA, 98052
Steven Buchanan - (UK) 14 Garrett Hill, London, SW1 8JR
Michael Suyama - (UK) Coventry House Miner Rd., London, EC2 7JR
Laura Callahan - (USA) 4726 - 11th Ave. N.E., Seattle, WA, 98105 Suyama, UK

Set non-mapped CountryName property of partial class

Employee empMichael = dc.Employees.SingleOrDefault(e => e.EmployeeID == 6);
 
// Set local CountryName property of partial class
empMichael.CountryName = "Great Britain";
 
// And check if it has updated the mapped property Country
Console.WriteLine("{0}, {1}", empMichael.LastName, empMichael.Country);

 

Object Relation Diagram versus Class Diagram

At first I was thinking that it is regrettable that you can't see the non-mapped properties in the object relational diagram of the DataContext. But afterwards I found out that when you create your own class diagram all properties (the mapped and the non-mapped ones) are visualized. The mapped ones will be displayed in gray. Great ! It allows you to create class diagrams to visualize sub domains of all your LINQ to SQL entities.

There are still some shortcomings which I hope Microsoft will solve in the final Visual Studio 2008 version. The standard class diagram does not show the LINQ associations by default. And there is also no indication for the key field.

Data shaping with non-mapped properties

Previous examples show that is quite easy to add your own properties which are NOT mapped to the database. But not everything runs smoothly as you can see in next example where I use the non-mapped property FullAddress.

var employeesShape = from emp in dc.Employees
               where emp.LastName.Contains("a")
               select new
               {
                 ID = emp.EmployeeID,
                 Name = emp.FirstName + " " + emp.LastName,
                 Address = emp.FullAddress,
                 OrderCount = emp.Orders.Count
               };
 
foreach (var emp in employeesShape)
  Console.WriteLine("({0}), {1}, {2} - {3}", emp.ID, emp.Name, emp.Address, emp.OrderCount);

When consuming a query which uses 'new' to do some data shaping with non-mapped properties, an InvalidOperationException will be thrown : Binding error : Member 'Northwind.Employee.FullAddress' is not a mapped member of 'Northwind.Employee'.

Matt Warren of Microsoft gave me following explanation : When you shape a query using 'new', DLINQ attempts to reduce the SQL query to just the columns necessary for the projection. If you don’t return an entity, LINQ to SQL won’t materialize one, since that would require all the columns to be returned. Therefore no actual Employee entity is ever produced on the client, so references to Employee’s columns must be translated to SQL that can run on the server. However, only mapped properties can be translated. There is no server equivalent of the 'FullAddress' property. To do something like this you need to add a computed column in the server table and then map a property to it.

Another solution is to return the whole entity in the LINQ to SQL query and implement a second local LINQ to Objects query to do some extra data shaping :

var employeesServer = from emp in dc.Employees
                      where emp.LastName.Contains("a")
                      select emp;
 
var employeesLocal = from emp in employeesServer.AsEnumerable()
                     select new
                     {
                       ID = emp.EmployeeID,
                       Name = emp.FirstName + " " + emp.LastName,
                       Address = emp.FullAddress,
                       OrderCount = emp.Orders.Count
                     };
 
foreach (var emp in employeesLocal)

(1) Nancy Davolio, 507 - 20th Ave. E. Apt. 2A, Seattle, WA, 98122 - 123
(4) Margaret Peacock, 4110 Old Redmond Rd., Redmond, WA, 98052 - 156
(5) Steven Buchanan, 14 Garrett Hill, London, SW1 8JR - 42
(6) Michael Suyama, Coventry House Miner Rd., London, EC2 7JR - 67
(8) Laura Callahan, 4726 - 11th Ave. N.E., Seattle, WA, 98105 - 104

Nice, but it can be improved. If you take a look at the log of the DataContext you can see what happens with previous queries :

One SQL statement is executed to retrieve a collection of all employees :

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

For each employee a new query will be executed to retrieve a collection of all orders of the employee. On the consumer caller side the Count will be calculated. So 6 queries are being executed and all data of the orders is returned.

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].[EmployeeID] = @p0

These many roundtrips to the database can cause a performance problem. But improving the performance is quite simple. Just make sure all data of child entities is included in the main LINQ to SQL query and not in the local LINQ query. In this case, you have to add Orders.Count in the main query.

var employeesServer2 = from emp in dc.Employees
                      where emp.LastName.Contains("a")
                      select new
                      {
                        Employee = emp,
                        OrderCount = emp.Orders.Count
                      };
 
var employeesLocal2 = from emp in employeesServer2.AsEnumerable()
                     select new
                     {
                       ID = emp.Employee.EmployeeID,
                       Name = emp.Employee.FirstName + " " + emp.Employee.LastName,
                       Address = emp.Employee.FullAddress,
                       OrderCount = emp.OrderCount
                     };
 
foreach (var emp in employeesLocal2)

Internally only one SQL statement (which contains a sub query with the Count aggregate function) will be executed on the database which makes it faster.

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

A second solution is to use the eager loading properties. I will explain this in an other article.

 

Change tracking

A LINQ to SQL DataContext will automatically track all changes and updates which are made to objects. Applying these changes to the database can be done by calling the SubmitChanges() method of the DataContext.

Sometimes it is handy to take a look at the entire change list of all insert/update/delete operations on the objects. Therefore you can call the GetChangeSet() method of the DataContext which will return following IList read-only collections :

Visual Studio 2008 RTM	Visual Studio Orcas Bèta 2
Inserts	AddedEntities
Updates	ModifiedEntities
Deletes	RemovedEntities

First let us add 3 objects; 2 employees and 1 customer :

NorthwindDataContext dc = new NorthwindDataContext();
 
// Add 2 employees and 1 customer
Employee employee1 = new Employee() { FirstName = "Jan", LastName = "Jansen", Address = "Kerkstraat 10", Country = "BE" };
// .Add() (bèta 2) 
// .InsertOnSubmit() (RTM) 
dc.Employees.InsertOnSubmit(employee1);
 
Employee employee2 = new Employee() { FirstName = "Piet", LastName = "Pieters", Address = "Grote Markt 95", Country = "BE" };
dc.Employees.InsertOnSubmit(employee2);
 
Customer customer = new Customer() { CompanyName = "Customer", ContactName = "Dirk Dirkx" };
dc.Customers.InsertOnSubmit(customer);

Call GetChangeSet() and check the number of added entities in the Inserts (AddedEntities) class

// How many entities have been added ?
// .AddedEntities (bèta 2)
// .Inserts (RTM)
Console.WriteLine("Number of added entities = {0}", dc.GetChangeSet().Inserts.Count.ToString());

You can also run through this generic list which is a IList<object>. If you would like to do anything with an object of this collection, you need to cast it down.

// Run through the list of added entities
IList<object> addedEntities = dc.GetChangeSet().Inserts;
foreach (var ent in addedEntities)
  Console.WriteLine("Added entity : {0}", ent.ToString());

Added entity : Northwind.Employee
Added entity : Northwind.Employee
Added entity : Northwind.Customer

Because it is a collection, you are able to implement a LINQ query. So let us filter the added employee entities :

// Query on the added entities and count the number of employee entities which have been added
// .AddedEntities (bèta 2)
// .Inserts (RTM)
Console.WriteLine("Number of added employees = {0}", dc.GetChangeSet().Inserts.Where(cs => cs is Employee).Count().ToString());
 
// Filter the employee entities which have been added
var addedEmployees = dc.GetChangeSet().Inserts.Where(cs => cs is Employee);
 
foreach (Employee emp in addedEmployees)

Added employee : Jansen, Jan
Added employee : Pieters, Piet

A SQL to LINQ entity does not have a kind of IsDirty property. If you would like to check if an object has been changed, you could use the Contains method on the Updates collection of the GetChangeSet.

var employeeCallahan = dc.Employees.Single(e => e.LastName == "Callahan");
 
Console.WriteLine("Is employee Callahan dirty : {0}", dc.GetChangeSet().Updates.Contains(employeeCallahan));
 
employeeCallahan.Notes += "Some extra notes";
 
Console.WriteLine("Is employee Callahan dirty : {0}", dc.GetChangeSet().Updates.Contains(employeeCallahan));

Is employee Callahan dirty : False
Is employee Callahan dirty : True

Once you know that an object is dirty, then it would be great to have acces to the original and new values. The new values are stored in the actual object. The GetOriginalEntityState method of the collection can be used to retrieve a copy (with new object identification number) of the original object.

var originalEmployeeCallahan = dc.Employees.GetOriginalEntityState(employeeCallahan);
Console.WriteLine("Original notes : {0}", originalEmployeeCallahan.Notes);
Console.WriteLine("New notes : {0}", employeeCallahan.Notes);

After putting all this together I created a simple Dump() extension method for the DataContext class which will create a list of all entity entries in the changeset.

public static class LINQtoSQLExtensionMethods
{
  /// <summary>
  /// Extension method for LINQ to SQL DataContext class
  /// </summary>
  /// <returns>String with tracking info about ChangeSet entries</returns>
  public static string Dump(this DataContext dc)
  {
    StringBuilder dump = new StringBuilder();
 
    var changeSetEntries = dc.GetChangeSet().Inserts
      .Union(dc.GetChangeSet().Updates)
      .Union(dc.GetChangeSet().Deletes);
    dump.AppendFormat("DataContext ChangeSet entries : # {0}\n", changeSetEntries.Count());
 
    foreach (var entry in dc.GetChangeSet().Inserts)
      dump.AppendFormat("\n- Added Entity : {0}", entry.ToString());
    foreach (var entry in dc.GetChangeSet().Updates)
      dump.AppendFormat("\n- Modified Entity : {0}", entry.ToString());
    foreach (var entry in dc.GetChangeSet().Deletes)
      dump.AppendFormat("\n- Deleted Entity : {0}", entry.ToString());
 
    return dump.ToString();
  }
}

 

 

Bulk operations

InsertAllOnSubmit and DeleteAllOnSubmit

Each LINQ to SQL entity has some methods to add and remove data :

Visual Studio 2008 RTM	Visual Studio Orcas Bèta 2
InsertOnSubmit(Entity)	Add(Entity)
InsertAllOnSubmit(IEnumerable<Entity>)	AddAll(IEnumerable<Entity>)
DeleteOnSubmit(Entity)	Remove(Entity)
DeleteAllOnSubmit(IEnumerable<Entity>)	RemoveAll(IEnumerable<Entity>)

The DeleteOnSubmit andDeleteAllOnSubmit methods can be used to pass a collection of entities which should be added or removed.

In the next example a list of 3 new employees is being created. This list is added to the database. Afterwards the same list is being removed from the database.

NorthwindDataContext dc = new NorthwindDataContext();
 
// Count employees and Belgian employees before adding and deleting
Console.WriteLine("(1) # employees = {0}", dc.Employees.Count().ToString());
Console.WriteLine("(1) # Belgian employees = {0}", dc.Employees.Count(e => e.Country == "BE").ToString());
 
// Create list with new employees
List<Employee> employeesToAdd = new List<Employee>();

employeesToAdd.Add(new Employee() { EmployeeID = 1000, FirstName = "Jan", LastName = "Jansen", Country = "BE" });
employeesToAdd.Add(new Employee() { EmployeeID = 1001, FirstName = "Piet", LastName = "Pieters", Country = "BE" });
employeesToAdd.Add(new Employee() { EmployeeID = 1002, FirstName = "John", LastName = "Johnson", Country = "BE" });
 
// Add all employees to the Employees entityset
// .AddAll() (bèta 2) 
// .InsertAllOnSubmit() (RTM) 
dc.Employees.InsertAllOnSubmit(employeesToAdd);
 
// Apply changes to database
dc.SubmitChanges();
 
// Count employees and Belgian employees after adding
Console.WriteLine("(2) # employees = {0}", dc.Employees.Count().ToString());
Console.WriteLine("(2) # Belgian employees = {0}", dc.Employees.Count(e => e.Country == "BE").ToString());
 
// Remove same list of employees from Employees entityset
// .RemoveAll() (bèta 2) 
// .DeleteAllOnSubmit() (RTM) 
dc.Employees.DeleteAllOnSubmit(employeesToAdd);
 
// Apply changes to database
dc.SubmitChanges();
 
// Count employees and Belgian employees after deleting
Console.WriteLine("(3) # employees = {0}", dc.Employees.Count().ToString());
Console.WriteLine("(3) # Belgian employees = {0}", dc.Employees.Count(e => e.Country == "BE").ToString());

(1) # employees = 9
(1) # Belgian employees = 0

(2) # employees = 12
(2) # Belgian employees = 3

(3) # employees = 9
(3) # Belgian employees = 0

For each added or removed entity an INSERT or DELETE SQL statement will be executed :

INSERT INTO [dbo].[Employees]([LastName], [FirstName], [Title], [TitleOfCourtesy],
  [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country],
  [HomePhone], [Extension], [Photo], [Notes], [ReportsTo], [PhotoPath]) 
VALUES (
  @p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14,
  @p15, @p16)

DELETE FROM [dbo].[Employees] 
WHERE ([EmployeeID] = @p0) AND ([LastName] = @p1)
  AND ([FirstName] = @p2) AND ([Title] IS NULL) AND ([TitleOfCourtesy] IS NULL) 
  AND ([BirthDate] IS NULL) AND ([HireDate] IS NULL) AND ([Address] IS NULL) 
  AND ([City] IS NULL) AND ([Region] IS NULL) AND ([PostalCode] IS NULL) 
  AND ([Country] = @p3) AND ([HomePhone] IS NULL) AND ([Extension] IS NULL) 
  AND ([ReportsTo] IS NULL) AND ([PhotoPath] IS NULL)

Update

It seems that there are no Update or UpdateAll methods avaible for multiple updates. So you first have to create a select query, run through the entities and update their properties.

In this example I first lookup the shipper Shippy. If it does not exist, then it will be created. All orders for customer QUICK which are shipped via Federal Shipping are being retrieved. Then we change the shipper to Shippy and submit the changes to the database. The second part of the example undoes these update operations.

NorthwindDataContext dc = new NorthwindDataContext();
 

// Get shipper Shippy or add it if it does not exists
Shipper shippy;
shippy = dc.Shippers.SingleOrDefault(s => s.CompanyName == "Shippy");
if (shippy == null)
{
  shippy = new Shipper() { CompanyName = "Shippy" };
  // .Add() (bèta 2) 
  // .InsertOnSubmit() (RTM) 
  dc.Shippers.InsertOnSubmit(shippy);
}
 
// Query all orders of customer QUICK which are shipped via Federal Shipping (ID=3)
var orders = from ord in dc.Orders
             where ord.CustomerID == "QUICK"
             && ord.Shipper.CompanyName == "Federal Shipping"
             select ord;
 

// Change shipper to Shippy
foreach (var o in orders)
  o.Shipper = shippy;
 
// Apply changes to database
dc.SubmitChanges();
 

// Undo changes by changing shipper back to Federal Shipping
var ordersUndo = from ord in dc.Orders
             where ord.CustomerID == "QUICK"
             && ord.Shipper.CompanyName == "Shippy"
             select ord;
 
foreach (var o in ordersUndo)
  o.Shipper = dc.Shippers.SingleOrDefault(s => s.CompanyName == "Federal Shipping");
 
dc.SubmitChanges(); 

Ok, this works fine, but isn't there an easier solution which needs less code ? Yes there is, you could write it as a lambda expression :

// Change shipper in all orders of customer QUICK which are shipped via Federal Shipping to Shippy
dc.Orders.Where(o => o.CustomerID == "QUICK" && o.Shipper.CompanyName == "Federal Shipping")
  .ForEach(o => o.Shipper = shippy);

On the 'Hooked To LINQ' Wiki website (http://hookedonlinq.com/UpdateOperator.ashx) I found a nice Update extension method created by Troy Magennis. Just include this Update extension in one of your assemblies. It is almost the same as the lamba expression but it will return the number of affected records/objects.

// Change shipper in all orders of customer QUICK which are shipped via Federal Shipping to Shippy
dc.Orders.Where(o => o.CustomerID == "QUICK" && o.Shipper.CompanyName == "Federal Shipping")
  .Update(o => o.Shipper = shippy);
 
// Undo changes by changing shipper back to Federal Shipping
dc.Orders.Where(o => o.CustomerID == "QUICK" && o.Shipper.CompanyName == "Shippy")
  .Update(o => o.Shipper = dc.Shippers.SingleOrDefault(s => s.CompanyName == "Federal Shipping"))

Using the Update extension makes your code more readable but the underlying result will be still the same. All data will be retrieved (SELECT) and then an UPDATE SQL statement will be executed for each entity.

UPDATE [dbo].[Orders]
SET [ShipVia] = @p13
WHERE ([OrderID] = @p0) AND ([CustomerID] = @p1) AND ([EmployeeID] = @p2) 
  AND ([OrderDate] = @p3) AND ([RequiredDate] = @p4) AND ([ShippedDate] = @p5) 
  AND ([ShipVia] = @p6) AND ([Freight] = @p7) AND ([ShipName] = @p8) 
  AND ([ShipAddress] = @p9) AND ([ShipCity] = @p10) AND ([ShipRegion] IS NULL) 
  AND ([ShipPostalCode] = @p11) AND ([ShipCountry] = @p12)

So, hopefully Microsoft will add their own Update method which has an option to apply the changes only in the database by using only one UPDATE SQL statement which will be a lot faster.

 

Summary

I really like LINQ to SQL. I'm sure there are better third-party ORM solutions available then LINQ to SQL, but it is great that the LINQ technologies are provided everywhere in the .NET 3.5 framework. There are still some issues (non-mapped properties in projections, database-side updates, multitier approach, class diagram visualization, ...) but I really believe that LINQ to SQL will evolve to a powerful but easy ORM technology. I am already using it in a small ASP.NET project. I hope to test other features in the near future and publish my experiences in some new articles (multitier approach, inheritance, concurrency conflicts, eager loading, dynamic where-clauses, ...). I'm also looking forward to the beta versions of the Entity Framework which will be the big brother of LINQ to SQL.

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