EntityFrameWork和TableValued参数 [英] EntityFrameWork and TableValued Parameter

问题描述

我正在尝试使用 Table-value 参数从 EntityFramework 调用存储过程。

I'm trying to call a stored procedure from EntityFramework which uses Table-value parameter.

但是当我尝试执行函数导入时，我不断收到警告消息，提示-

But when I try to do function import I keep getting a warning message saying -

函数'InsertPerson'在以下位置具有参数'InsertPerson_TVP'
参数索引0的数据类型为表类型，当前目标.NET Framework版本不支持
。该函数被排除在
之内。

The function 'InsertPerson' has a parameter 'InsertPerson_TVP' at parameter index 0 that has a data type 'table type' which is currently not supported for the target .NET Framework version. The function was excluded.

我在这里进行了初步搜索，发现很少的帖子说EntityFrameWork中有可能解决问题，并很少说当前版本不支持。

I did a initial search here and found few posts which says It's possible in EntityFrameWork with some work arounds and few saying it's not supported in current versions.

有人知道这个问题的更好方法或解决方案吗？

Does any one know a better approach or solution for this problem?

推荐答案

我最终还是这样做了，请注意，我们正在开发EF DataContext （不是 ObjectContext ）

I ended up doing this, Please note we are working on EF DataContext(not ObjectContext)

执行带有输出参数的存储过程

       using (DataContext context = new DataContext())
        {                  
          ////Create table value parameter
          DataTable dt = new DataTable();
          dt.Columns.Add("Displayname");
          dt.Columns.Add("FirstName");
          dt.Columns.Add("LastName");
          dt.Columns.Add("TimeStamp");

          DataRow dr = dt.NewRow();
          dr["Displayname"] = "DisplayName";
          dr["FirstName"] = "FirstName";
          dr["LastName"] ="LastName";
          dr["TimeStamp"] = "TimeStamp";             
          dt.Rows.Add(dr);

          ////Use DbType.Structured for TVP
          var userdetails = new SqlParameter("UserDetails", SqlDbType.Structured);
          userdetails.Value = dt;
          userdetails.TypeName = "UserType";

          ////Parameter for SP output
          var result = new SqlParameter("ResultList", SqlDbType.NVarChar, 4000);
          result.Direction = ParameterDirection.Output;

          context.Database.ExecuteSqlCommand("EXEC UserImport @UserDetails, @ResultList OUTPUT", userdetails, result);

          return result == null ? string.Empty : result.Value.ToString();
        }

我的表值参数（UDT表）脚本如下所示：

My Table-Value-Parameter (UDT Table) script looks like this:

CREATE TYPE [dbo].[UserType] AS TABLE (
    [DisplayName]      NVARCHAR (256)   NULL,
    [FirstName]        NVARCHAR (256)   NULL,
    [LastName]         NVARCHAR (256)   NULL,
    [TimeStamp]        DATETIME         NULL
   )

我的存储过程开始像

CREATE PROCEDURE UserImport 
-- Add the parameters for the stored procedure here
@UserDetails UserType Readonly,
@ResultList NVARCHAR(MAX) output  
AS

对于没有输出的存储过程参数，我们不需要添加/传递给SP的任何输出参数。

For Stored procedure without output parameter we don't need any ouput parameter added/passed to SP.

希望对您有所帮助。

这篇关于EntityFrameWork和TableValued参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！