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

Question

I am developing a service using WebApi2 and EntityFramework6. I have a legacy SQLServer DB that my service must work with.

That DB is using heavily the 'hierarchyid' data type and this type is used internally in DB's stored procedures.

Seems like EF6 is not supporting 'hierarchyid' data type, so i used this fork that adds support for 'hierarchyid'.

While the retrieval from the DB is working great with the 'hierarchyid' type, my problem is with the Stored Procedures that need a 'hierarchyid' as a parameter.

The stored procedure looks like this:

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

My client code for invoking this stored procedure looks like this:

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 query throws 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?

Shot in the dark here... Can you change the Sproc to except a Nvarchar and then cast the value to a hirachyId internally in the Sproc? — SimonGates, Sep 08 '16 at 21:03

OlegAxenow · Answer 1 · 2016-09-08T20:55:28.563

Unfortunately, MetaType.GetMetaTypeFromValue does not allow to add types (all supported types are hardcoded). I think you can accomplish your goal with nvarchar parameters and conversions.

In your 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 code 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

Also, you can try to use Microsoft.SqlServer.Types and SqlHierarchyId type like this:

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

But, I think, this is wrong direction.

Denis Reznik · Answer 2 · 2016-09-09T14:35:28.623

Oleg's answer is correct, hierarchyid is still not integrated to the EF very well, and you should operate with strings in .net. Here is one more approach which was used from the first days of HierarchyId datatype:

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 an application you are adding a partial class for your EF data context with the SP call using plain old ADO.NET. Probably you will write this other way or use Dapper instead, but the main idea here is passing parameter as string to SQL Server, and it will convert to the HierarchyId implicitly:

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 as any other stored procedure using your EF context:

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

UPD: here is how the extension method for legacy HierarchyId procedure call will look like with Dapper (as for me it looks much better than plain 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");
            }
        }

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

2 Answers2