I am trying to use SQL server HierarchyId data type with Entity Framework. Since entity framework doesn't have support for HierarchyId data type I am using Entity Framework fork which has hierarchyid support: https://entityframework.codeplex.com/SourceControl/network/forks/zgabi/EfHierarchyId/latest
DB Table is as follows:
CREATE TABLE OrgStructure(
[Id] [hierarchyid] NOT NULL,
[Level] AS ([Id].[GetLevel]()),
[NodeId] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](50) NULL)
My Entity which uses HierarchyId data type is defined follows:
public class OrgStructure
{
public HierarchyId Id { get; set; }
public Nullable<int> Level { get; set; }
public int NodeId { get; set; }
public string Title { get; set; }
}
and EntityFramework Mapping configuration is defined as:
public class OrgStructureMapping: EntityTypeConfiguration<OrgStructure>
{
public OrgStructureMapping()
{
// Primary Key
this.HasKey(t => t.Id);
// Properties
this.Property(t => t.Id)
.HasColumnType("hierarchyId")
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed);
this.Property(t => t.NodeId)
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
this.Property(t => t.Title)
.HasMaxLength(50);
// Table & Column Mappings
this.ToTable("OrgStructure");
this.Property(t => t.Id).HasColumnName("Id");
this.Property(t => t.Level).HasColumnName("Level");
this.Property(t => t.NodeId).HasColumnName("NodeId");
this.Property(t => t.Title).HasColumnName("Title");
}
}
DBContext is as follows:
public partial class VisibilityHierarchyDBContext : DbContext
{
public virtual DbSet<OrgStructure> OrgStructure { get; set; }
public VisibilityHierarchyDBContext(string nameOrConnectionString)
: base(nameOrConnectionString)
{
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Configurations.Add(new OrgStructureMapping());
}
}
Registered DbContext in startup.cs as follows
public void ConfigureServices(IServiceCollection services)
{
services.AddScoped<VisibilityHierarchyDBContext>((s) => new VisibilityHierarchyDBContext(Configuration.GetConnectionString("VisibilityDB")));
// Add framework services.
services.AddMvc();
}
Using DBContext as follows:
public class HomeController : Controller
{
VisibilityHierarchyDBContext context;
public HomeController(VisibilityHierarchyDBContext context)
{
this.context = context;
}
public IActionResult Index()
{
var o = context.OrgStructure.Take(5).ToList(); /* Exception is thrown here */
return View();
}
}
I am getting following error in Index action shown above:
The 'Id' property on 'OrgStructure' could not be set to a 'Microsoft.SqlServer.Types.SqlHierarchyId' value. You must set this property to a non-null value of type 'System.Data.Entity.Hierarchy.HierarchyId'.
Any help to resolve this error is much appreciated.
I figured it out. Actually error message is bit misleading. When I removed "Level" property from entity definition and mapping it worked like charm. It was breaking because Level column is defined as computed column which auto picks smallint as datatype whereas in entity definition its type is int.
Have you tried using using Computed
as the DatabaseGeneratedOption
instead of None
?