如何使用'hierarchyid'参数从EntityFramework 6调用存储过程
我正在使用WebApi2和EntityFramework6开发服务。 我有一个传统的SQLServer数据库,我的服务必须使用。如何使用'hierarchyid'参数从EntityFramework 6调用存储过程
该数据库严重使用'hierarchyid'数据类型,并且此类型在DB的存储过程中内部使用。
似乎EF6不支持'hierarchyid'数据类型,所以我使用this fork来增加对'hierarchyid'的支持。
尽管从DB中检索与'hierarchyid'类型的工作很好,但我的问题是需要'hierarchyid'作为参数的存储过程。
存储过程是这样的:
CREATE PROCEDURE [dbo].[GetSomethingByNodeId]
(
@startingRoot HIERARCHYID
,@return HIERARCHYID OUTPUT
)
我的客户端调用此存储过程的代码如下所示:
var param1 = new SqlParameter("@startingRoot", new HierarchyId("/"));
var param2 = new SqlParameter{ ParameterName = "@return", Value = 0, Direction = ParameterDirection.Output };
var obj = context.Database.SqlQuery<HierarchyId>("GetSomethingByNodeId" @startingRoot, @return out", param1, param2).ToList();
但不幸的是在调用此查询抛出,说一个异常:
An unhandled exception of type 'System.ArgumentException' occurred in EntityFramework.SqlServer.dll
Additional information: No mapping exists from object type System.Data.Entity.Hierarchy.HierarchyId to a known managed provider native type.
关于我如何使这项工作的任何想法?
不幸的是,MetaType.GetMetaTypeFromValue不允许添加类型(所有支持的类型都是硬编码的)。 我认为你可以用nvarchar参数和转换完成你的目标。
在你的C#代码:
var param1 = new SqlParameter("@startingRoot", "/1/");
var param2 = new SqlParameter { ParameterName = "@return", Value = "", Size = 1000, Direction = ParameterDirection.Output };
var ids = context.Database.SqlQuery<HierarchyId>("GetSomethingByNodeId @startingRoot, @return out", param1, param2).ToList();
var returnedId = new HierarchyId(param2.Value.ToString());
在你的程序中(我写了里面的一些测试代码):
CREATE PROCEDURE [dbo].[GetSomethingByNodeId]
(
@startingRoot nvarchar(max), @return nvarchar(max) OUTPUT
)
as
declare @hid hierarchyid = hierarchyid::Parse('/1/')
select @return = @hid.ToString()
declare @root hierarchyid = hierarchyid::Parse(@startingRoot)
select @root as field
此外,您还可以尝试使用Microsoft.SqlServer.Types和SqlHierarchyId类型是这样的:
var sqlHierarchyId = SqlHierarchyId.Parse("/");
var param1 = new SqlParameter("@startingRoot", sqlHierarchyId) { UdtTypeName = "HierarchyId" };
但是,我认为,这是错误的方向。
Oleg的答案是正确的,hierarchyid仍然没有很好地整合到EF中,并且您应该使用.net中的字符串进行操作。这里是一个更这是从HierarchyId的数据类型的第一天使用的方法:
存储过程:
CREATE PROCEDURE GetSomethingByNodeId
@startingRoot hierarchyid, -- you don't need to use nvarchar here. String which will come from the application will be converted to hierarchyId implicitly
@return nvarchar(500) OUTPUT
AS
BEGIN
SELECT @return = @startingRoot.GetAncestor(1).ToString();
END
在要添加一个部分类为您的EF数据上下文与应用使用普通的旧ADO.NET进行SP调用。也许你会写其他的方式或使用小巧玲珑代替,但这里的主要思想是通过参数作为字符串到SQL Server,它会转换为HierarchyId的暗示:
public partial class TestEntities
{
public string GetSomethingByNodeId(string startingRoot)
{
using (var connection = new SqlConnection(this.Database.Connection.ConnectionString))
{
var command = new SqlCommand("GetSomethingByNodeId", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@startingRoot", startingRoot);
var outParameter = new SqlParameter("@return", SqlDbType.NVarChar, 500);
outParameter.Direction = ParameterDirection.Output;
command.Parameters.Add(outParameter);
connection.Open();
command.ExecuteNonQuery();
return outParameter.Value.ToString();
}
}
}
然后调用该方法,任何其他存储采用方法您的EF背景:
using (var context = new TestEntities())
{
var s = context.GetSomethingByNodeId("/1/1.3/");
}
UPD:这里是如何对传统HierarchyId的过程调用扩展方法看起来像短小精悍(对我来说,它看起来比普通的ADO.NET好得多):
public string GetSomethingByNodeId(string startingRoot)
{
using (var connection = new SqlConnection(this.Database.Connection.ConnectionString))
{
var parameters = new DynamicParameters();
parameters.Add("startingRoot", startingRoot);
parameters.Add("return", null, DbType.String, ParameterDirection.Output, 500);
connection.Open();
connection.Execute("GetSomethingByNodeId", parameters, commandType: CommandType.StoredProcedure);
return parameters.Get<string>("return");
}
}
在这里黑暗中射击......你能改变除了Nvarchar之外的Sproc,然后在Sproc的内部将这个值转换成hirachyId吗? – SimonGates