Version française de cet article

For a change, we are going to play with a technology that is not mature, and with which we are far from developing production applications... But we have to admit that we enjoy playing with the preview versions of these upcoming brand new toys.

So, before starting this article, here is a small mandatory warning:

This article demonstrates the use of technologies and tools that are available today only as pre-alpha-preview-etc versions, and no reliable date has been announced yet for the final release. It’s not even guaranteed that we’ll see it one day!

This being said, well-informed sources have hinted that all this will be part of the Visual Studio Orcas/.NET 3.0/C# 3.0/VB 9.0 band-wagon, and as such should not be expected before 2007 at best.
Now that you have been warned, let’s get started!

Agenda

We'll see:

• How to create an object graph from a database using DLINQ
• The differences between immediate loading of data and deferred (lazy) loading
• How to create views on objects in memory with LINQ (sorting, grouping, projections)
• How to reproduce these operations without LINQ
• How to filter and mix data coming from a database with XML data using XLINQ
• How to save a data set into an XML document
• How to perform transformations on data in memory to get HTML

The architecture of the example is very simple:

1. a database
2. a data-access layer
3. a presentation layer

Reminders on Linq, DLinq, XLinq

The Linq project offers querying features integrated right into programming languages. This will allow executing strongly-typed queries on sources such as databases, XML documents or even object graphs in memory. This technology relies on innovations on programming languages like C# 3 or VB.NET 9, and it will be ported to other .NET languages like Delphi.

Note: The examples contained in this article are in C#, but can easily be ported to VB.NET 9.

DLinq is an extension to Linq that allows querying a database (only SQL Server for the moment) and do object-relational mapping.
XLinq is an extension to Linq that allows querying XML documents, as well as creating or transforming XML.

You will be able to learn more on these technologies by referring to the links at the end of this article.

Note: This article is based on the technical preview from the PDC 2005 and new features not available at that time are likely to appear afterwards.

Data-Access Layer

We will now begin constructing our example application, starting with the data-access layer.

DLinq includes a command-line tool named SQLMetal that generates code from a database. This generated code contains the classes that represent the database and its tables as .NET objects decorated with the metadata required by DLinq. This tool greatly simplifies things, and this is what we’ll use here. You could however create your own object model by hand using the classes and attributes provided by the DLinq framework if you need more flexibility.

In our case, we will use the Northwind database provided with SQL Server.
Here is how the generated code looks like:

Here is our DAO (Data-Access Object)::

Each method demonstrates a distinct way of loading data:

1. ListCustomerByCountry_Deferred uses deferred (lazy) loading, which means that this DAO’s method returns an enumeration of clients, but the data will be loaded on-demand as we try to access it. This means that calls to the database may happen outside the method invocation.
2. ListCustomerByCountry_ImmediateCustomer allows loading immediately the data on clients, but the data on details (Orders) will be loaded on-demand later (deferred loading).
3. ListCustomerByCountry_ImmediateOrders suppresses the deferred loading of both the clients and their orders: all the data is loaded immediately before being returned by the method as an enumeration. We get a datapacket ready for use in a disconnected mode.

Here is what is performed in each case:

1. We just execute a simple query:

   return
     from customer in _Database.Customers
     where customer.Country == country
     select customer;
   
   This creates what is called a sequence, on which we’ll be able to apply transformation or enumeration operations.
   This code shows that the default behavior is deferred loading.
    

2. We transform the enumeration into a list:

   return
     (from customer in _Database.Customers
     where customer.Country == country
     select customer).ToList();
   
   ToList() loops on the enumeration based on the sequence and returns a generic List<T> that contains the elements of the sequence.
    

3. We state that we wish the orders and their details to be included in the sequence, then we create a list:

   return
     (from customer in _Database.Customers
     where customer.Country == country
     select customer).Including(customer => customer.Orders.Including(order => order.OrderDetails)).ToList();

   This is the solution that should be used when we wish to work in a disconnected, semi-disconnected or remote mode. This also allows limiting the calls to the database.

To be able to see when the calls to the database are made, we will trace two things: the SQL queries sent by DLinq to the database, and when the executions or our methods begin and end.

In order to trace the SQL calls, we will ask the Northwind object – generated by SQLMetal – to log information to the console. This can be done like this:

_Database = new Northwind(_ConnectionString) { Log = Console.Out };

