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 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 metadata 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

CREATE PROCEDURE USP_Test
AS
BEGIN
    CREATE TABLE #TempTable
    (
        id int,
        Name varchar(50)
    )
    
    INSERT INTO #TempTable
    VALUES (1,'Dan')
    
    SELECT * FROM #TempTable
END

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

You will then get the screen below.

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.

Alter PROCEDURE USP_Test
AS
BEGIN
    Declare @TempTable table
    (
        id int,
        Name varchar(50)
    )
    insert into @TempTable
    values (1,'Dan')
    select * from @TempTable
END
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.

This 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.

ALTER PROCEDURE USP_Test
AS
BEGIN

    Set FMTONLY off;
    Create table #TempTable
    (
        id int,
        Name varchar(50)
    )
    insert into #TempTable
    values (1,'Dan')
    select * from #TempTable
END
GO

Note. Set FMTONLY off; this is the command you can comment out after you have created the complete 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.

ALTER PROCEDURE USP_Test
AS
BEGIN

if 1= 0
begin
select 1 [id], 'MyName' [Name]
end
Create table #TempTable
(
id int,
Name varchar(50)
)
insert into #TempTable
values (1,'Dan')
select * from #TempTable
END
GO

That's it. Thanks for reading.