The following are my previous articles on the fundamentals of Entity Framework
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.