you're reading...

LINQ to SQL quick tip

Quick tip:  If you are going to composite method calls together in order to make use of multiple method calls to refine the SQL Query to be generated, make sure the calls are made against IQueryable<T> objects and not IEnumerable<T>.

LINQ method calls (Where, Skip, Take, Count, etc) against IQueryable<T> have different extension methods that get called compared to those that get called when called against an IEnumerable<T> argument.

To illustrate this, consider the following design.  I have a model which only does a few things, but to reduce the amount of code duplication I split the query refinements into individual methods.  I have a base method which just selects the data from the table.  Another method which will perform a Where query on data passed in, another which will Page the data passed in, and another which will just count the number of rows in the data passed in.

In other words, I can do this:

IEnumerable<TEntity> GetSearchResults( string search, int page )
  IEnumerable<TEntity> results = QueryData();
  results = SearchData( results, search );
  results = PageData( results, page, Constants.ResultsPerPage );

  return results;

If these methods take and return IEnumerable<T> bad things start to happen, foremost is that the base query selects everything out of the table.  The Where query is all performed client side rather than being generated into the SQL query, and the same for the paging and counting!

Swap out IEnumerable<T> for IQueryable<T> and things begin to work well though.  The Where query is generating Where clauses in SQL and the same goes for the other methods.  And since IQueryable<T> requires implementers to also implement IEnumerable<T>, at any point in time I can decide to stop chaining calls and revert to IEnumerable<T> so method callers have no idea what the internal implementation of getting that IEnumerable<T> really is.

The only changes I need to make to the code I have above is to change the results variable to IQueryable<T>, as wells as the return type and the parameter type of QueryData, SearchData, and PageData to deal with IQueryable<T>.  The return type of the GetSearchResults method doesn’t need to be changed!

Edit: I tried to use the syntax highlighting feature of WordPress provided by a google code project, but it failed miserably on the generics.


About James

I am a Senior Developer/Consultant for InfoPlanIT, LLC. I previously spent over 7 years as a Product Manager for what eventually became ComponentOne, a division of GrapeCity. While there, I helped to create ActiveReports 7, GrapeCity ActiveAnalysis, and Data Dynamics Reports.


No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s


%d bloggers like this: