Skystone Software
http://www.SkystoneSoftware.com|
Linq to SQL - Filtered and Sorted Associations
Published: 12/21/2008 |
|
I've recently been fnally able to dive into using Linq to SQL, and decided to use the DataClasses and associated visual designer to model the business / data access layer for the newest version of our content management software. After hours of virtually banging my head against the wall I figured out how to extend default associations to filter and sort the collections related to an object.
The Problem: The Linq to SQL visual designer allows you to quickly create DataClasses encapsulating a table in a database by dragging the table into a design surface and modifying a few properties. Obejcts can have one to many relationships by creating Associations between them - and a collection property is automatically added to the parent in the relationship. The problem with the relationship between two of the objects I was designing is that the relationship was more complex than a foreign key relationship - the child collection should only include related records with a certain field value containing NULL. In addition, I wanted to be able to sort the child collection by yet another field, and neither of these options were available through the default Associations visual editor.
The Solution: The DataContext object associated with the objects has a LoadOptions property that accepts a class of type DataLoadOptions (in the namespace System.Data.Linq). Existing associations can be extended through this object when the DataContext is created by configuring a new DataLoadOptions object in the OnCreated method of the DataContext and assigning it to the LoadOptions property.
Here's an example:
C#:
partial class DOMDataContext
{
partial void OnCreated()
{
DataLoadOptions dlo = new DataLoadOptions();
dlo.AssociateWith<Client>
(c => c.Sites.OrderBy(s => s.Name).ThenBy(s => s.Created));
dlo.AssociateWith<Site>
(s => s.Menus.Where(m => m.ParentID == null).OrderBy(m => m.Order));
dlo.AssociateWith<Site>
(s => s.Domains.OrderByDescending(d => d.IsSiteDefault).ThenBy(d => d.Name));
dlo.AssociateWith<Menu>
(m => m.SubMenus.OrderBy(s => s.Order).ThenBy(s => s.Caption));
this.LoadOptions = dlo;
}
}
So the Associations have been configured using the design surface specifying the foreign key relationships between the tables, and the above code extends these relationships to add sorting and filtering logic. The second AssociateWith method call filters the Menus collection (by specifying that the ParentID field must be null) and then sorts it by calling the OrderBy method.
For more information on using the AssociateWith method, click here; for more information on using lambda expressions as inline anonymous functions, click here.
Questions or Comments? .
VB to C# and C# to VB translation provided by Instant C# and Instant VB.
