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

Friday, August 1, 2008

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!

One Comment

  1. http:// says:

    I’ve been playing around with this concept some too, and am having trouble when it comes to some more complex scenarios. For example, in the above, you typically wouldn’t want _all_ the posts for a blog – just the ones within a given date range.

    In keeping with the fluent scheme, something like this:

    //Get all posts in the last 3 days for blogs with the given tags and a name that starts with ‘j’
    var posts = db.Blogs
    .AreTaggedWith(tags)
    .BlogNameStartsWith(“J”)
    .GetPosts()
    .CreatedAfter(DateTime.Now.AddDays(-3))

    “GetPosts()” is where it gets tricky. I was able to come up with something using ‘SelectMany’, but the resulting SQL included a CROSS JOIN, which seemed a little off to me. Any ideas?

Leave a Reply