Practical Introduction to Entity Framework: Day 6

The following are my previous articles on the fundamentals of Entity Framework:

Practical Introduction To Entity Framework: Day 1
Practical Introduction To Entity Framework: Day 2
Practical Introduction To Entity Framework: Day 3
Practical Introduction To Entity Framework: Day 4
Practical Introduction To Entity Framework: Day 5

To start with this article please read all the 4 previous articles to understand better or see this video https://www.youtube.com/v/b6vTIiBNcJ0.

I am assuming you have a basic understanding of Entity Framework now, so let's start.

How to return temporary tables and a dynamic query in Entity Framework

When we return a temporary table or dynamic query in a Stored Procedure, The Entity Framework doesn't create a scalar or complex type. Actually Entity Framework doesn't get the meta data of the query so it returns nothing.

So what is the solution? Because we have used many temporary tables in Stored Procedures.

I have 3 ways to resolve this problem, you can select one as you need to.

Let me explain the problem in detail and show some screens for a clear idea, then we will see solutions.

The following is a Stored Procedure with a temporary table:

  1. CREATE PROCEDURE USP_Test   
  2. AS   
  3. BEGIN  
  4. Create table #TempTable  
  5. (  
  6. id int,  
  7. Name varchar(50)  
  8. )  
  9.   
  10. insert into #TempTable  
  11. values (1,'Dan')  
  12. select * from #TempTable  
  13. END  
Stored procedure with temporary table

Now check the function import in the mode browser as in the following:

function import in mode browser

You we will the get screen as below:

edit in function import

I think you can see the problem now, so now check all of the ways to resolve this problem.

Solution 1

Use a table variable instead of a temporary table as in the following code:
  1. Alter PROCEDURE USP_Test   
  2. AS  
  3. BEGIN  
  4. Declare @TempTable table  
  5. (  
  6. id int,  
  7. Name varchar(50)  
  8. )  
  9. insert into @TempTable  
  10. values (1,'Dan')  
  11. select * from @TempTable  
  12. END  
  13. GO   
Now check again the function of the Stored Procedure in the mode browser as in the following screen, you will get column information after clicking get column information.

check function of stored procedure

Means we resolved the problem, we got column information.

Solution 2

Use the following SQL command in the procedure and you will get the column information to create a new complex type.
  1. ALTER PROCEDURE USP_Test   
  2. AS  
  3. BEGIN  
  4.   
  5. Set FMTONLY off;   
  6. Create table #TempTable  
  7. (  
  8. id int,  
  9. Name varchar(50)  
  10. )  
  11. insert into #TempTable  
  12. values (1,'Dan')  
  13. select * from #TempTable  
  14. END  
  15. GO  
Note: Set FMTONLY off; is the command you can comment out after you have created the complext type. Just keep in mind that if you have changed anything in the procedure then please remove the comment and update.

Solution 3

This is not a good way, but it's a very easy way. Just add a select statement with dummy data and it will not execute because 1=0.
  1. ALTER PROCEDURE USP_Test   
  2. AS  
  3. BEGIN  
  4.   
  5. if 1= 0  
  6. begin  
  7. select 1 [id], 'MyName' [Name]  
  8. end  
  9. Create table #TempTable  
  10. (  
  11. id int,  
  12. Name varchar(50)  
  13. )  
  14. insert into #TempTable  
  15. values (1,'Dan')  
  16. select * from #TempTable  
  17. END  
  18. GO  
That's it. Thanks for reading.