Support SQL Server HierarchyId data type

Topics: EF Runtime, General
Dec 21, 2012 at 10:15 PM
Edited Dec 25, 2012 at 5:22 PM

There is an open WorkItem about HierarchyId in the issue tracker: http://entityframework.codeplex.com/workitem/128

I needed this feature too, so I implemeted it in my own fork:

http://entityframework.codeplex.com/SourceControl/network/forks/zgabi/EntityFrameworkHierarchyId (comment: HierarchyId support added)

I would appreciate if this modification was merged into the main branch with a contribution.

Developer
Dec 21, 2012 at 10:25 PM

Hi zgabi,

We would welcome a contribution in this area. Before we can look at your code you will need to submit a Contributor License Agreement (CLA) as described here: http://entityframework.codeplex.com/wikipage?title=Contributing

We will also need to review the design and API and work with you if there need to be any changes. It would be very useful if you could give a brief description of what you have added in terms of public API and how you expect it to be used. (This may be obvious from the code but I haven't looked at it yet due to the need for a CLA.)

Once we reach agreement on the design, API, tests, etc., then the normal process would be fore you to submit a pull request, which is also described in the link above.

I look forward to working with you on this!

Thanks,
Arthur

Dec 21, 2012 at 11:16 PM
Edited Dec 21, 2012 at 11:27 PM

Hi Arthur,

Tomorrow I'll print/sign/scan and send the CLA.

I have added a new public class (HierarchyId), which is almost the same as the SqlHierarchyId class, but I didnt want to add refenrece to the Microsoft.SqlServer.Types assembly. (But tt is very easy to add a reference to that assembly and use the original SqlHierarchyId class instead of HierarchyId class)

The implementation of the HierarchyId type is very similar to the implementation of the DbGeometry and DbGeography types.

Example:

Code first model:

public class Table1
{
    public int Id { get; set; }
    public HierarchyId Path { get; set; }
}

public class MyContext : DbContext
{
    public DbSet<Table1> Table1 { get; set; }
}
How to use:
  
var items = c.Table1.OrderByDescending(o => o.Path).ToArray();

foreach (var table1 in items)
{
   Console.WriteLine(table1.Id + " " + table1.Path);
}

output:

4 /2/
3 /1/2/
2 /1/1/
1 /1/
var items = c.Table1.Where(o=>o.Path.IsDescendantOf(new HierarchyId("/1/")) == 1).Select(
    o => new
        {
            Id = o.Id,
            OrigPath = o.Path,
            Path = o.Path.GetReparentedValue(new HierarchyId("/1/"), HierarchyId.GetRoot()),
            Level = o.Path.GetLevel()
        }).ToArray();


foreach (var table1 in items)
{
    Console.WriteLine(table1.Id + "; " + table1.OrigPath + "; " + table1.Path + "; " + table1.Level);
}

output:

1; /1/; /; 1
2; /1/1/; /1/; 2
3; /1/2/; /2/; 2

Thanks,

Gábor

Developer
Dec 21, 2012 at 11:22 PM

Hi Gábor,

Thanks for providing this information. Several important members of the team are on vacation at the moment. We will certainly discuss this in more detail as a priority after the holiday period and get back to you then. Having the signed CLA will make things much easier.

Thanks again,
Arthur

Dec 22, 2012 at 10:50 AM

Hi Arthur,

Ok, I'm on vacation, too. I checked the CLA and I can submit it only in January because I have to sign it with my employer. However I made this modification in my free time, and the commited codes are not related to my official work.

Gábor

Developer
Dec 23, 2012 at 10:57 PM

Just a thought - if HierarchyId is a new EDM type (I did not look at the code), wouldn't it require revving CSDL (and as a result all other artifacts)?

Dec 24, 2012 at 10:35 AM

I tried to use hierarchyid in CDSL, and it was working. I haven't modified the CSDL parsing.

I have created a changeset (temporary added to SpatialTest class) , could you please check it (without CLA)? It contains modifications only in FunctionalTest project.

http://entityframework.codeplex.com/SourceControl/network/forks/zgabi/EntityFrameworkHierarchyId/changeset/e88ede671f9c

When I run the DbQuery_SelectMany_with_TVFs_and_spatial_types_works function the result is:

1 SRID=4326;POINT (-122.31946 47.625112) Supplier1 /1/
10 SRID=4326;POINT (-122.341529 47.611693) Supplier10 /3/
11 SRID=4326;POINT (-122.352842 47.6186) Supplier11 /3/1/
...

 Both the insert (in SpatialNorthwindInitializer.cs) and the select works.

Developer
Dec 24, 2012 at 6:08 PM

I will wait for the CLA before I take a look.

Jan 11, 2013 at 6:56 AM

I sent the CLA.

Developer
Jan 11, 2013 at 11:03 PM

zgabi,

Thanks for submitting the CLA. One of the EF team members is going to look at your code and also do some broader thinking around the best way to support hierarchy IDs and will get back to you.

Thanks,
Arthur

Developer
Jan 23, 2013 at 5:48 PM

Hi zgabi,

We have spent some time on the EF team brainstorming what we think the best approach is for tackling hierarchy ID and other related features. The conclusions from these discussions are as follows:

  • It is currently hard for EF to support new data types because the Entity Data Model (EDM) needs to be updated to support each new type. There are times when adding types as first-class members of EDM adds enough value that it is worth doing all the work and reving the EDM version. However, there are also cases where the value of this is not high, and we believe that HierarchyID is probably one of these cases. In such cases we really want to make it easy to support new types without the EF internals having hard-coded information about those types. Instead we would need some form of extension points that can handle reading and converting from a data reader and also converting appropriately for updates. There probably also needs to be a way for the store model to handle the new type so that areas like Migrations can handle scaffolding for them.
  • The dependency we took on the SQL Types assembly for spatial is problematic in several ways—for example, different versions of the assembly, needing to download and install the assembly on the client machine even when the rest of EF is bin deployable, the dynamic binding, and so on. It may be that we need to use the SQL Types assembly for HierarchyID but it is not clear that this is the case.
  • It’s not clear what the most useful mappings are for hierarchy ID. Having a type like the one you added may be an approach we take, but it couples the application’s model assembly to EF, which is usually something we avoid. (I know that the spatial types do this as well, but this is a compromise we wish we could have avoided.) In line with the first bullet point, it would be good if the type we map to is not fixed. It may also be useful to map things like navigation properties over the hierarchical data, and in such cases maybe the HierarchyID itself can just be stored as a string property. We don’t necessarily need all of this to be done in a contribution, but whatever mapping we do choose should be flexible enough to handle such things in the future.
  • The schedule and work we have planned for EF6 doesn’t allow us the time to go deeply into the design for everything above. However, we do plan to return to it after EF6 is released. At that time we would like to re-engage with you if you are still interested and potentially take some of your code or any additional work you do in this area as a contribution.

In summary, we appreciate greatly the work you have done here and your desire to contribute to EF, but for now we’re going to hold off on the contribution until we have a bit more time to ensure that we’re creating the best underlying building blocks for this area. 

Thanks,
Arthur

Nov 14, 2013 at 2:48 PM
Edited Nov 14, 2013 at 2:49 PM
Hi guys, are there any updates on this feature?
It would be very useful to have HierarchyId in list of supported types.
I can help you with development in case it will speed-up process.

Tomas Jecha
MVP
Nov 14, 2013 at 3:27 PM
Hi Tomas,

There is no official HierarchyID support, yet but i created my own nuget package with HierarchyID support:
https://www.nuget.org/packages/EntityFrameworkWithHierarchyId/

Or you can build your own package if you don't trust in my dll:)
Source of the version 6.0.1:
https://entityframework.codeplex.com/SourceControl/network/forks/zgabi/EfHierarchyId?branch=hotfix-6.0.1

zgabi
Dec 24, 2013 at 7:25 AM
Hi, guys!
Can anybody tell me, please, how to make this fork works? I have install it via NuGet Package Manager Console (using Install-Package EntityFrameworkWithHierarchyId -Version 6.0.2 command). But when I try to import the database table, containig hierarchyid field, to the Entity Data Model component, error appears, that the hierarchyid data type doesn't supported in the current version of the Entity Framework! Looks like, my Visual Studio Express 2013 for Web didn't "see" installed package with EF with hierarchyid :-(
Can anybody help me, please, with this issue?
Dec 24, 2013 at 12:05 PM
Hi,

In the nuget package you can find only the entityframework dlls, the tools which you try to use in VS is the original EF tools. It not supports hierarchyid. So you can do the following:
remove hierarhyid columns from DB, generate the EF codefirst model with the official EF power tools. Add hierarhyid columns manually to the code first model.

zgabi
Dec 24, 2013 at 12:23 PM
Thank you, zgabi! I'll try solution, you offered!
Dec 24, 2013 at 3:22 PM
Edited Dec 24, 2013 at 3:25 PM
zgabi wrote:
Hi,

In the nuget package you can find only the entityframework dlls, the tools which you try to use in VS is the original EF tools. It not supports hierarchyid. So you can do the following:
remove hierarhyid columns from DB, generate the EF codefirst model with the official EF power tools. Add hierarhyid columns manually to the code first model.

zgabi
Zgabi, I'm sorry, but it's still doesn't work :( I try to add hierarchyid column, but there's no such type in my Visual Studio. May be I need to do some additional actions to correctly install EF with hierarchyid? There's my steps to add EF with hierarchyid:
  1. Create a new MVC4 project (Empty template, .NET Framework 4.5)
  2. Go to NuGet Package Manager Console
  3. Type "Install-Package EntityFrameworkWithHierarchyId -Version 6.0.2", press Enter
  4. I have following messages (I have a Russian interface, so I translate messages manually):
"EntityFrameworkWithHierarchyId 6.0.2" successfully installed.
Adding "EntityFrameworkWithHierarchyId 6.0.2" to MvcApplication1.
"EntityFrameworkWithHierarchyId 6.0.2" successfully added to MvcApplication1.
Further, there're several errors like this
Exception when call "LoadFrom" with "1" argument: "Can't load file or package "EntityFramework.PowerShell.Utility, Version=6.0.0.0, Culture=neutral, PublicKeyToken=6847f3395fc61b47" либо одну из их зависимостей. Сбой при проверке правильности строгого имени. (Исключение из HRESULT: 0x8013141A)"
C:\Users\keyssser ekssselle\SkyDrive\разработка\MvcApplication1\packages\EntityFrameworkWithHierarchyId.6.0.2\tools\EntityFramework.psm1:669 знак:62
  • $utilityAssembly = [System.Reflection.Assembly]::LoadFrom <<<< ((Join-Path $ToolsPath EntityFramework.PowerShell.Utility.dll))
    • CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    • FullyQualifiedErrorId : DotNetMethodException
That's all. After this I create a class Class1.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;

namespace MvcApplication1.Models
{
public class Class1
{
     HierarchyId tmp;    }
}
  1. Try to type Hierarchyid, but IntelliSence can't find this type. When I try to compile a project, error appears, that "HierarchyId type or assembly not found".
    Tell me, please, what's wrong in this scenario?
Dec 24, 2013 at 8:33 PM
I tried your steps, but I dont have any error message.
HierarchyId is in System.Data.Entity.Hierarchy namespace. Do you have gtalk or skype?
Btw: the dlls in 6.0.2 package are not signed, you have to sign them manually. The snk file is in the (EfHierarchyId's) repository.
Feb 11 at 6:38 AM
Edited Feb 11 at 7:34 AM
Hi Man
This is going to kill me I encountered with same error too.

Exception calling "LoadFrom" with "1" argument(s): "Could not load file or assembly 'EntityFramework.PowerShell.Utility,
Version=6.0.0.0, Culture=neutral, PublicKeyToken=6847f3395fc61b47' or one of its dependencies. Strong name validation fai
led. (Exception from HRESULT: 0x8013141A)"
At D:\ 2012\Projects\ClassLibrary1\packages\EntityFrameworkWithHierarchyId.6.0.2\tools\EntityFramework.psm1:669 char:62
  • $utilityAssembly = [System.Reflection.Assembly]::LoadFrom <<<< ((Join-Path $ToolsPath EntityFramework.PowerShell.Ut
    ility.dll))
    • CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    • FullyQualifiedErrorId : DotNetMethodException
You cannot call a method on a null-valued expression.
At D:\ 2012\Projects\ClassLibrary1\packages\EntityFrameworkWithHierarchyId.6.0.2\tools\EntityFramework.psm1:670 char:50
  • $dispatcher = $utilityAssembly.CreateInstance <<<< (
    • CategoryInfo : InvalidOperation: (CreateInstance:String) [], RuntimeException
    • FullyQualifiedErrorId : InvokeMethodOnNull
Exception calling "CreateInstanceFrom" with "8" argument(s): "Could not load file or assembly 'EntityFramework.PowerShell
, Version=6.0.0.0, Culture=neutral, PublicKeyToken=6847f3395fc61b47' or one of its dependencies. Strong name signature co
uld not be verified. The assembly may have been tampered with, or it was delay signed but not fully signed with the corr
ect private key. (Exception from HRESULT: 0x80131045)"
At D:\ 2012\Projects\ClassLibrary1\packages\EntityFrameworkWithHierarchyId.6.0.2\tools\EntityFramework.psm1:698 char:31
  • $domain.CreateInstanceFrom <<<< (
    • CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    • FullyQualifiedErrorId : DotNetMethodException
Where is snk file to sign this dll I couldn't find repository or snk file.
I hope you give me reply soon because its urgent.
Feb 11 at 6:40 AM
Where is this snk file and also repository I couldn't find anything...
Feb 11 at 6:58 AM
Hi,

The snk file is here: https://entityframework.codeplex.com/SourceControl/network/forks/zgabi/EfHierarchyId/changeset/view/99489bd70239c8c96708da10e89481bb96ab7c22#src/
EcmaPublicKey.snk. (This is not the original ecma public key, it is my own snk file, which contains the private key, too)

Or you can try to use the latest ("unstable") version (https://www.nuget.org/packages/EntityFrameworkWithHierarchyId/6.1.0-pre1). It is already signed.
I can send you my EFTools version, too, if you want to generate models (which contains hierarchyid) from database.
Feb 11 at 7:32 AM
Edited Feb 11 at 7:33 AM
zgabi wrote:
Hi,

The snk file is here: https://entityframework.codeplex.com/SourceControl/network/forks/zgabi/EfHierarchyId/changeset/view/99489bd70239c8c96708da10e89481bb96ab7c22#src/
EcmaPublicKey.snk. (This is not the original ecma public key, it is my own snk file, which contains the private key, too)

Or you can try to use the latest ("unstable") version (https://www.nuget.org/packages/EntityFrameworkWithHierarchyId/6.1.0-pre1). It is already signed.
I can send you my EFTools version, too, if you want to generate models (which contains hierarchyid) from database.
Hi man thanks a lot I installed unstable version its work well. I test it :
CREATE TABLE [dbo].[Clas] (
[ClasId] INT                 IDENTITY (1, 1) NOT NULL,
[fff]    [sys].[hierarchyid] NULL,
CONSTRAINT [PK_dbo.Clas] PRIMARY KEY CLUSTERED ([ClasId] ASC)
  );
Its created from my db code first model.
Feb 12 at 12:09 PM
Edited Feb 12 at 1:16 PM
hi man
I hope you be good I have a problem with this mainly problem is how to use it?
---w1
---w2
---w3
------w31
------w32
------w33
-----------w331
-----------w332
-----------w334
-----------------w3341
-----------------w3342
---w4
---w5
This is my structure. At beginning I have query for reading all parent then (w1,w2,w3,w4 and w5) I have a column in my datable that which row is parent. Then I want to read all of the node children at the same time for example for w3:
------w31
------w32
------w33
-----------w331
-----------w332
-----------w334
-----------------w3341
-----------------w3342

Performance is very important. I have one table only but it will have a n million records with at least 10 level, so how can I make index based on HierarchyId field too to have a better performance.

Thanks
Feb 12 at 1:39 PM
I wrote an exampel earlier:
            foreach (var table1 in c.Employees.Where(x => x.Node.IsDescendantOf(HierarchyId.Parse("/1/"))))
            {
                Console.WriteLine(table1.EmployeeId + " " + table1.Name + " " + table1.Node);
            }
Result:
2 Emp1 /1/
4 Emp3 /1/1/
5 Emp4 /1/1/1/
7 Emp6 /1/2/

About indexes: it is not EF specific you can use the same indexes.
Feb 12 at 6:19 PM
Hi
Yes but it had problem on where condition I fixed it
      
               

               var items = db.Comments.Where(o => o.Position.IsDescendantOf(new HierarchyId(path))).Select(
                    o => new
                   {
                      // Id = o.CommentId,
                       OrigPath = o.Position,
                     //  Path = o.Position.GetReparentedValue(new HierarchyId(path), HierarchyId.GetRoot()),
                      // Level = o.Position.GetLevel()
                   }).ToArray();

                foreach (var t in items)
                {
                    Console.WriteLine(t.OrigPath);
                } 
            
    
    
Anyway, thank you it was big help.
Mar 6 at 10:19 AM
Hi!

I have the problem with running HierarchyID support in my MVC5 project. :(

This part of code throws System.NotSupportedException exception.
(The specified type member 'PathId' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.)
public ActionResult Index()
{
   var items = db.Categories.OrderByDescending(o => o.PathId).ToArray();
   return View(items);
}
Definition in edmx file:
namespace Administration.Models
{
    using System;
    using System.Collections.Generic;
    using System.Data.Entity.Hierarchy;
    
    public partial class Category
    {
        public Category()
        {
            this.CategoriesParameters = new HashSet<CategoriesParameter>();
            this.Products = new HashSet<Product>();
        }
    
        public int Id { get; set; }
        public HierarchyId PathId { get; set; }
        public string PageTitle { get; set; }
        public string PageDescription { get; set; }
        public string PageKeywords { get; set; }
        public string Name { get; set; }
        public string Culture { get; set; }
        public bool IsEnabled { get; set; }
    
        public virtual ICollection<CategoriesParameter> CategoriesParameters { get; set; }
        public virtual ICollection<Product> Products { get; set; }
    }
}
Installed: EntityFramework.HierarchyId 6.1.0-pre3

Thank you for your help.
Jiri
Mar 6 at 6:39 PM
Hi,

I was not able to reproduce your problem. Please could you send me a whole simplified project? +Database create script

zgabi
Mar 7 at 8:12 AM
Hi zgabi,

thank you for fast reply. I have sent you the download link via PM.

This is step by step, what i do (VS 2013):
  1. New project - ASP.NET Web Application - MVC
  2. Models - Add - ADO.NET Entity Data Model "HidTestModel" - Generate from database
    (hierarchyId is ignored by generator - OK)
  3. PM> Install-Package EntityFrameworkWithHierarchyId -Pre
  4. Manually add hierarchyId field to Category.cs
  5. Add the query to HomeController.cs
  6. Run project - it throws System.NotSupportedException exception.

And second issue:
  1. It seems that there is some collision between Microsoft.AspNet.Identity.EntityFramework and EntityFrameworkWithHierarchyId.
    File: Models - IdentityModels.cs
    ApplicationDbContext - throws EntityFramework assembly not referenced error during compilation
    When EntityFrameworkWithHierarchyId is uninstalled it works fine.
Mar 24 at 12:02 PM
zgabi wrote:
Hi,

I was not able to reproduce your problem. Please could you send me a whole simplified project? +Database create script

zgabi
Hi zgabi
There is a problem with it,
It couldn't be used with the new Identity system of Asp.net

VS 2013, EFHier..6.1, MVC 5.1

The Assembly Microsoft.AspNet.Identity.EntityFramework is tied to the guid of the original EF.

If you or anyone found any solution please share it with us, thanks...
Mar 24 at 12:11 PM
Hi Heelsa,

You can disassemble the Identity dll with ildasm, modify the publickeytoken of the referenced EF, and compile again with ilasm. It is a little hack, and i don't know whether it is legal or not. I haven't tried it, but it should work.
Mar 24 at 12:12 PM
Edited Mar 24 at 12:13 PM
Hi wntcz,

I've sent you the problem in your project on 7th of March to your email address. Is everything ok now?
Mar 24 at 12:18 PM
zgabi wrote:
Hi Heelsa,

You can disassemble the Identity dll with ildasm, modify the publickeytoken of the referenced EF, and compile again with ilasm. It is a little hack, and i don't know whether it is legal or not. I haven't tried it, but it should work.
Thanks mentioning this, I did a similar activity :D actually with refle.ctor, I've built a new version using EFHier... as a reference, not sure exactly about the problem but still there's something wrong there, I'm getting some kinda assembly manifest exception, "Could not load file or assembly 'EntityFramework',
If I managed this problem I'll let you know, and if you did this please providethe hacky-version of the 'EF...Identity' file. thanks.
Mar 24 at 1:41 PM
Done. Thanks...