Many years ago I gave a presentation on Linq to a group of developers in London. It was in the very early days of Linq and dotnet. I have been a fan of this technology for all these years. I have long known there are (at least) four things that break Linq. They all revolve around the mechanism that Linq uses to generate its’ classes.
1. #temp tables. Linq cannot determine the column types of #temp tables, you need to use @table variable instead.
2. When you use a @temp table you need to union it with declared variables
(CAST 0 AS INT) Column1
AA.Column1 > 0
3. The same is true for user defined functions udfs and a similar solution applies, we normally comment the called proc with a dependency list at the top.
4. Multiple row sets returned. You will only get the first row set used to define the linq class. If you have a “debug flag” parameter passed then all debug row sets returned need to be returned after the intended row set.
5. Beware git (and presumably other version control system) where you have explicit different connection strings on different development and testing machines. Search for your “LocalSqlServer” string in your project.
6. COALESCE is useful in its place as is ISNULL. The latter returns a non null type in linq, the former a nullable type – changing from one to the other may well kill the class linq generates.