Entity Framework 6.1.3 Hierarchy id not working

c# entity-framework-6 hierarchy

Question

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.

1
2
4/26/2017 10:26:30 PM

Accepted Answer

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.

1
4/27/2017 11:23:02 PM

Popular Answer

Have you tried using using Computed as the DatabaseGeneratedOption instead of None?



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow