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?

Answer 1

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.