使用表值参数调用存储过程不返回任何行

I am using entity framework 6, calling a stored procedure which takes a table value parameter. The stored procedure executes without error but returns no rows. I use profiler to trace the call so I can see how EF executes it. If I manually call the stored procedure a different way it will return rows as expected.

我正在使用实体框架6，调用一个带有表值参数的存储过程。存储过程执行时没有错误但不返回任何行。我使用分析器跟踪调用，以便我可以看到EF如何执行它。如果我以不同的方式手动调用存储过程，它将按预期返回行。

Here's my application code:

这是我的应用程序代码：

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();

        }
    }   
}

This code executes the following sql statements at runtime, which returns no records:

此代码在运行时执行以下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

If I run the following sql, it will return records:

如果我运行以下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

Is there something different I can do on the client side to get this to execute correctly?

我可以在客户端做一些不同的事情来让它正确执行吗？

1 个解决方案

#1

This code executes the following sql statements at runtime, which returns no records:

此代码在运行时执行以下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]

This code returns no record because it does not pass any parameter to stored procedure. This does not cause an error but outputs nothing. The code should look like this (I evidenced missing parameter with *****) :

此代码不返回任何记录，因为它不会将任何参数传递给存储过程。这不会导致错误，但不会输出任何内容。代码看起来应该是这样的（我用*****证明缺少参数）：

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