Сохраненная процедура возвращает int вместо набора результатов

У меня есть хранимая процедура, содержащая динамический выбор. Что-то вроде этого:

ALTER PROCEDURE [dbo].[usp_GetTestRecords] 
    [email protected] int = 0, 
    [email protected] int = 0
    @groupId nvarchar(10) = 0
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @query  NVARCHAR(max)

    SET @query = 'SELECT * FROM CUSTOMERS WHERE Id = ' + @groupId
    /* This actually contains a dynamic pivot select statement */

    EXECUTE(@query);
END

В SSMS хранимая процедура работает нормально и показывает набор результатов.

В С#, использующем Entity Framework, он показывает возврат int вместо IEnumerable?

private void LoadTestRecords()
{
    TestRecordsDBEntities dataContext = new TestRecordsDBEntities();
    string id = ddlGroupId.SelectedValue;

    List<TestRecord> list = dataContext.usp_GetTestRecords(id); //This part doesn't work returns int
    GridView1.DataSource = list;
}

Сгенерированная функция для usp_GetTestRecords

public virtual int usp_GetTestRecords(string groupId)
{
    var groupIdParameter = groupId != null ?
        new ObjectParameter("groupId", groupId) :
        new ObjectParameter("groupId", typeof(string));

    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("usp_GetTestRecords", groupIdParameter);
}

Ответы

Ответ 1

Entity Framework не может определить, что возвращает ваша хранимая процедура. Мне удалось создать табличную переменную, которая отражает данные из оператора SELECT. Просто вставьте в переменную таблицы, затем сделайте выбор из этой переменной таблицы. EF должен забрать его.

Ответ 2

Я получаю это, когда у меня есть хранимая процедура, которая включает вызов "exec" во временную таблицу, например:

insert into #codes (Code, ActionCodes, Description)
exec TreatmentCodes_sps 0

Похоже, что Entity Framework запутывается относительно того, что должно быть возвращено процедурой. Решение, с которым я столкнулся, состоит в том, чтобы добавить это вверху sproc:

SET FMTONLY OFF

После этого все хорошо.

Ответ 3

У меня такая же проблема, и я нашел решение здесь

  • Перейдите в ваш .edmx
  • В окне браузера модели/функции Импорт находит вашу процедуру, а затем дважды щелкните по ней.
  • Измените тип возврата, который вы хотите
  • Сохраните .edmx и снова проверьте тип возврата.

Снимок экрана

Это должно быть то, что вам нужно сейчас.

Ответ 4

См. ответ Ladislav Mrnka в этой статье "Переполнение стека" fooobar.com/questions/100573/...

У меня была та же основная проблема.

Добавление

SET FMTONLY OFF

К процедуре, которую вы пытаетесь импортировать во время импорта, будет устранена эта проблема. Хорошей практикой является удаление строки после этого, если целью базы данных не является только предоставление схемы для EF (Entity Framework).

Основная причина осторожности заключается в том, что EF использует этот параметр для предотвращения мутаций данных при попытке получить метаданные.

Если вы обновите модель сущности из базы данных, любые процедуры с этой строкой в ​​них могут потенциально обновлять данные в этой базе данных, просто пытаясь получить схему.

Я хотел добавить дополнительную заметку об этом, поэтому вам не нужно было полностью сканировать другую ссылку.

Если вы хотите попробовать использовать FMTONLY, вот несколько вещей, которые нужно иметь в виду.

when FMTONLY is on:
  1) only the schema is returned (no) rows.
     similar to adding a blanket false statement to your where clause (ie "where 1=0")
  2) flow control statements are ignored

Пример

set fmtonly on

if 1=1
begin
    select 1 a
end
else
begin
    select 1 a,2 b
end

while 1=1
select 1 c

Приведенное выше возвращает NO строк и метаданные для каждого из трех запросов

По этой причине некоторые люди предлагают переключить его таким образом, чтобы использовать его в несоблюдении контроля потока

if 1=0
begin
    set fmtonly off
end

Фактически вы можете использовать это, чтобы ввести логику, которая отслеживает этот

set fmtonly off
declare @g varchar(30)
set @g = 'fmtonly was set to off'

if 1=0
begin
    set fmtonly off
    set @g = 'fmtonly was set to on' 
end

select @g

Подумайте ОЧЕНЬ ОСТОРОЖНО, прежде чем пытаться использовать эту функцию, поскольку она обе устарела и потенциально может сильно затруднить выполнение sql

ОСНОВНЫЕ понятия, которые необходимо понимать, следующие

1. EF turns FMTONLY on to prevent MUTATING data from executing stored procedures
   when it executes them during a model update.
   (from which it follows)

2. setting FMTONLY off in any procedure that EF will attempt to do a schema scan
   (potentially ANY and EACHONE) introduces the potential to mutate database
   data whenever *anyone* attempts to update their database model.

Ответ 5

Entity Framework автоматически вернет скалярное значение, если ваша хранимая процедура не имеет первичного ключа в вашем результирующем наборе. Таким образом, вам нужно будет включить столбец первичного ключа в ваш оператор select или создать временную таблицу с первичным ключом, чтобы Entity Framework вернула результирующий набор для вашей хранимой процедуры.

Ответ 6

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

Ответ 7

У меня была та же проблема, я изменил имя полей возврата по ключевому слову "AS" и обратился к моей проблеме. Одной из причин этой проблемы является именование имен столбцов с зарезервированными ключевыми словами SQL Server.

Примером является пара:

ALTER PROCEDURE [dbo].[usp_GetProducts]

AS
BEGIN

 SET NOCOUNT ON;

 SELECT 
      , p.Id
      , p.Title
      , p.Description AS 'Description'

    FROM dbo.Products AS p
END

Ответ 8

Я обдумал это немного, и я думаю, что у меня есть лучший/более простой ответ

Если у вас есть сложное хранилище, которое сложно скомпоновать структуру сущности (для текущих версий Entity Framework, использующих тег FMTONLY для схемы доступа)

подумайте о том, чтобы сделать следующее в начале вашей хранимой процедуры.

--where [columnlist] matches the schema you want EF to pick up for your stored procedure

if 1=0
begin
    select 
       [columnlist]
    from [table list and joins]
    where 1=0
end

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

declare @tablevar as table
(
    blah int
    ,moreblah varchar(20)
)

if 1=0
begin
    select * from @tablevar
end

...
-- load data into @tablevar
select * from @tablevar

Ответ 9

Лучшее решение, которое я нашел, - немного обмануть.

В процедуре магазина все комментируйте, ставьте первую строку с select [foo]='', [bar]='' и т.д... Теперь обновите модель, перейдите к отображенной функции, выберите комплексный тип и нажмите Get Column Information, а затем Create Complex Type.

Теперь прокомментируйте поддельный выбор и снимите комментарий с реального тела процедуры магазина.

Ответ 10

Просто измените на

ALTER PROCEDURE [dbo].[usp_GetTestRecords] 
    [email protected] int = 0, 
    [email protected] int = 0
    @groupId nvarchar(10) = 0
AS
BEGIN
    SET NOCOUNT ON;


    SELECT * FROM CUSTOMERS WHERE Id =  @groupId

END

Ответ 11

Я знаю, что это старый поток, но если у кого-то есть те же проблемы, я расскажу о своих бедах.

Чтобы найти проблему, запустите обработчик sql при добавлении сохраненного proc. Затем вы можете увидеть, какая инфраструктура сущности передается как параметры для генерации вашего набора результатов. Я предполагаю, что почти всегда он будет передавать значения нулевых параметров. Если вы генерируете sql на лету, объединяя строковые значения и значения параметров, а некоторые из них равны нулю, sql сломается, и вы не получите набор возвратов.

Мне не нужно создавать временные таблицы или что-то еще только команду exec.

Надеюсь, что это поможет

Ответ 12

Если вам нужно это сделать, вам может быть лучше просто сделать частичный dbcontext и самостоятельно создать функцию С#, которая будет использовать SqlQuery для возврата необходимых вам данных. Преимущества для некоторых других вариантов:

  • Не нужно ничего менять при обновлении модели.
  • Не будет перезаписываться, если вы сделаете это непосредственно в сгенерированном классе (кто-то выше упоминает об этом, как будто это опция:))
  • Не нужно добавлять что-либо к самому процессу, который может иметь побочные эффекты сейчас или позже

Пример кода:

 public partial class myEntities
    {
       public List<MyClass> usp_GetTestRecords(int _p1, int _p2, string _groupId)
       {
          // fill out params
          SqlParameter p1 = new SqlParameter("@p1", _p1);
          ...
          obj[] parameters = new object[] { p1, p2, groupId };

          // call the proc
          return this.Database.SqlQuery<MyClass>(@"EXECUTE usp_GetTestRecords @p1, @p2, @groupId", parameters).ToList();
       }
    }

Ответ 13

Во время импорта

УСТАНОВИТЬ FMTONLY ON может использоваться для принятия sp-схемы.

Если вы измените sp и хотите обновить новый, вы должны удалить старую определенную функцию из файла edmx (из xml), потому что, удалив sp из браузера модели, он не удаляется в edmx. Например:

    <FunctionImport Name="GetInvoiceByNumber"     ReturnType="Collection(Model.Invoice_Result)">
       <Parameter Name="InvoiceNumber" Mode="In" Type="Int32" />
    </FunctionImport>

У меня была та же проблема, и когда я полностью удаляю тег FuctionImport соответствующего sp, модель обновляется правильно. Вы можете найти тег, выполнив поиск имени функции из visual studio.

Ответ 14

Возможно, вам удастся открыть браузер модели, а затем перейти в функцию Импорт, дважды щелкнув соответствующую хранимую процедуру, а затем вручную щелкнув "Получить информацию о столбцах" и нажав "Создать новый сложный тип". Обычно это устраняет проблему.

Ответ 15

Ну, у меня тоже была эта проблема, но после нескольких часов онлайн-поиска ни один из вышеперечисленных методов не помог. Наконец, я узнал, что это произойдет, если ваша процедура хранилища получает некоторые параметры со значением NULL и которые генерируют любую ошибку при выполнении запроса. Entity Framework сгенерирует метод для хранимой процедуры, определив сложную модель объекта. Из-за этого нулевого значения ваша процедура магазина будет возвращать и значение int.

Пожалуйста, проверьте вашу процедуру хранения, либо предоставляет пустой набор результатов с нулевыми значениями. Это исправит вашу проблему. С надеждой.

Ответ 16

Я думаю, что это проблема разрешений для базы данных, я не знаю, что именно может быть, но в моей работе мы используем пользователей Active Directory для предоставления приложениям подключения к базам данных, эти учетные записи специально созданы для приложений, каждого приложения. У меня есть собственная учетная запись, ну, как разработчики, у меня есть разрешения на чтение, запись и другие базовые вещи, никаких изменений и никаких расширенных функций, и у меня та же проблема при запуске Visual Studio с моей обычной учетной записью, то, что я сделал должен был открыть Visual Studio, выбрав опцию "как другой пользователь" в контекстном меню, и я поместил логин AD, предоставленный для приложения, и вуаля !, теперь мои хранимые процедуры загружаются со всеми полями, которые я ожидал, до этого, мои хранимые процедуры возвращались как int. Я надеюсь, что это поможет кому-то, может быть, разрешения VIEW DEFINITION для учетной записи базы данных сделают свое дело