Fluent Filters, IQueryable and Linq To Sql for Easy Data Access

Thursday, July 31, 2008

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?

3 Comments

  1. paul says:

    Mike,

    Nice work. I think the generated SQL looks reasonably performant. The gap between ‘reasonably performant’ and ‘most tuned query’, though, is going to made up of a number of factors like the density of your data and what the indexes are and so forth. Offhand, I can’t think of a much better query myself, though.

  2. http:// says:

    I read the same Storefront article and did a similar implementation using IEnumerable. Is there any advantage to using IQueryable? From what I saw, I got the same kind of fluent query usage.

    Looking for some knowledge here.

    Thanks,

    Kevin

  3. http:// says:

    @Kevin,

    IQueryable gives you the SQL generation capability as long as you keep the object as IQueryable. Once you cast to IEnumerable, you are working strictly with objects and the subsequent operations performed on the sequence cannot be offloaded to SQL Server.

Leave a Reply