FAQ - General

Question
linq Could not retrieve schema information for database object
Answer

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
SELECT
  AA.Column1
 FROM
(
SELECT
   (CAST 0 AS INT) Column1
UNION
   SELECT
    Column1
   FROM
    @Table
)  AA 
WHERE
 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.

If you found this information useful, please share it!


This FAQ was last updated on Wednesday, September 30, 2015

Contact Information

To find out more about Ava solutions you can contact us in a number of ways:
Follow Us...