How to get custom SQL Server type into Entity Framework generated code

Question

There is a TABLE Type defined in SQL server:

CREATE TYPE RealtySearchResult AS TABLE
(
realtyId int not null,
OwnerId int not null,
...)

And stored procedure:

CREATE PROCEDURE [dbo].[SearchRealty]
(@fulltext nvarchar(200) null,
 @skipRows int,
 @pageCount int,
....
)
AS
BEGIN

DECLARE @SQL nvarchar(max)
DECLARE @result RealtySearchResult

CREATE TABLE #TEMP
(
realtyId int not null,
OwnerId int not null,
...
)

set @SQL = N'
INSERT INTO #TEMP
SELECT 
realty.Id AS realtyId,
realty.OwnerId,
....join with fulltext catalog.... WHERE....@pageCount .....@skipRows'

-- sp_executesql cannot write to local table variable @result,
-- that is why it reads to temp table and then to @result

exec sp_executesql @SQL, N'@skipRows int, @pageCount int', @skipRows, @pageCount

INSERT INTO @result SELECT * FROM #TEMP
SELECT * FROM @result

END

And then in Visual Studio I update the model from database and a new method (wrapper for store procedure SearchRealty) is generated, but it does not contains generated code for returning complex type.

I would expect that EntityFramework should be able to recognize that the store procedure returns defined table type RealtySearchResult and should generate wrapper for it. I am too lazy to write the complex return type by myself in C# again (I just wrote it in SQL). It is really needed?

Can I just generate wrapper for RealtySearchResult type in EntityFramework somehow?

I use Visual Studio 2017 and EntityFramework 6.

It sounds as duplicate as Stored procedure in Entity Framework database first approach but once I click the button Get Column Information I got message "The selected stored procedure or function returns no columns".

Why you are creating `#TEMP` table while you already have it (`@result`) — Ilyes, Nov 17 '17 at 11:53
Possible duplicate of [Stored procedure in Entity Framework database first approach](https://stackoverflow.com/questions/43821023/stored-procedure-in-entity-framework-database-first-approach) — Camilo Terevinto, Nov 17 '17 at 11:55
I need to create temp table for sp_executesql, because it is restriction made by Microsoft. Sp_executesql does not support to local table variable as OUTPUT parameter, just input. — Tomas Kubes, Nov 17 '17 at 11:55
@CamiloTerevinto I just posted screenshot why it is not duplicate. — Tomas Kubes, Nov 17 '17 at 12:04
Have you tried just declaring result as a table variable (not a type)? — Nick.Mc, Nov 17 '17 at 12:20
possible duplicate of https://stackoverflow.com/questions/46940660/entity-framework-not-getting-column-info-on-a-different-schema/46961196#46961196 — Kirsten, Nov 17 '17 at 12:21

score 0 · Answer 1 · answered Nov 17 '17 at 20:47

Analysis

Based on link Entity Framework not getting column info on a different schema provided by kirsten I realize that EntityFramework execute stored procedure with mode

SET FMTONLY ON

It means it strips all condition and dynamic SQL. This result in empty temporary table and procedure failing during receiving metadata from EntityFramework.

Solution

To help the designer to get metadata without dynamic SQL. Count with that conditions are removed. Following code does a job:

DECLARE @result RealtySearchResult

IF 0=1
BEGIN
    SELECT * FROM @result
    RETURN
END

During execution of store procedure by EntityFramework (in order to get metadata), condition 0=1 is removed and empty table of Table type is returned which is enough to get metadata. This code is never trigerred in production because of impossible condition.

How to get custom SQL Server type into Entity Framework generated code

1 Answers1

Analysis

Solution