Note that we use a new syntaxe, named object initialization expression, which can be used to initialize the properties of an object. This is not particularly useful here, but it can be useful in other contexts like the construction of an object in Linq queries.
The code we’ve just used is equivalent to the following one:

_Database = new Northwind(_ConnectionString);
_Database. Log = Console.Out;

To trace the calls to our methods, we will use a very simple utility method:

static public void LogMethod(bool begin)
{
  MethodBase method;

  method = new System.Diagnostics.StackFrame(1).GetMethod();
  Console.WriteLine((begin ? "Begin" : "End")+" - "+
    method.DeclaringType.FullName+"."+method.Name);
}

Sorry, no AOP this time ;-)

We will study the behavior of our various methods with the following code:

IEnumerable<nwind.Customer> customers;

using (var dao = new CustomerDao())
{
  customers = dao.ListCustomersByCountry_ImmediateOrders("Brazil");
}

foreach (nwind.Customer customer in customers)
{
  Console.WriteLine("Customer: " + customer.CustomerID);
  foreach (nwind.Order order in customer.Orders)
    Console.WriteLine("\tOrder: " + order.OrderID);
}

This code just retrieves the list of clients, then browses this list, as well as each client’s orders.
Here is what we get with ListCustomersByCountry_Deferred:

DataAccess.CustomerDao.ListCustomersByCountry_Deferred - Begin
DataAccess.CustomerDao.ListCustomersByCountry_Deferred - End
SELECT [t0].[Address], [t0].[City], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Country], [t0].[CustomerID], [t0].[Fax], [t0].[Phone], [t0].[PostalCode], [t0].[Region]
FROM [Customers] AS [t0]
WHERE [t0].[Country] = @p0

Customer: AROUT
SELECT [t0].[CustomerID], [t0].[EmployeeID], [t0].[Freight], [t0].[OrderDate], [t0].[OrderID], [t0].[RequiredDate], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipCountry], [t0].[ShipName], [t0].[ShippedDate], [t0].[ShipVia], [t0].[ShipPostalCode], [t0].[ShipRegion]
FROM [Orders] AS [t0]
WHERE [t0].[CustomerID] = @p0

  Order: 10355
  Order: 10383
...
Customer: BSBEV
SELECT [t0].[CustomerID], [t0].[EmployeeID], [t0].[Freight], [t0].[OrderDate], [t0].[OrderID], [t0].[RequiredDate], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipCountry], [t0].[ShipName], [t0].[ShippedDate], [t0].[ShipVia], [t0].[ShipPost
alCode], [t0].[ShipRegion]
FROM [Orders] AS [t0]
WHERE [t0].[CustomerID] = @p0

  Order: 10289
  Order: 10471
  ...
...

We can see here that no query to the database is executed during the call to the method. The queries are executed afterwards, for the list of clients, then for the list of each client’s orders.

Here is what we get with ListCustomersByCountry_ImmediateCustomers:

DataAccess.CustomerDao.ListCustomersByCountry_ImmediateCustomers - Begin
SELECT [t0].[Address], [t0].[City], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Country], [t0].[CustomerID], [t0].[Fax], [t0].[Phone], [t0].[PostalCode], [t0].[Region]
FROM [Customers] AS [t0]
WHERE [t0].[Country] = @p0

DataAccess.CustomerDao.ListCustomersByCountry_ImmediateCustomers - End
Customer: AROUT
SELECT [t0].[CustomerID], [t0].[EmployeeID], [t0].[Freight], [t0].[OrderDate], [t0].[OrderID], [t0].[RequiredDate], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipCountry], [t0].[ShipName], [t0].[ShippedDate], [t0].[ShipVia], [t0].[ShipPostalCode], [t0].[ShipRegion]
FROM [Orders] AS [t0]
WHERE [t0].[CustomerID] = @p0

  Order: 10355
  Order: 10383
  ...
Customer: BSBEV
SELECT [t0].[CustomerID], [t0].[EmployeeID], [t0].[Freight], [t0].[OrderDate], [t0].[OrderID], [t0].[RequiredDate], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipCountry], [t0].[ShipName], [t0].[ShippedDate], [t0].[ShipVia], [t0].[ShipPostalCode], [t0].[ShipRegion]
FROM [Orders] AS [t0]
WHERE [t0].[CustomerID] = @p0

  Order: 10289
  Order: 10471
  ...
...

In this case, only the query to get the list of the clients is executed immediately, within the DAO's method call. A new query is executed later to get the list of the commands of each client.

