How To: Sort a LINQ to SQL Entity at the Parent and Child Level
I've been really diving head first into the CTP of the ASP.NET MVC Framework as well as getting my hands dirty with LINQ to SQL, as I'm sure many of you have been. During this time, I hit a road bump and was banging my head against the wall trying to figure this one out with my LINQ to SQL knowledge. In a previous post, I wrote about using a Repeater control to display a LINQ to SQL entity with a parent child relationship. The example we used was the concept of a FAQ engine with a FaqCategory table and a Faq table. The relationship is shown below.
Each table has a "DisplayOrder" field which, as the name implies, is used to sort the entities for display purposes. Sorting the parent table (in this case FaqCategory) is straightforward. However, accomplishing this while also sorting on the child table was not as straightforward. We have a couple of options available to us when we need to sort the child entity of a hierarchical relationship.
Method 1 - Using the DataLoadOptions class
The first method, and the one that I chose for this specific implementation, is to use the very handy DataLoadOptions class. This class really hasn't gotten as much attention as it deserves in recent LINQ to SQL blog posts, but it really has some great features that let you customize how data is loaded. You can set entities that should be loaded together which can decrease roundtrips to the database server and increase performance. The Faq implementation is below with sorting on the child table.
This will tell the data context to always load the Faq table along with the FaqCategories
Method 2 - Projecting the Child Entity
The other method is simply projecting the child entity itself. I'm slowly but surely getting used to the LINQ to SQL syntax. This implementation is displayed below.
Special thanks to Joe Albahari for his help with my LINQ to SQL syntax!
Hope this helps!