EntityFrameWork和TableValued参数

本文介绍了EntityFrameWork和TableValued参数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试使用 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.

希望对您有所帮助。