Here is what we get with ListCustomersByCountry_ImmediateOrders:

DataAccess.CustomerDao.ListCustomersByCountry_ImmediateOrders - Begin
SELECT [t0].[Address], [t0].[City], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Country], [t0].[CustomerID], [t0].[Fax], (
SELECT COUNT(*) AS [C0]
FROM [Orders] AS [t1]
WHERE [t1].[CustomerID] = [t0].[CustomerID]
) AS [Orders], [t0].[Phone], [t0].[PostalCode], [t0].[Region]
FROM [Customers] AS [t0]
WHERE [t0].[Country] = @p0
ORDER BY [t0].[CustomerID]

SELECT [t1].[CustomerID], [t1].[EmployeeID], [t1].[Freight], [t1].[OrderDate], (
SELECT COUNT(*) AS [C1]
FROM [Order Details] AS [t2]
WHERE [t2].[OrderID] = [t1].[OrderID]
) AS [OrderDetails], [t1].[OrderID], [t1].[RequiredDate], [t1].[ShipAddress], [t1].[ShipCity], [t1].[ShipCountry], [t1].[ShipName], [t1].[ShippedDate], [t1].[ShipVia], [t1].[ShipPostalCode], [t1].[ShipRegion]
FROM [Customers] AS [t0], [Orders] AS [t1]
WHERE ([t0].[Country] = @p1) AND ([t1].[CustomerID] = [t0].[CustomerID])
ORDER BY [t0].[CustomerID], [t1].[OrderID]

SELECT [t2].[Discount], [t2].[OrderID], [t2].[ProductID], [t2].[Quantity], [t2].[UnitPrice]
FROM [Customers] AS [t0], [Orders] AS [t1], [Order Details] AS [t2]
WHERE ([t0].[Country] = @p2) AND ([t1].[CustomerID] = [t0].[CustomerID]) AND ([t2].[OrderID] = [t1].[OrderID])
ORDER BY [t0].[CustomerID], [t1].[OrderID], [t2].[OrderID], [t2].[ProductID]

DataAccess.CustomerDao.ListCustomersByCountry_ImmediateOrders - End
Customer: AROUT
  Order: 10355
  Order: 10383
  ...
Customer: BSBEV
  Order: 10289
  Order: 10471
  ...
...

In this later case, the dialog between the database happens entirely within the invocation of the method of the DAO. In addition to the fact that the whole of the data is fetched at once, it's worth noting that only three queries are executed, instead if 1 + 1 by client + 1 by order (if we access to the commands' details), hence performance improvements and reduced communication with the database.
This is the behavior we wish to achieve in the case of this article: we want to get all the data to avoid calling the database later on.

Of course, each of these behaviors matches different situations, and none is paramount. But you should be aware of what happens behind the scene in the different cases, otherwise get ready for surprises and big performance problems.

Nota Bene: this is nothing new and already works with the object-relational mapping tools available on the market.

Presentation layer

Creating views in memory

From our (really light!) presentation layer, we will create views on the data provided by the data-access layer. We will then keep these data in memory.

Sorting

Let's start by sorting data.

What's simplest is to sort based on a given property, here CustomerName:

void DisplayCustomersSortedByName(IEnumerable<nwind.Customer> customers)
{
  // Sort
  var result = from customer in customers
      orderby customer.CompanyName
      select customer;

  // Display
  DisplayCustomers(customers);
}

What if we want to sort on another property? We encounter one of the limits of the current implementation: queries are not dynamic, but hard coded. This means that the field on which the sorting operation, is performed cannot be specified dynamically. At least not easily, for the moment.
However, we can use a workaround based on a lambda expression. The following method for example takes a function as a parameter, which is used to define how to perform the sorting operation:

void DisplayCustomersSorted(IEnumerable<nwind.Customer> customers,
        Func<nwind.Customer, Object> orderKeySelector)
{
  // Sort
  var result = customers.OrderBy(orderKeySelector);

  // Display
  DisplayCustomers(customers);
}

The function provided as orderKeySelector takes a client as a parameter and returns the value of one of this client's parameter.
This method can be invoked like this:

DisplayCustomersOrdered(delegate (nwind.Customer customer) { return customer.CompanyName; });

Or more simply using a lambda expression:

DisplayCustomersOrdered(customer => customer.CompanyName);

If we want to choose the property dynamically, we have to use another trick. We can for example use the following function:

