LINQ to SQL Gotcha #3: Chaining Where Clauses

Star date: 2010.036

In LINQ to SQL you can chain multiple where clauses like this:

Module Module1

    Sub Main()

        Using context As New TestDataContext
            context.Log = Console.Out
            Dim articles = context.Articles.Where(Function(a) a.articleID > 10) _
                                           .Where(Function(a) a.articleID Mod 2 = 0) _
                                           .ToList()
        End Using

        Console.ReadKey()

    End Sub

End Module

This will generate SQL that looks roughly like this:

SELECT ... FROM Articles WHERE articleID > 10 AND articleID % 2 = 0

Since chained where clauses are equivalent to ANDing multiple expressions in a single WHERE, the above SQL is exactly what you would expect to see. Unfortunately, things get more complicated when one of the expressions cannot be converted to SQL, like in this case:

Module Module1

    Sub Main()

        Using context As New TestDataContext
            context.Log = Console.Out
            Dim articles = context.Articles.Where(AddressOf FilterArticle).ToList()
        End Using

        Console.ReadKey()

    End Sub

    Function FilterArticle(ByVal a As Article) As Boolean
        Return a.articleID Mod 2 = 0
    End Function

End Module

The above code generates SQL that looks like this:

SELECT ... FROM Articles

The query has no where clause, it just loads all the articles and then filters them on the client side. It's usually optimal to do the filtering on the SQL side, but the behaviour is reasonable. I wouldn't expect the ORM to be capable of magically converting the contents of the FilterArticle function into SQL (it sure would be nice though). This is still expected behaviour, but here's an example where things get weird:

Module Module1

    Sub Main()

        Using context As New TestDataContext
            context.Log = Console.Out
            Dim articles = context.Articles.Where(AddressOf FilterArticle) _
                                           .Where(Function(a) a.articleID > 10) _
                                           .ToList()
        End Using

        Console.ReadKey()

    End Sub

    Function FilterArticle(ByVal a As Article) As Boolean
        Return a.articleID Mod 2 = 0
    End Function

End Module

This code generates the same SQL as last time:

SELECT ... FROM Articles

It is filtering both where clauses on the client side even though the second one could have been converted to SQL. If you flip the where clauses like this:

Module Module1

    Sub Main()

        Using context As New TestDataContext
            context.Log = Console.Out
            Dim articles = context.Articles.Where(Function(a) a.articleID > 10) _
                                           .Where(AddressOf FilterArticle) _
                                           .ToList()
        End Using

        Console.ReadKey()

    End Sub

    Function FilterArticle(ByVal a As Article) As Boolean
        Return a.articleID Mod 2 = 0
    End Function

End Module

then you will still get the expected SQL:

SELECT ... FROM Articles WHERE articleID > 10

The where clause that can be converted to SQL is filtered in the SELECT statement, but the clause that cannot be converted is filtered on the client side. I would have hoped that the order of the where clauses would not matter since they are just being ANDed, but that is not the case.

The lesson is that if you need to chain a where clause that cannot be converted to SQL, try to put it at the end of the chain. This can be a real issue if you are using a data access layer that automatically filters queries (eg: for security) with a function that cannot convert to SQL. If all of your LINQ to SQL queries have this built in filter then none of them will ever generate WHERE clauses in the SQL, it will just load the entire table every time.

Graeme Hill's Dev Blog

LINQ to SQL Gotcha #3: Chaining Where Clauses