EF не может вывести схему возврата из хранимой процедуры, выбрав из таблицы #temp

Предположим следующее:

CREATE PROCEDURE [MySPROC]
AS 
BEGIN

CREATE TABLE #tempSubset(
    [MyPrimaryKey] [bigint]  NOT NULL,
    [OtherColumn]  [int]     NOT NULL)

INSERT INTO #tempSubset (MyPrimaryKey, OtherColumn) 
    SELECT SomePrimaryKey, SomeColumn 
    FROM   SomeHugeTable
    WHERE  LimitingCondition = true

SELECT MyPrimaryKey, OtherColumn 
FROM   #tempSubset
WHERE  SomeExpensiveCondition = true

END

Когда я генерирую функцию import или сопоставляю возвращаемый тип, EF не генерирует сложный тип или не говорит мне:

Выбранная хранимая процедура или функция не возвращает столбцы

Как преодолеть это?

Другие ответы предлагают с использованием табличных переменных (не будем делать это по соображениям производительности) подделка схемы возврата и комментирование реальная хранимая процедура, другие предлагают делать похожие с представлениями... но должен быть способ сделать это, не добавляя лишних накладных расходов или требующих меня разбить хранимую процедуру для обновления модели?

Ответ 1

CREATE PROCEDURE [MySPROC]
AS 
BEGIN

--supplying a data contract
IF 1 = 2 BEGIN
    SELECT
        cast(null as bigint)  as MyPrimaryKey,
        cast(null as int)    as OtherColumn
    WHERE
        1 = 2  
END

CREATE TABLE #tempSubset(
    [MyPrimaryKey] [bigint]  NOT NULL,
    [OtherColumn]  [int]     NOT NULL)

INSERT INTO #tempSubset (MyPrimaryKey, OtherColumn) 
    SELECT SomePrimaryKey, SomeColumn 
    FROM   SomeHugeTable
    WHERE  LimitingCondition = true

SELECT MyPrimaryKey, OtherColumn 
FROM   #tempSubset
WHERE  SomeExpensiveCondition = true

END

Поставка контракта на поддельные данные для набора результатов - самый простой, самый чистый и быстрый способ позаботиться об этой проблеме. Эта же проблема существует и в элементах управления источниками данных в SSIS..NET прочитает набор результатов из недостижимого раздела "контракт" запроса и предоставит метаданные для сложного типа. Отсутствие влияния на производительность и отсутствие необходимости комментировать SQL, выполняющий фактическую работу.

Ответ 2

Добавьте это в начало определения хранимой процедуры:

SET FMTONLY OFF

разрешило модели выводить схему из временной таблицы без проблем. В качестве бонуса он не требует дополнительного обслуживания для контракта.

Пример:

SET FMTONLY OFF

CREATE TABLE #tempTable (
    ...
)

...

SELECT * FROM #tempTable

Ответ 3

Решение 1 Используйте переменную таблицы вместо временной таблицы.

Решение 2 Используйте Set FMTONLY; SQL в процедуре, и вы получите информацию о столбцах для создания нового сложного типа.

Решение 3 Это не очень хороший способ, но это очень простой способ. Просто добавьте оператор select с фиктивными данными, и он не будет выполняться, потому что 1 = 0.

вы можете проверить информацию на этой ссылке

Ответ 4

Это неполно, но когда отключено fmtonly, вы можете сгенерировать контракт данных, используя следующее:

        SELECT * 
        FROM tempdb.sys.columns 
        WHERE [object_id] = OBJECT_ID(N'tempdb..#u');

        select case  system_type_id 
        when 62 then 'cast(null as float) as ' 
        when 175 then 'cast(null as char(' + cast(max_length as varchar(50)) + ')) as ' 
        when 167 then 'cast(null as varchar(' + cast(max_length as varchar(50)) + ')) as ' 
        when 56 then 'cast(null as int) as ' 
        when 104 then 'cast(null as bit) as ' 
        when 106 then 'cast(null as decimal(' + cast(precision as varchar(50)) + ',' + cast(scale as varchar(50)) + ')) as ' 
        when 40 then 'cast(null as date) as '            
        end
        + name + ','
        from  tempdb.sys.columns 
        WHERE [object_id] = OBJECT_ID(N'tempdb..#u');