Object GetOrderKey(nwind.Customer customer)
{
  switch (_OrderKey)
  {
    case "CompanyName":
      return customer.CompanyName;
    case "City":
      return customer.City;
    default:
      return customer.CustomerID;
  }
}

and write:

_OrderKey = "ContactName";
DisplayCustomersOrdered(GetOrderKey);

Grouping

Here is how to group by city:

void DisplayCustomersGroupedByCity(IEnumerable<nwind.Customer> customers)
{
  // Create groups sorted by city name
  var result = from customer in customers
      group customer by customer.City into cities
      orderby cities.Key
      select cities;

  // Display
  foreach (var group in result)
  {
    Console.WriteLine(group.Key);
    foreach (var customer in group.Group)
      Console.WriteLine("\t"+customer.CompanyName);
  }
}

Projection

We will now create a view presenting the list of the commands of all the clients, combined with information on the associated client such as his name or city.

void DisplayOrders(IEnumerable<nwind.Customer> customers)
{
  // Create view
  var result = from customer in customers
      from order in customer.Orders
      orderby order.OrderDate
      select new {
        order.OrderID,
        order.OrderDate,
        CustomerName = customer.CompanyName,
        CustomerCity = customer.City,
      };

  // Display
  ObjectDumper.Write(result);
  /* Same as:
  foreach (var order in result)
    Console.WriteLine(
      "OrderID="+order.OrderID+
      "\tOrderDate="+order.OrderDate.Value.ToShortDateString()+
      "\tCustomerName="+order.CustomerName+
      "\tCustomerCity="+order.CustomerCity);
  */
}

We could also imagine a view of the clients with the number of commands and the total amount for each client:

void DisplayCustomersWithOrderData(IEnumerable<nwind.Customer> customers)
{
  // Create view
  var result = from customer in customers
      orderby customer.CompanyName
      select new {
        customer.CustomerID,
        customer.CompanyName,
        OrderCount = customer.Orders.Count,
        OrderTotal = customer.Orders.Sum(order => order.OrderDetails.Sum(detail => detail.Quantity * detail.UnitPrice))};

  // Display
  ObjectDumper.Write(result);
  /* Same as this:
  foreach (var customer in result)
    Console.WriteLine(
      "ID="+customer.ID+
      "\tName="+customer.Name+
      "\tOrderCount="+customer.OrderCount+
      "\tOrderTotal="+customer.OrderTotal);
  */
}

Without Linq and C# 3.0

To get an idea on Linq's value in the cases we have presented, we will now see of to achieve the same results without Linq, using only C# 2.0.

Grouping

Here is the code to perform grouping without Linq:

void DisplayCustomersGroupedByCity_NoLinq(IEnumerable<nwind.Customer> customers)
{
  IDictionary<String, IList<nwind.Customer>> cities;

  // Create groups sorted by city name
  cities = new SortedDictionary<String, IList<nwind.Customer>>();
  foreach (nwind.Customer customer in customers)
  {
     IList<nwind.Customer> cityCustomers;

    if (!cities.TryGetValue(customer.City, out cityCustomers))
    {
      cityCustomers = new List<nwind.Customer>();
      cities[customer.City] = cityCustomers;
    }
    cityCustomers.Add(customer);
  }

  // Display
  foreach (KeyValuePair<String, IList<nwind.Customer>> city in cities)
  {
    Console.WriteLine(city.Key);
    foreach (var customer in city.Value)
      Console.WriteLine("\t"+customer.CompanyName);
  }
}

This code is more complex, but remains accessible. We can easily imagine that things can get more complicated with more complex queries.

Projection

Here is how to create a view on the commands without Linq:

void DisplayOrders_NoLinq(IEnumerable<nwind.Customer> customers)
{
  List<OrderView> orders;

  // Create view
  orders = new List<OrderView>();
  foreach (nwind.Customer customer in customers)
  {
    foreach (nwind.Order order in customer.Orders)
      orders.Add(new OrderView(order.OrderID, order.OrderDate.Value, customer.CompanyName, customer.City));
  }

  // Sort
  orders.Sort(new Comparison<OrderView>(delegate (OrderView view1, OrderView view2) {
    return view1.OrderDate.CompareTo(view2.OrderDate);
  } ));

  // Display
  ObjectDumper.Write(orders);
  /* Same as:
  foreach (OrderView order in orders)
    Console.WriteLine(
      "OrderID="+order.OrderID+
      "\tOrderDate="+order.OrderDate.ToShortDateString()+
      "\tCustomerName="+order.CustomerName+
      "\tCustomerCity="+order.CustomerCity);
  */
}

