How to call a stored procedure from EntityFramework 6 with the parameter 'hierarchyid'

Question

I am developing a service using WebApi2 and EntityFramework6. I have an outdated SQLServer database that my service should work with.

This database heavily uses the 'hierarchyid' data type, and this type is used inside DB stored procedures.

It seems that EF6 does not support the 'hierarchyid' data type, so I used this fork , which adds support for the hierarchy.

While retrieving from a database works fine with a hierarchyid type, my problem is with stored procedures that need a hierarchy as a parameter.

The stored procedure is as follows:

CREATE PROCEDURE [dbo].[GetSomethingByNodeId] ( @startingRoot HIERARCHYID ,@return HIERARCHYID OUTPUT ) 

My client code to call this stored procedure is as follows:

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

But unfortunately, calling this request raises an exception that says:

 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. 

Any ideas on how I can make this work?

Answer 1

Unfortunately, MetaType.GetMetaTypeFromValue does not allow adding types (all supported types are hard-coded). I think you can achieve your goal with nvarchar parameters and conversions.

In C # code:

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

In your procedure (I wrote some test codes inside):

 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 

Alternatively, you can try using Microsoft.SqlServer.Types and SqlHierarchyId, for example:

 var sqlHierarchyId = SqlHierarchyId.Parse("/"); var param1 = new SqlParameter("@startingRoot", sqlHierarchyId) { UdtTypeName = "HierarchyId" }; 

But, I think this is the wrong direction.

Answer 2

Oleg's answer is correct, the hierarchy is still not very well integrated into EF, and you have to work with strings in .net. Here is another approach that has been used since the early days of the HierarchyId data type:

Stored Procedure:

 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

In the application, you add a partial class for your EF data context with an SP call using the plain old ADO.NET. You will probably write it in another way or use Dapper instead, but the main idea here is to pass the parameter as a SQL Server string, and it will implicitly convert to 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(); } } } 

Then call this method like any other stored procedure using your EF context:

 using (var context = new TestEntities()) { var s = context.GetSomethingByNodeId("/1/1.3/"); } 

UPD: this is how the extension method for an obsolete call to the HierarchyId procedure will look like with Dapper (it looks much better to me than regular 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"); } }