Linq to SQL – DataLoadOptions (Previously DataShape)

DataLoadOptions allows you to specify which child entities you automatically want to be loaded with their parent entity. By defining how the data should be loaded, we can reduce the number of requests made to the database to retrieve information and thus improving performance of our application.
Take this code for example:

var query = from c in db.Customers
                  select c;

foreach (var c in query)
{
   Console.WriteLine(“{0}, Phone: {1} has {2} orders”, c.ContactName, c.Phone, c.Orders.Count);
}

This is a simple query which outputs the name, telephone number and the number of orders they have to the console.  There doesn’t look to be anything wrong with this, however if we run SQL Profiler that a large number of queries are being executed.

The query process is that all the customers are loaded into memory, and then for each customer a query is made to the orders table to return how many records they have which is then counted in memory.  Having queries like this will soon put pressure on your database.

This is where DataLoadOptions plays a part.  We can define that when a Customer object is loaded from the database, that all the Orders for that Customer should also be loaded with this code.

DataLoadOptions options = new DataLoadOptions();
options.LoadWith(Customer => Customer.Orders);
db.LoadOptions = options;
var query……

Now if we re-run the application, instead of having a query for each customer to find out the number of orders, we just have a single query being executed which returns the Customer and Order information.

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]
ORDER BY [t0].[CustomerID], [t1].[OrderID]

Changing the foreach loop to output the order information will still only result in a single query being executed on the server.

foreach (var c in query)
{
    Console.WriteLine(“{0}, Phone: {1}”, c.ContactName, c.Phone);
    foreach (var o in c.Orders)
    {
        Console.WriteLine(“OrderID: {0}”, o.OrderID);
    }
}

However, it doesn’t solve all of our problems.  If we wanted a query which provided details on the Order Lines within the ProductID being outputted, we could write something like this:  

foreach (var c in query)
{
    Console.WriteLine(“{0}, Phone: {1}”, c.ContactName, c.Phone);
    foreach (var o in c.Orders)
    {
        Console.WriteLine(“Order Details for OrderID: {0} with {1} lines”, o.OrderID, o.Order_Details.Count);

        foreach (var od in o.Order_Details)
        {
            Console.WriteLine(“Product ID: {0}”, od.ProductID);
        }
    }
}

You would have thought that by adding the following code we would be able to limit the number of queries:

options.LoadWith(Order => Order.Order_Details);

Sadly, this is not the case and will still cause a query to be executed for each customer, in fact it is actually for each order. According to Michael Pizzo on the product team, this is by design with this explanation:

In LINQ to SQL, in order to reduce the complexity and amount of data returned by the join query required to do span, only one association in any given query is actually spanned in. LINQ to SQL picks the deepest level association in the query to do the join in order to minimize the number of queries generated.  So, in this case, by adding a span between orders and order_details, you are basically “hiding” any span of orders.

DataLoadOptions can improve the performance of your application and something which you should remember when writing Linq to SQL queries.  However, if we are going more than one layer deep, you are still going to have a huge number of queries being executed.  Remember to run SQL Profiler to make sure your application isn’t going to take out your production server(s).

Technorati Tags: , ,

Extending DataContext using Extension methods

Extension methods are great as they allow you to extend any class within C# 3.0 with the instance of the class being passed in as a parameter. 

In C# 2.0 we had an initial way with partial classes which allows us to extend classes with our own custom logic, however it only worked if the other class was marked as partial.

I talked before how we can extend the DataContext created by the designer (NorthwindDataContext) by using partial classes,  however if we wanted a method on all of the DataContext object in our system, we would have to create a partial class for each of them separate. This would result in a lot of duplicate code and a maintenance nightmare.

partial class DataClasses1DataContext { //… }

partial class DataClasses2DataContext { //… }

The other solution would be to add a partial class to the DataContext and have it be inherited by the other objects in the system, however this doesn’t work as the DataContext class is not partial.

The answer is, Extension methods. Extension methods allow us to extend any object in the system, even if it is marked as sealed.  They require the following:

  • Be in their own public static class
  • Method be public static
  • use the ‘this’ keyword with the object we want to extend as the first parameter.  Other parameters can be added after this.
  • Accessible from the calling object.  If they are in there own namespace, the namespace needs to be referenced via a using directive.

Using this, we can extend the DataContext class and have all of our own DataContext’s in the system implement our new functionality.

The following code could be used and access any information from the DataContext as a parameter:

namespace MyExtension
{
    public static class Extension
    {
        public static void PrintConnection(this DataContext dc)
        {
            Console.WriteLine(dc.Connection.ConnectionString);
        }
    }
}

This could then be accessed by any child object in the system like this:

DataClasses1DataContext db = new DataClasses1DataContext();
db.PrintConnection();

We are limited to the public interface of DataContext, but this is still a very useful technique. This also applies to any other object within .Net.

Technorati Tags: , ,

Partial Methods in Linq

Following on from my previous post, I noticed Linq takes advantage of the Partial Methods feature in the language so I thought I would take a closer look.

Within VS Beta 2, the DataContext and database objects contain a number of partial methods to allow you to hook into various methods which are called at certain points within the execution.  All of the definitions are in the datacontext.designer.cs file in a region called Extensibility Method Definitions.

The standard methods included within the data context are:

  • partial void OnCreated();
  • partial void InsertCustomer(Customer instance);
  • partial void UpdateCustomer(Customer instance);
  • partial void DeleteCustomer(Customer instance);

OnCreated() is called when the DataContext object is created, while the others are called on SubmitChanges().  Once SubmitChanges() are called, the DataContext will start processing any changes and calling the methods, if they have been implemented.  HOWEVER I found that if you extend one of the methods, then the action would never be passed to the database. This must be a bug, so something to be aware of.  It might be by design and you use this to override the logic – its not very clear.  If they do override the functionality and its not a bug, I don’t like the fact that there is no distinction between a method which is a hook and a method which overrides functionality. If it was something like partial override void …. then it would be a lot better that how it is at the moment.

The database entities also have extension points.

  • partial void OnLoaded();
  • partial void OnValidate();
  • partial void OnCreated();
  • partial void OnOrderIDChanging(int value);
  • partial void OnOrderIDChanged();

These partial methods are a little bit more interesting.  OnCreated is called when an object is initialised.  When loading from a database, OnCreated is called first, then OnLoaded is called.  OnXChanging() is called when the property is changed, just before the value is stored in the internal variable, not when SubmitChanges is called. OnXChanged() is called after the value is stored.

The most important method is OnValidate() which is called just before the action is passed to the database.  Here, you can add custom/business logic to verify that the object (can access everything as you are inside the object, for example this.OrderID) is correct and valid before being saved.  If there is a problem, you can throw an exception and catch it by putting a try/catch around db.SubmitChanges(). Very useful feature.

 

All of the code I wrote can be found here : C# Sample Code

Technorati Tags: , ,

Partial Methods in C# 3.0 and VB.net 9.0

Partial methods are a new language feature in both VB.net and C# and are designed to enable lightweight event handling and hooks into the class.  The code samples are in C#, but I have provided a link at the bottom for the VB.net version – the principals are the same.

Few important points:

  • Partial methods can only be defined or implemented within a partial class.
  • They must return void however can accept arguments (ref parameters but not out parameters).
  • Must be private

Consider the following C# code which has a simple getter and setter for the name of a person.

partial class Person
{
        string name;

        public string Name
        {
            get { return name; }

            set
            {
                OnNameChanging(value);
                name = value;
                OnNameChanged();
            }
        }

        partial void OnNameChanging(string name);

        partial void OnNameChanged();

}

When a name is set, a call is made to the OnNameChanging method, passing in the value as a parameter. After the name has changed the OnNameChanged method is called.  But these methods are just placeholders/interfaces which could be implemented by a developer.

In a separate partial class we can then implement this functionality which will handle what we want to happen when the methods are called.

partial class Person
{
        partial void OnNameChanged()
        {
            Console.WriteLine(“OnNameChanged()”);
        }

        partial void OnNameChanging(string name)
        {
            Console.WriteLine(“OnNameChanging(string name)”);
            Console.WriteLine(name);
        }
}

Now, when we compile, everything will be merged and act as a single class. If we then set the name on the object, the following is wrote out to the console

OnNameChanging(string name)
Ben Hall
OnNameChanged()

But what what happens if no partial class implements the method body for a partial method? Well, the compiler removes calling reference from the code and does not get compiled. If the two methods where not implemented, the setter would look like this:

set
{
  name = value;
}

The result is that it is really effective to include calls to partial methods within generated code, like from a Linq DataContext, because if they are not taken advantage of then they are removed.

Looking forward to seeing how these will be used.

Links

C# Sample | VB.net Sample

http://blogs.msdn.com/wesdyer/archive/2007/05/23/in-case-you-haven-t-heard.aspx
http://blogs.msdn.com/vbteam/archive/2007/03/27/partial-methods.aspx

Technorati Tags: , ,