You'll note that we resort to a class named OrderView, which we have to create of course...
Refer the complete source code for details.

XLinq

XLinq can be used for several things. Here are some examples.

Filtering and mixing data from a database with XML data

void DisplayWebCustomers(IEnumerable<nwind.Customer> customers)
{
  const String WebCustomersData = @"<WebCustomers>
      <Customer ID='FAMIA' EMail='info@arquibaldo.br' WebSite='http://arquibaldo.br' />
      <Customer ID='HANAR' EMail='me@hanari.com' WebSite='http://www.hanari.com' />
      <Customer ID='QUEDE' EMail='him@freemails.br' WebSite='http://www.brazilnet/delicia' />
      <Customer ID='WELLI' EMail='sales@wellington.br' WebSite='http://www.wellington.br' />
   </WebCustomers>";

  // Load XML
  var webCustomers = XElement.Parse(WebCustomersData);

  // Create view
  var result = from customer in customers, webCustomer in webCustomers.Elements()
      where customer.CustomerID == webCustomer.Attribute("ID").Value
      select new {
        ID = customer.CustomerID,
        Name = customer.CompanyName,
        EMail = webCustomer.Attribute("EMail").Value,
        WebSite = webCustomer.Attribute("WebSite").Value};

  // Display
  ObjectDumper.Write(result);
}

Only the clients present both in the XML and the database are kept, and enriched with an e-mail address and a web site.

Persisting a set of data in the XML format

The following code generates XML, which we can transform, save, transfer...:

void DisplayAsXml(IEnumerable<nwind.Customer> customers)
{
  // Create XML
  var xml = new XElement("Customers",
              from customer in customers
              where customer.City == "Rio de Janeiro"
              select new XElement("Customer",
                 new XAttribute("ID", customer.CustomerID),
                 new XAttribute("Name", customer.CompanyName)
              )
            );

  // Display
  Console.WriteLine(xml);
}

Generating XHTML

The same way we can generated XML data, we can generated XHTML to get a web presentation of data:

void DisplayAsXhtml(IEnumerable<nwind.Customer> customers)
{
  #region Create HTML

  var header = new [] {
    new XElement("th", "Customer id"),
    new XElement("th", "Customer name"),
    new XElement("th", "Country"),
    new XElement("th", "City"),
  };

  var rows = from customer in customers
      select new XElement("tr",
        new XElement("td", customer.CustomerID),
        new XElement("td", customer.CompanyName),
        new XElement("td", customer.Country),
        new XElement("td", customer.City)
      );

  var html = new XElement("html",
    new XElement("body",
      new XElement("table",
        new XAttribute("border", 1),
        header,
        rows
      )
    )
  );

  #endregion Create HTML

  #region Display

  Console.WriteLine(html);

  String filename = Path.ChangeExtension(Path.GetTempFileName(), "html");
  File.WriteAllText(filename, html.ToString(), Encoding.UTF8);
  System.Diagnostics.Process.Start(filename);

  #endregion Display
}

Conclusion

We have just scratched the surface of the possibilities offered by Linq et al., but we have been able to get an overview of some of the services it will provide. This subject will become rich enough to publish many other articles about it, especially considering that these technologies will continue to evolve before their official release.
Update: I'm currently working on a book that will cover Linq, DLinq and XLinq! Keep an eye on my weblog to know when it becomes available.

Linq introduces new concepts directly into the languages. You should note that for the moment, only the languages have been enriched, while the .NET platform did not need to evolve. The compiler used is C# 3.0, but the required framework version is still an unmodified version 2.0. We may however see evolutions happen on the framework later on as new features are added, and maybe Linq won't work on .NET 2.0 in the end.
We can notice how the complexity is increasing, with new keywords and many new concepts to grasp.

If we take a look into the future, we can imagine Microsoft integrating Linq with Atlas to enable queries on data right in web browsers without having to call the server... Before we get there, you can user TrimQuery and AMASS.

Who is Fabrice Marguerie?
Fabrice Marguerie is a .NET architect and a Microsoft MVP. Fabrice works as a consultant on design and implementation missions. He writes a weblog in English: http://weblogs.asp.net/fmarguerie and administrates the web site: http://sharptoolbox.com
Fabrice created metaSapiens, a company providing development tools and services.