LINQ-to-SQL Compiled Query Problem (works as uncompiled query)

Answer 1

I believe you might be hitting the same issue detailed here. Bryan Watts gives a very good explanation of what is wrong.

Answer 2

I believe the compiled query must be translatable to SQL, which your extension method cannot be. If you profile the SQL created by your "regular" query you may find it is selecting the entire table so it can feed all the rows into your extension method.

You'd do better to put your filtering logic in the query (as part of the expression tree) so it can be translated to SQL and run server side.

The OrderBy is also a problem because of the Skip. You need to make this translatable to SQL or LINQ will have to return all the rows in order to filter them down client side.

If you can't express these as LINQ expressions, consider creating SQL Functions on the server and mapping them to your DataContext. LINQ will be able to translate those to the T-SQL function calls.

EDIT:

I guess I was assuming your extension methods weren't building expression trees. Sorry.

Consider this link which seems similiar to your problem. It references another link that goes into more detail.

It looks like the MethodCallExpression is the issue.

The code is a bit long to be posted here, but similar to tomasp.net's expander, I visit every expression in the expression tree and if the node is a MethodCallExpression which calls a method that returns an expression tree, I replace that MethodCallExpression by the expression tree returned by invoking the method.

So, it appears the problem is that when compiling the query, the method is not executed so there is no expression tree to translate to SQL.

Answer 3

LINQ to SQL CompiledQuery is good option, but things that needs to be taken into consideration while using the same is that first time when it is called it takes more time than a routine query would take. Refer to below link...

http://www.a2zmenu.com/LINQ/LINQ%20to%20SQL%20CompiledQuery.aspx