Does Linq to SQL or Linq to Entities 4.0 support the hierarchyid datatype

Question

Is their a way to use linq to SQL/Entities 4.0 to work with the hierarchy datatype?

Answer 1

None of the Microsoft family of ORMs can currently use CLR User-Defined Types (which includes the built-in hierarchyid and geospatial types) as column types.

If you use these in your database, you have two workarounds:

1. Add a computed column to the hierarchy table with the expression CAST(hid AS varbinary892)). You'll need to make sure that you include this column in every query (including stored procedures) that gets used by Linq. Then, add this column to the entity mapping.

   At this point, you can extend the entity's partial class to add the "real" hierarchyid column as its own property by adding a reference to Microsoft.SqlServerTypes and using the BinaryReader/BinaryWriter classes to convert the BLOB data to/from SqlHierarchyId.

   Note that you can't write Linq queries against this column. You'll just get a "no supported translation" error if you try. So keep in mind that this is a limited workaround.

2. The other option, and the one I generally prefer, is to not use the hierarchyid column at all from within L2S/EF. Add a separately-indexed surrogate auto-inc key to the hierarchy table and treat the hierarchyid as an implementation detail. Use UDFs and Views to implement hierarchical queries that only require the surrogate ID as a parameter.

   This sounds like a pain, but it's not really that bad if you work this way from the get-go. Before Microsoft introduced the hierarchyid type, I was using an adaptation of Dennis Forbes' materialized path hierarchy, which was based around an adjacency list and maintaining the path as a kind of denormalization. hierarchyid makes this a lot easier to do.

   Unfortunately I don't have a complete working example of everything you need to do to maintain the correct associations between a hierarchyid and adjacency list, but if you read Dennis's article it should be a good start. Don't implement the materialized path, use hierarchyid instead, but read the sections about implementing self-maintaining hierarchies with triggers.

   If Microsoft ever does implement support for hierarchyid in their ORMs, it would be easy to remove the adjacency list and switch exclusively to a solution based on hierarchyid. But since managing hierarchies based around hierarchyid require a slew of stored procedures to maintain anyway (since you don't get "automatic" IDs), you should get comfortable with writing a lot of SQL UDFs and Stored Procedure abstractions around hierarchical queries.