Home > View Post

Creating reusable Entity Framework queries thanks to deferred execution

With all the shenanigans that are now going on in my queries (see Sub-selects in code with the Entity Framework, Projection blows includes in Entity Framework and Sorting associations in the Entity Framework) it became important to follow the DRY (Don't repeat yourself) principle. This is surprisingly easy thanks to deferred execution. I can create a method like so:

private static IQueryable<PostData> AdvancedPostQuery(this BlogEntities be)
    return from p in be.Posts
         select new PostData
             Post = p,
             Tags = p.Tags,
             Comments = p.Comments,
             User = p.User,
             NextLinkData = (from n in be.Posts where n.PostedDate > p.PostedDate orderby n.PostedDate ascending select new PostLinkData { Title = n.Title, LinkTitle = n.LinkTitle }).Take(1).FirstOrDefault(),
             PreviousLinkData = (from prev in be.Posts where prev.PostedDate < p.PostedDate orderby prev.PostedDate descending select new PostLinkData { Title = prev.Title, LinkTitle = prev.LinkTitle }).Take(1).FirstOrDefault(),

And I can still add additional where clauses and the like to the returned IQueryable object without fear of loading the whole database into memory and performing those operations in-proc. No sirree, the execution (and generation) of the SQL query is deferred until the data is actually required for use in-proc which allows you to create a reusable method like the one above and build up your queries in layers.

Very nice. Very clever.

I've painted what some might consider a very negative picture of EF in these recent posts but I certainly don't view EF negatively. In fact, it's one of my favourite technologies to come out of Redmond for some time and I'm much happier with it than the previous manual DAL that powered thejoyofcode.com. Give it a shot.

Josh Post By Josh Twist
5:17 AM
13 Feb 2009

» Next Post: WCF: Could not establish trust relationship for the SSL/TLS secure channel with authority
« Previous Post: Using .Select() instead of foreach

Comments are closed for this post.

Posted by Simon Ince @ 16 Feb 2009 10:13 AM
Nice post - and useful trick. But what do you think about making your example method public? Would that be permissible, or would you rather keep this powerful query mechanism internal to your data access code, so that "run-away" queries don't end up getting pushed to the database? e.g. selecting every row.


Posted by josh @ 19 Feb 2009 7:25 AM
Hi Simon,

If you remember at the start of the series of posts on EF this was to replace an existing DAL and to do so without the consumer knowing EF was the underlying data access technology. Hence this is private because only the DAL would be using it.

I'd probably continue to do EF behind a DAL style interface I think - but that's just my personal preference. YMMV.


Posted by Ray @ 31 Jul 2009 3:22 AM
Nice handy way of using it, I wouldn't suggest to make it public as Simon mentioned earlier since other developers in the team may not use it properly.

© 2005 - 2022 Josh Twist - All Rights Reserved.