I decided to write a second part to my previous post on extending the IQueryable interface to create a really neat and organized data access layer with Linq To Sql. The cool thing about this practice is that it nicely separates individual filters for integration testing and then lets you combine them to form much more complex queries.
I added a new filter, CreatedBefore, which simply filters on the created date of the blog.
I can now use this by itself or daisy-chain it with the other filters I’ve created. The resulting T-SQL is below. Note the new CreateDate filter’s effect on it.
Linq to Sql gets even cooler! Let’s say a Blog had associated Posts (a standard one-to-many, parent-child relationship). This would result in a entity model similar to the one below.
Using the same filters I already created for the Blog entity and combining it with the DataLoadOptions property of the data context, I can now get not just the filtered Blog entities, but their associated Posts in just one trip to the database! Nice!
I’ve blogged about how to use the DataLoadOptions in a previous post. Setting this as usual gives you the immediate result you’re looking for.
The generated T-SQL statements start to get a little uglier, but I’ll take the Pepsi challenge when it comes to some custom SQL a developer might have written for this same functionality.
Hope this helps!
Inspired by Rob Conery’s great work on his MVC Storefront screencasts, I decided to look into his Pipes and Filters implementation and attempt to create my own. Although there was a lot of critical commentary on some of the design decisions, I found myself very intrigued by this fluent interface implementation. I haven’t referenced back to the specific code samples so if it differs significantly, please keep in mind that this was certainly inspired by it.
A very nice feature of Linq to Sql is that it delays query execution until you start enumerating over the collection. This lets the developer build complex queries programatically and make only one call to the database. Linq to Sql is smart enough to derive the resulting T-SQL to produce your result set.
For this scenario, let’s assume we want to select blogs that are tagged with specific tags and the blog name starts with a certain string. Below we can see a simple entity model that shows the relationships among the Blog, BlogTag and tag tables.
Let’s keep the domain-specific language for our extension method names. I’ll create two extension methods: “AreTaggedWith” and “BlogNameStartsWith”. The former will accept a string array of tags and the latter will accept a string parameter. Both of these methods extend the IQueryable<Blog> interface and return the same.
We can then “daisy-chain” these all together to create are linq query before actually enumerating (and therefore executing) the final query. This will produce a nice looking, fluent interface for our data access layer.
Using SQL Server Profiler, we can take a look at the query that Linq to Sql actually generated and how it is executed with input parameters and all.
Looking at the query above, I’d love to hear your thoughts on whether this is the most tuned query for this scenario. Did Linq to Sql did a good job generating the query?
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!