Programming Languages Hacks

Importanti regole per linguaggi di programmazione rilevanti come Java, C, C++, C#…

  • Subscribe

  • Lettori

    I miei lettori abituali

  • Twitter

Where condition: IEnumerable vs IQueryable

Posted by Ricibald on August 8th, 2013

Check this LINQ To Entities query

    IEnumerable<Book> books = ObjectContext.Books.Where(x => x.Id == 23);
    books = books.Where(x => !x.IsActive);

The query is, as expected, defferred: only when iterating we’ll get the value.

But…this is the query it produces (!!!):

SELECT *
FROM [dbo].[Books]
WHERE [Id] = 23

But… wait!! And the IsActive condition? This is the trap…!!

Solving the main trap

The extension method where exists in two forms:

If at compile time whe are using the first extension method we’ll execute query using LINQ To Objects. It’s deferred, but the filter is applied in memory on the entire result set and doesn’t filter in the data source…!!!

To fix it, “just” make sure to always use IQueryable.Where:

    IQueryable<Book> books = ObjectContext.Books.Where(x => x.Id == 23);
    books = books.Where(x => !x.IsActive);

Or… better (another reason to always use var!!):

    var books = ObjectContext.Books.Where(x => x.Id == 23);
    books = books.Where(x => !x.IsActive);

This produces the following correct query:

SELECT * 
FROM [dbo].[Books]
WHERE ([Id] = 23) AND ([IsActive] <> cast(1 as bit))

Solving the dependent trap

Now that we know the rule… take attention to write a similar code or you’ll have the same error:

void Test() 
{
    IQueryable<Book> books = ObjectContext.Books.Where(x => x.Id == 23);
    books = FilterActive(books);
    books.Dump();   // show data
}

IQueryable<Book> FilterActive(IEnumerable<Book> books) 
{
    return books.Where(x => !x.IsActive);
}

In the example you are filtering using IEnumerable.Where and this leads to the same error. You need to take in input IQueryable collection.

However, if you need to use IEnumerable because you’ll want to be indipendent of IQueryable you can write the following and it will work:

void Test() 
{
    var books = ObjectContext.Books.Where(x => x.Id == 23);
    books = FilterActive(books).AsQueryable();
}

IEnumerable<Book> FilterActive(IEnumerable<Book> books) 
{
    return books.AsQueryable().Where(x => !x.IsActive);
}

This is a trap, but not a bug. This strange behavior enable this scenario (thanks to @Enzo): the DAL works internally passing IQueryable objects and returns to the caller only IEnumerable. The Business Layer uses the detached IEnumerable to refine the query in memory. The Presentation Layer execute only if needed the mixed query (because deferred).

Leave a Reply

You must be logged in to post a comment.