Skip to content

LINQ to SQL tips and tricks #1  

Being on the inside of a product team often leads to uncovering or stumbling upon lesser known techniques and here are a few little nuggets I found interesting – I have more if there is interest.

Loading a delay-loaded property

LINQ to SQL lets you specify that a property is delay-loaded meaning that it is not normally retrieved as part of normal query operations against that entity. This is particularly useful for binary and large text fields such as a photo property on an employee object that is rarely used and would cause a large amount of memory to be consumed on the client not to mention traffic between the SQL and application.

There are times however when you want all these binaries returned in a single query, say for example returning all the photos for the company photo intranet page:

var db = new NorthwindContext();
var loadOptions = new DataLoadOptions();
loadOptions.LoadWith<Employee>(e => e.Photo);
db.LoadOptions = loadOptions;

Multiple entity types from a single stored procedure

It is actually possible to return multiple entity types from a single stored procedure in LINQ to SQL although this is not well known as the LINQ to SQL designer doesn’t actually support it. Indeed to generate the code it is necessary to hand-edit the DBML and then use SQL Metal (or my T4 template) to generate the required method signature.

In fact it is just much easier to write the code yourself and add it to the non-generated portion of your data context. If you imagine a stored procedure GetStaticData that looks like this:

CREATE PROCEDURE GetStaticData AS
  SELECT * FROM Region
  SELECT * FROM Categories
  SELECT * FROM Territories

Then all you need to do is write a method signature that looks like this (the sequence of result type attributes must match the order in the stored procedure):

[Function(Name=@"dbo.DynamicContractsActiveBetween")]
[ResultType(typeof(Region))]
[ResultType(typeof(Category))]
[ResultType(typeof(Territory))]
public IMultipleResults GetStaticData() {
   return (IMultipleResults) ExecuteMethodCall(this, (MethodInfo) MethodInfo.GetCurrentMethod()).ReturnValue;
}

Intercepting create, update and delete operations

There are times it is useful to be able to listen in to when these events happen and perform your own logic, perhaps auditing or logging for some scenarios. The easiest way to do this is to implement some specially-named methods on your data context, perform your action and then to dispatch the call back to LINQ to SQL.

The format of these specially-named methods is [Action][Entity] and then you should pass back control to LINQ to SQL using ExecuteDynamic[Action] where [Action] is either Insert, Update or Delete. One example of such usage might be:

partial class NorthwindContext {
   partial void InsertEmployee(Employee instance) {
      instance.CreatedBy = CurrentUser;
      instance.CreatedAt = DateTime.Now;
      ExecuteDynamicInsert(instance);
   }

   partial void UpdateEmployee(Employee instance) {
      AuditEmployeeOwnerChange(instance);
      instance.LastModifiedAt = DateTime.Now;
      ExecuteDynamicUpdate(instance);
   }

   partial void DeleteEmployee(Employee instance) {
      AuditDelete(instance, CurrentUser);
      ExecuteDynamicDelete(instance);
   }
}

Check out part 2 of LINQ to SQL tips

[)amien

5 responses  

  1. Cool. Keep the tricks coming.

    Would also be cool if you could reveal some of the upcoming features/changes to “LINQ to SQL” ?

    Ibleif – March 18th, 2009
  2. pingback

    […] Guard has two nice posts with LINQ tips. In part one, he covers eager loading, retrieving multiple entity types from a single stored procedure, […]

    Summary 20.04.2009 « Bogdan Brinzarea’s blogApril 21st, 2009
  3. The interception feature is awesome, I can’t believe that one flew under the radar. I’m not sure how to query for that in Google though, as there are allot of people who’ve rolled their own auditing functionality for L2S, can you point to some documentation or give a more complete example. I would appreciate that very much, thanks.

    Michael D. Hall – May 18th, 2009
  4. @Michael – searching for “Linq to SQL Extensibility” is a good start – that’s the name given to those methods in the default generated code:

    #region Extensibility Method Definitions
    partial void OnCreated();
    partial void InsertsiteContent_Album(siteContent_Album instance);
    partial void UpdatesiteContent_Album(siteContent_Album instance);
    partial void DeletesiteContent_Album(siteContent_Album instance);
    #endregion

    Alternatively, ScottGu calls them “Custom Entity Insert/Update/Delete Method Validation” in his post: http://weblogs.asp.net/scottgu/archive/2007/07/11/linq-to-sql-part-4-updating-our-database.aspx

    Zhaph - Ben DuguidMay 20th, 2009
  5. @Zhaph Thanks, sometimes it is hard to ask the question when you don’t have the words for it. I appreciate the tip!

    Michael D. Hall – May 20th, 2009

Respond to this