HierarchyID with .NET 5 and EF Core throws cast error

Question

I'll say straight up at the beginning that I am not a 'real' developer. I'm currently teaching myself C# using a pet project, and I've hit a stumbling block that no amount of Googling or book reviewing has helped thus far. The few people I know with a dev background have pretty much shrugged their shoulders as most do Java or the like, not C#, and the one person that does C# is still using the prior version for .NET Core and has never done anything in Entity Framework.

For my project, I'm using .NET Framework 5, EF Core 5.0.9, and latest version of SQL Server and I'm building a C# Web API. I do have the EntityFrameworkCore.SqlServer.HierarchyId library loaded. Visual Studio recognizes the HierarchyId data type without any issues, and the database and all tables create via a migration, with all tables having the correct column data types on the other side, so I'm fairly certain I have at least most of what I need in place.

On the SQL Server side, I've verified that I can manually create an item in the specified table, and the HierarchyId and all works as expected. I have a number of other tables that do not have any HierarchyId columns, and the create actions for those all work fine.

Despite this, every time I try to create a new item, or to query for an existing item from the table with the HierarchyId, I get an error stating

Unable to cast object of type 'System.Int16' to type 'System.String'

Walking through the steps in the debugger I can verify that the value generates properly (/ in this case) and shows as type Microsoft.EntityFrameworkCore.HierarchyId, and I haven't gotten to the point where I'm trying to serialize anything to return to the client yet, so I'm not sure what's happening.

The code being used is quite simple (generalized slightly):

var entity = new myTableItem
{
     myItemName = myItem.Name,
     myItemDescription = myItem.Description,
     myItemHierarchyId = HierarchyId.GetRoot()
}

myContext.add(entity);
myContext.save();

As soon as it gets to Add, it blows up and provides the previously specified error. I have also tried manually creating a root, and then attempting to retrieve the root entry by it's ID (DB Id not HierarchyId) so I can try adding a child node, but as soon as the query runs, I get the same error and it doesn't go any further (which is how I determined it was the HierarchyId column). For reference, the code I'm using to retrieve the root entry is as follows:

parentItem = myContext.myTableItems.FirstorDefault(x => x.myItemId == myItem.myItemId).myItemHierarchyId

As with the create, walking through the debugger shows an item returned with all the correct properties and values nice and neat, but it still blows up. Log content, for whatever it's worth, is included below for reference (generalized similar to the above, but otherwise unchanged). The last line is something I'm dumping to the log because that's how I always did it with PowerShell as I only just learned this morning how to do based things with the debugger, but it shows I have the data and the correct data type.

2021-09-13 12:28:54.0063 ERROR Something went wrong: Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
 ---> System.InvalidCastException: Unable to cast object of type 'System.Int16' to type 'System.String'.
   at Microsoft.Data.SqlClient.SqlBuffer.get_String()
   at Microsoft.Data.SqlClient.SqlDataReader.GetString(Int32 i)
   at lambda_method260(Closure , DbDataReader )
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeResultSetWithPropagation(Int32 commandIndex, RelationalDataReader reader)
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.Consume(RelationalDataReader reader)
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.Consume(RelationalDataReader reader)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IList`1 entries)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(DbContext _, Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges()
   at MyProject.Repo.RepositoryManager.Save() in ..MyProject.Repo\RepositoryManager.cs:line 155
   at MyProcjet.API.Controllers.MyItemsController.NewMyItem(MyItemCreateDto MyItem) in ..MyItemsController.cs:line 181
   at lambda_method3(Closure , Object , Object[] )
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware.<Invoke>g__Awaited|6_0(ExceptionHandlerMiddleware middleware, HttpContext context, Task task)
2021-09-13 14:39:57.2496 INFO var myItemHierarchyId:/; Type: Microsoft.EntityFrameworkCore.HierarchyId

Can anyone help?

The result of GetRoot() is '/', and the object type is recognized as Microsoft.EntityFrameworkCore.HirearchyId. — Christopher Whitfield, Sep 14 '21 at 12:27
``` namespace MyProject.Entities { [Table("MyItems", Schema = "MyProject")] public class myTableItem { [Key] public int myItemId { get; set; } [Required] public string myItemName { get; set; } public string myItemDescription { get; set; } [Required] public HierarchyId myItemHierarchyId { get; set; } public string myItemOrgLevel { get; set; } public string myItemTextOrgLevel { get; set; } public DateTime? myItemCreated { get; set; } public DateTime? myItemModified { get; set; } } } ``` — Christopher Whitfield, Sep 14 '21 at 12:31

score 0 · Accepted Answer · answered Sep 14 '21 at 07:05

0

Unable to cast object of type 'System.Int16' to type 'System.String'

Maybe the column type in your table is not the same with the type in your myTableItem class.Try to check your myTableItem class and your table,which type in myTableItem class is int,and in table is string.

answered Sep 14 '21 at 07:05

Yiyi You

16,875
1
10
22

Column type in the DB shows up as 'hierarchyid, not null'. As I said, I'm no developer, but the issue only seems to pop up in relation to translating data from or to SQL, as every time the error pops the first two values are 'at Microsoft.Data.SqlClient.SqlBuffer.get_String()' and 'Microsoft.Data.SqlClient.SqlDataReader.GetString(Int32 i)'...for the longest time, I was looking at other props that were submitted as integers, but then I tried just a query for a record and it popped the same error. As per the code sample in my other comment, the item is supposed to be HierarchyId. – Christopher Whitfield Sep 14 '21 at 12:36
+1 your C# model seems to have an incorrect type somewhere (unrelated to the hierarchyid). Try [scaffolding a model from the database](https://learn.microsoft.com/en-us/ef/core/managing-schemas/scaffolding?tabs=dotnet-core-cli) and comparing it to what your DbContext and entity classes look like. – bricelam Sep 14 '21 at 18:08
@bricelam your suggestion hit the mark. In my code-first model, I had a column define as a string, but for whatever reason it created the column as 'small int'. When I scaffolded the table, it set it as 'short'. I hadn't paid any attention to it because the column value auto-generates on the DB side, and it wasn't part of what I was submitting. Thanks a ton!! – Christopher Whitfield Sep 14 '21 at 21:43

HierarchyID with .NET 5 and EF Core throws cast error

1 Answers1