c# - 使用“HasConversion()”或任何其他方法将字符串转换为 Fluent API (EF Core) 中的 HierarchyId?
问题描述
我正在使用带有 EF Core Code First 方法的 FileTables。我已经使用存储过程从字符串中的 FileTable(命名为“DocumentStore”)获取 Path_Locator(PK),现在尝试将其插入另一个表中,在该表中存储有关该文件的信息。我已经在使用代码优先迁移引用 FileTable 的表中手动添加了外键约束。因此,我想将检索到的 Path_Locator(作为字符串)添加到另一个表中。但这里的问题是我检索了字符串中的值,但外键约束的类型为hierarchyid
.
这就是我在 DbContext 类中添加的方式
builder.Entity<ArticleFiles>(entity => {
entity.Property(x => x.PathLocator).HasColumnType("hierarchyid");
});
以下是堆栈跟踪:
Microsoft.EntityFrameworkCore.Database.Command[20102]
Failed executing DbCommand (87ms) [Parameters=[@p0='?' (DbType = Int32), @p1='?' (Size = 450), @p2='?' (Size = 4000), @p3='?' (Size = 4000), @p4='?' (DbType = Int32), @p5='?' (Size = 450), @p6='?' (DbType
= Boolean)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
INSERT INTO [ArticleFiles] ([ArticleId], [PathLocator], [FileDescription], [FileName], [FileTypeId], [GeneratedFileName], [IsAttachment])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6);
fail: Microsoft.EntityFrameworkCore.Update[10000]
An exception occurred in the database while saving changes for context type 'PublicationFinal.Data.DataContext'.
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
---> Microsoft.Data.SqlClient.SqlException (0x80131904): A .NET Framework error occurred during execution of user-defined routine or aggregate "hierarchyid":
Microsoft.SqlServer.Types.HierarchyIdException: 24001: SqlHierarchyId.Parse failed because the input string '0xFD92800AD195392FF75255D5F1E836FB6FD89006BFD8987C037B7061F86242EF9612661F45F8F1084C' is not a
valid string representation of a SqlHierarchyId node.
Microsoft.SqlServer.Types.HierarchyIdException:
at Microsoft.SqlServer.Types.SqlHierarchyId.Parse(SqlString input)
.
The statement has been terminated.
at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__164_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.Tasks.Task.<>c.<.cctor>b__274_0(Object obj)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
ClientConnectionId:f2ef6681-7b4c-41bb-b313-b8c6acbfd34a
Error Number:6522,State:2,Class:16
--- End of inner exception stack trace ---
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(DbContext _, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellatio
nToken)
at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
---> Microsoft.Data.SqlClient.SqlException (0x80131904): A .NET Framework error occurred during execution of user-defined routine or aggregate "hierarchyid":
Microsoft.SqlServer.Types.HierarchyIdException: 24001: SqlHierarchyId.Parse failed because the input string '0xFD92800AD195392FF75255D5F1E836FB6FD89006BFD8987C037B7061F86242EF9612661F45F8F1084C' is not a valid
string representation of a SqlHierarchyId node.
Microsoft.SqlServer.Types.HierarchyIdException:
at Microsoft.SqlServer.Types.SqlHierarchyId.Parse(SqlString input)
.
The statement has been terminated.
at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__164_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.Tasks.Task.<>c.<.cctor>b__274_0(Object obj)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
ClientConnectionId:f2ef6681-7b4c-41bb-b313-b8c6acbfd34a
Error Number:6522,State:2,Class:16
--- End of inner exception stack trace ---
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(DbContext _, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken
)
at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
在将其放入数据库之前,我可以使用 fluentapi 中的某些方法将此字符串转换为 HierarchyId 吗?
解决方案
我想您需要 tostring 函数,但尝试完全回答。
您可以将 hierarchyid 转换为新列或临时表中的字符串,然后使用它。
例如,如果您有此表:
CREATE TABLE SimpleDemo
(
Level hierarchyid NOT NULL,
Location nvarchar(30) NOT NULL,
LocationType nvarchar(9) NULL
);
Go
INSERT SimpleDemo
VALUES
('/1/', 'Europe', 'Continent'),
('/2/', 'South America', 'Continent'),
('/1/1/', 'France', 'Country'),
('/1/1/1/', 'Paris', 'City'),
('/1/2/1/', 'Madrid', 'City'),
('/1/2/', 'Spain', 'Country'),
('/3/', 'Antarctica', 'Continent'),
('/2/1/', 'Brazil', 'Country'),
('/2/1/1/', 'Brasilia', 'City'),
('/2/1/2/', 'Bahia', 'State'),
('/2/1/2/1/', 'Salvador', 'City'),
('/3/1/', 'McMurdo Station', 'City');
Go
您可以找到如下结果:
SELECT CAST(Level AS nvarchar(100)) AS [Converted Level], *
FROM SimpleDemo ORDER BY Level
你可以使用功能:
Select Level.ToString()
From SimpleDemo
我希望这有帮助
如果你想使用 c# 函数来转换这个 hierarchyid 你可以使用这个代码,但你应该安装 SSDT,SQLCLIENT :
using System;
using System.Collections;
using System.Text;
using Microsoft.SqlServer.Server; // SqlFunction Attribute
using Microsoft.SqlServer.Types; // SqlHierarchyId
public partial class HierarchyId_Operations
{
[SqlFunction(FillRowMethodName = "FillRow_ListAncestors")]
public static IEnumerable ListAncestors(SqlHierarchyId h)
{
while (!h.IsNull)
{
yield return (h);
h = h.GetAncestor(1);
}
}
public static void FillRow_ListAncestors(
Object obj,
out SqlHierarchyId ancestor
)
{
ancestor = (SqlHierarchyId)obj;
}
public static HierarchyId CommonAncestor(
SqlHierarchyId h1,
HierarchyId h2
)
{
while (!h1.IsDescendantOf(h2))
h1 = h1.GetAncestor(1);
return h1;
}
}
你可以在这里找到这个样本
推荐阅读
- c# - 通过用户管理器设置 RefreshToken 的 ASP.NET Core 用户管理器问题
- c - 覆盖 CTRL-HANDLERS 窗口 C
- javascript - html属性的java脚本替换
- visual-studio-extensions - 如何在 Visual Studio Marketplace 中发布以供直接下载
- linux-kernel - 嵌入式 linux 构建/引导问题
- laravel - Date not showing in date input field laravel 5.8
- c++ - 如何将纪元时间转换为 time_point
- javascript - 闭包函数中的实际参数在哪里?
- tensorflow - 如何实现强大的背景去除?
- jsf - JSF 是否仍然与 Microprofile 相关