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

Я использую Entity Framework 6, вызывая хранимую процедуру, которая принимает параметр табличного значения. Хранимая процедура выполняется без ошибок, но не возвращает строк. Я использую профилировщик для отслеживания вызова, чтобы увидеть, как его выполняет EF. Если я вручную вызову хранимую процедуру другим способом, она вернет строки, как и ожидалось.

Вот мой код приложения:

public IEnumerable<Table1> ListTableValueParameter(IEnumerable<int> lstIDs)
{
    //Convert enumerable int to DataTable
    System.Data.DataTable dtIDs = new System.Data.DataTable();
    dtIDs.Columns.Add("ID", typeof(int));

    foreach(int i in lstIDs)
    {
        dtIDs.Rows.Add(i);
    }

    var db = new POCDBContext();

    //Create parameter for table
    System.Data.SqlClient.SqlParameter p = new System.Data.SqlClient.SqlParameter();
    p.SqlDbType = System.Data.SqlDbType.Structured;
    p.ParameterName = "@IDS";
    p.Value = dtIDs;
    p.TypeName = "dbo.IntegerTableParameter";

    using (var connection = db.Database.Connection)
    {
        connection.Open();
        var command = connection.CreateCommand();
        command.CommandText = "EXEC [dbo].pList_TableProperties";

        command.Parameters.Add(p);

        //Execute stored procedure
        using (var reader = command.ExecuteReader())
        {
            return
                ((IObjectContextAdapter)db)
                    .ObjectContext
                    .Translate<Table1>(reader).ToList();

        }
    }   
}

Этот код выполняет следующие операторы SQL во время выполнения, которые не возвращают никаких записей:

declare @p3 dbo.IntegerTableParameter
insert into @p3 values(1)
insert into @p3 values(2)
insert into @p3 values(3)
insert into @p3 values(4)
insert into @p3 values(5)

exec sp_executesql N'EXEC [dbo].pList_TableProperties',N'@IDS [dbo].[IntegerTableParameter] READONLY',@IDS=@p3

Если я запущу следующий sql, он вернет записи:

declare @p3 dbo.IntegerTableParameter
insert into @p3 values(1)
insert into @p3 values(2)
insert into @p3 values(3)
insert into @p3 values(4)
insert into @p3 values(5)

EXEC [dbo].pList_TableProperties @p3

Есть ли что-то другое, что я могу сделать на стороне клиента, чтобы это выполнялось правильно?


comment
command.CommandText = "[dbo].pList_TableProperties"; command.CommandType = CommandType.StoredProcedure на самом деле не должно решить вашу проблему, но в любом случае это хорошо.   -  person Jeroen Mostert    schedule 01.08.2017
comment
На самом деле @JeroenMostert, я думаю, это решит проблему. Поскольку для команды не задано значение StoreProcedure и параметр в сквозном запросе не упоминается, он будет добавлен в команду, но будет вести себя так, как будто параметр вообще не передается.   -  person Sean Lange    schedule 01.08.2017
comment
@JeroenMostert: я думаю, что ваше решение правильное. Другой вариант: command.CommandText = "EXEC [dbo].pList_TableProperties @IDS"; вместо command.CommandText = "EXEC [dbo].pList_TableProperties";   -  person TriV    schedule 01.08.2017
comment
Упс. Я совершенно упустил это из виду. Я ожидал, что sp_executesql будет жаловаться на параметры привязки, отсутствующие в операторе. В любом случае, именно поэтому рекомендуется указывать CommandType при вызове хранимой процедуры, потому что тогда вы обязательно получите ошибки об отсутствующих и лишних параметрах.   -  person Jeroen Mostert    schedule 01.08.2017
comment
Также не нужно устанавливать свойство параметра TypeName при передаче TVP в прок.   -  person Dan Guzman    schedule 02.08.2017
comment
@JeroenMostert и другие правы, решение состоит в том, чтобы установить CommandType! Вы должны указать это как ответ, чтобы я мог принять. Когда я это делаю, я также могу пропустить Exec в команде, и вы также правы в том, что не нужно устанавливать TypeName!   -  person Jeremy    schedule 03.08.2017


Ответы (1)


Этот код выполняет следующие операторы SQL во время выполнения, которые не возвращают никаких записей:

declare @p3 dbo.IntegerTableParameter
insert into @p3 values(1)
insert into @p3 values(2)
insert into @p3 values(3)
insert into @p3 values(4)
insert into @p3 values(5)

exec sp_executesql N'EXEC [dbo].pList_TableProperties',N'@IDS [dbo].[IntegerTableParameter]

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

exec sp_executesql N'EXEC [dbo].pList_TableProperties *****@IDS*****',N'@IDS [dbo].[IntegerTableParameter]
person sepupic    schedule 02.08.2017