The Situation

I recently wanted to declare a table, insert data into it and then, depending on a variable passed in to the stored procedure (@i_bitCurrent), either return the contents of the table, or filter the contents with a Where clause and return them (3 options in total). The reason I wanted to use the temporary table approach was to avoid duplicating the long select statement 3 times, with just the Where part of the statement being different (or have three separate stored procedures).

The Problem

I got the stored procedure to work, and return the expected data set when run from within Microsoft SQL Management Studio, but Entity Framework (in Visual Studio 2012) refused to recognise the return type (it saw a return type of nullable int).

The Solution

After Googling the problem I found a similar problem and suggested answer on Stack Overflow – I needed to supply a faux data contract that Entity Framework would then use as the return type. The faux data contract is the first IF statement in the code below.

The Code

This is a cut-down version of the code, so you can see why I did not want to duplicate it (making changes would ahve been a nightmare…) The faux data contract IF statement at the beginning will never be executed as 1 will never equal 2. It is simply there to get Entity Framework to recognise the return type. It will need to be updated if the temporary table is ever updated.

Entity Framework / Visual Studio

Then when I removed the stored procedure from the model in Visual Studio (from both the Stored Procedures / Functions folder and Function Imports folder), and refreshed the model from the database, the stored procedure was added correctly and a Complex Type created with the correct return value.

That is an Entity Framework Hack!

Note

Ints and bits (bools) will be returned as nullable, so you may need to use returnType.fieldName.Value ( in C#, or the equivalent in the language you are using) to access the value of these types in the returned type.