sql-server - 如何在 SQL Server 中使用实体框架代码优先迁移更新 NVARCHAR(MAX) 列？
问题描述
我创建了一个小系统来管理带有代码优先迁移的 EF 存储过程，主要是因为我想通过迁移来管理对数据库的所有更改。
该系统自动管理存储过程的Create、Alter和Drop以及down方法中的反向更改。
简单来说，这些步骤是：
- 创建一个实体（表），然后将过程的主体存储在列中。这是我遇到问题的专栏。实体是
[buy].[buying_rckStoredProcedureDefinition] - 创建一个配置来探索一个包含
.sql文件的文件夹，每个文件都包含一个存储过程的定义 - 对于每个文件，执行一个“HasData”，将文件名作为记录的键，将内容作为
tCreateOrUpdate NVARCHAR(MAX)列的值 - 为 SQL Server 中用于
sp_executesql执行创建或更改过程的表创建 INSERT 或 UPDATE 触发器，并为删除记录时执行 DROP 过程的 DELETE 创建触发器。
该系统在两个月前一直运行良好，但今天一位开发人员提交了一个.sql包含 4014 个字符的文件，然后它就崩溃了。
以下是我迁移的内容：
using Microsoft.EntityFrameworkCore.Migrations;
namespace Data.Command.Migrations
{
public partial class buying_ST014_M : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.UpdateData(
schema: "buy",
table: "buying_rckStoreProcedureDefinition",
keyColumn: "sProcedure",
keyValue: "buying_readComparativeTable",
column: "tCreateOrUpdate",
value: "/*-----------------------------------------------------------------------------------------------------------------*/\n/* Objetivo : Devolver un cuadro comparativo con todo sus listados correspondiente. */\n/* Parámetros: @nApprovingEmployeeId: Identificador del empleado inmediato superior. */\n/*-----------------------------------------------------------------------------------------------------------------*/\nCREATE OR ALTER PROCEDURE buy.buying_readComparativeTable @nComparativeTableId INTEGER AS\nBEGIN\n SELECT nComparativeTableId Id,\n nQuoteOrderId QuoteOrderId,\n sNoteSSGG NoteSSGG,\n sSubject Subject,\n nCreatorEmployee CreatorEmployee,\n nResponsibleEmployee ResponsibleEmployee,\n nComparativeTableVisualizationId VisualizationId,\n nBuyingRequestTypeId BuyingRequestTypeId,\n nCustomFormatDocId CustomFormatDocId,\n nCompany CompanyId,\n nRegional RegionalId,\n nCurrencyId CurrencyId,\n nMonetaryAuthEmployee MonetaryAuthEmployee,\n sMonetaryAuthNote MonetaryAuthNote,\n dCreateDate CreateDate,\n nComparativeTableStatusId StatusId\n FROM buy.buying_ComparativeTable (NOLOCK)\n WHERE nComparativeTableId=@nComparativeTableId\n ORDER BY nComparativeTableId;\n\n\n SELECT nComparativeTableItemId Id,\n nComparativeTableId ComparativeTableId,\n nQuoteOrderItemId QuoteOrderItemId,\n sProductUNSPSC ProductUNSPSC,\n sProductUNSPSC Unit,\n sDescription Description,\n nQuantity Quantity,\n nUnitMeasurementId UnitMeasurementId\n FROM buy.buying_ComparativeTableItem (NOLOCK)\n WHERE nComparativeTableId=@nComparativeTableId\n ORDER BY nComparativeTableItemId,nComparativeTableId;\n \n SELECT nComparativeTableSectionId Id,\n nComparativeTableId ComparativeTableId,\n nQuoteId QuoteId,\n nProviderId ProviderId,\n nProviderId ProviderName,\n bSuggested Suggested,\n nTotalPrice TotalPrice\n FROM buy.buying_ComparativeTableSection (NOLOCK)\n WHERE nComparativeTableId=@nComparativeTableId\n ORDER BY nComparativeTableSectionId,nComparativeTableId;\n \n SELECT SectionItems.nComparativeTableSectionItemId Id,\n SectionItems.nQuoteItemsId QuoteItemsId,\n SectionItems.nComparativeTableSectionId ComparativeTableSectionId,\n SectionItems.nComparativeTableItemId ComparativeTableItemId,\n SectionItems.nUnitPrice UnitPrice,\n SectionItems.nQuantity Quantity,\n SectionItems.bItemSuggested ItemSuggested\n FROM buy.buying_ComparativeTableSectionItem as SectionItems\n INNER JOIN buy.buying_ComparativeTableSection as Sections\n ON Sections.nComparativeTableSectionId = SectionItems.nComparativeTableSectionId\n where Sections.nComparativeTableId=@nComparativeTableId\n ORDER BY SectionItems.nComparativeTableSectionItemId,SectionItems.nComparativeTableSectionId,SectionItems.nComparativeTableItemId ;\nEND");
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.UpdateData(
schema: "buy",
table: "buying_rckStoreProcedureDefinition",
keyColumn: "sProcedure",
keyValue: "buying_readComparativeTable",
column: "tCreateOrUpdate",
value: "/*-----------------------------------------------------------------------------------------------------------------*/\n/* Objetivo : Devolver un cuadro comparativo con todo sus listados correspondiente. */\n/* Parámetros: @nApprovingEmployeeId: Identificador del empleado inmediato superior. */\n/*-----------------------------------------------------------------------------------------------------------------*/\nCREATE OR ALTER PROCEDURE buy.buying_readComparativeTable @nComparativeTableId INTEGER AS\nBEGIN\n SELECT nComparativeTableId Id,\n nQuoteOrderId QuoteOrderId,\n sNoteSSGG NoteSSGG,\n sSubject Subject,\n nCreatorEmployee CreatorEmployee,\n nResponsibleEmployee ResponsibleEmployee,\n nComparativeTableVisualizationId VisualizationId,\n nBuyingRequestTypeId BuyingRequestTypeId,\n nCustomFormatDocId CustomFormatDocId,\n nCompany CompanyId,\n nRegional RegionalId,\n nCurrencyId CurrencyId,\n nMonetaryAuthEmployee MonetaryAuthEmployee,\n sMonetaryAuthNote MonetaryAuthNote,\n dCreateDate CreateDate,\n nComparativeTableStatusId StatusId\n FROM buy.buying_ComparativeTable (NOLOCK)\n WHERE nComparativeTableId=@nComparativeTableId\n ORDER BY nComparativeTableId;\n\n\n SELECT nComparativeTableItemId Id,\n nComparativeTableId ComparativeTableId,\n nQuoteOrderItemId QuoteOrderItemId,\n sProductUNSPSC ProductUNSPSC,\n sProductUNSPSC Unit,\n sDescription Description,\n nQuantity Quantity,\n nUnitMeasurementId UnitMeasurementId\n FROM buy.buying_ComparativeTableItem (NOLOCK)\n WHERE nComparativeTableId=@nComparativeTableId\n ORDER BY nComparativeTableItemId,nComparativeTableId;\n \n SELECT nComparativeTableSectionId Id,\n nComparativeTableId ComparativeTableId,\n nProviderId ProviderId,\n nProviderId ProviderName,\n bSuggested Suggested,\n nTotalPrice TotalPrice\n FROM buy.buying_ComparativeTableSection (NOLOCK)\n WHERE nComparativeTableId=@nComparativeTableId\n ORDER BY nComparativeTableSectionId,nComparativeTableId;\n \n SELECT SectionItems.nComparativeTableSectionItemId Id,\n SectionItems.nQuoteItemsId QuoteItemsId,\n SectionItems.nComparativeTableSectionId ComparativeTableSectionId,\n SectionItems.nComparativeTableItemId ComparativeTableItemId,\n SectionItems.nUnitPrice UnitPrice,\n SectionItems.nQuantity Quantity,\n SectionItems.bItemSuggested ItemSuggested\n FROM buy.buying_ComparativeTableSectionItem as SectionItems\n INNER JOIN buy.buying_ComparativeTableSection as Sections\n ON Sections.nComparativeTableSectionId = SectionItems.nComparativeTableSectionId\n where Sections.nComparativeTableId=@nComparativeTableId\n ORDER BY SectionItems.nComparativeTableSectionItemId,SectionItems.nComparativeTableSectionId,SectionItems.nComparativeTableItemId ;\nEND");
}
}
}
迁移完成了它应该做的事情，更新了列中的数据，但是当我执行它时，我得到了这个错误：
Build started...
Build succeeded.
Applying migration '20210810191019_buying_ST014_M'.
Failed executing DbCommand (14ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
UPDATE [buy].[buying_rckStoreProcedureDefinition] SET [tCreateOrUpdate] = CONCAT(N'/*-----------------------------------------------------------------------------------------------------------------*/', NCHAR(10), N'/* Objetivo : Devolver un cuadro comparativo con todo sus listados correspondiente.
*/', NCHAR(10), N'/* Parámetros: @nApprovingEmployeeId: Identificador del empleado inmediato superior. */', NCHAR(10), N'/*-----------------------------------------------------------------------------------------------------------------*/', NCHAR(10), N'CREATE OR ALTER PROCEDURE buy.buying_readComparativeTable @nComparativeTableId INTEGER AS', NCHAR(10), N'BEGIN', NCHAR(10), N' SELECT nComparativeTableId Id,', NCHAR(10), N' nQuoteOrderId QuoteOrderId,', NCHAR(10), N' sNoteSSGG NoteSSGG,', NCHAR(10), N' sSubject Subject,', NCHAR(10), N' nCreatorEmployee CreatorEmployee,', NCHAR(10), N' nResponsibleEmployee ResponsibleEmployee,', NCHAR(10), N' nComparativeTableVisualizationId VisualizationId,', NCHAR(10), N' nBuyingRequestTypeId BuyingRequestTypeId,', NCHAR(10), N' nCustomFormatDocId CustomFormatDocId,', NCHAR(10), N' nCompany CompanyId,', NCHAR(10), N' nRegional RegionalId,', NCHAR(10), N'
nCurrencyId CurrencyId,', NCHAR(10), N' nMonetaryAuthEmployee MonetaryAuthEmployee,', NCHAR(10), N' sMonetaryAuthNote MonetaryAuthNote,', NCHAR(10), N' dCreateDate CreateDate,', NCHAR(10), N' nComparativeTableStatusId StatusId', NCHAR(10), N' FROM buy.buying_ComparativeTable (NOLOCK)', NCHAR(10), N' WHERE nComparativeTableId=@nComparativeTableId', NCHAR(10), N'
ORDER BY nComparativeTableId;', NCHAR(10), NCHAR(10), NCHAR(10), N' SELECT nComparativeTableItemId Id,', NCHAR(10), N'
nComparativeTableId ComparativeTableId,', NCHAR(10), N' nQuoteOrderItemId QuoteOrderItemId,', NCHAR(10), N'
sProductUNSPSC ProductUNSPSC,', NCHAR(10), N' sProductUNSPSC Unit,', NCHAR(10), N' sDescription Description,', NCHAR(10), N' nQuantity Quantity,', NCHAR(10), N' nUnitMeasurementId UnitMeasurementId', NCHAR(10), N'
FROM buy.buying_ComparativeTableItem (NOLOCK)', NCHAR(10), N' WHERE nComparativeTableId=@nComparativeTableId', NCHAR(10), N'
ORDER BY nComparativeTableItemId,nComparativeTableId;', NCHAR(10), N' ', NCHAR(10), N' SELECT nComparativeTableSectionId Id,', NCHAR(10), N' nComparativeTableId ComparativeTableId,', NCHAR(10), N' nQuoteId QuoteId,', NCHAR(10), N' nProviderId ProviderId,', NCHAR(10), N' nProviderId ProviderName,', NCHAR(10), N'
bSuggested Suggested,', NCHAR(10), N' nTotalPrice TotalPrice', NCHAR(10), N' FROM buy.buying_ComparativeTableSection (NOLOCK)', NCHAR(10), N' WHERE nComparativeTableId=@nComparativeTableId', NCHAR(10), N' ORDER BY nComparativeTableSectionId,nComparativeTableId;', NCHAR(10), N' ', NCHAR(10), N' SELECT SectionItems.nComparativeTableSectionItemId Id,', NCHAR(10), N' SectionItems.nQuoteItemsId QuoteItemsId,', NCHAR(10), N' SectionItems.nComparativeTableSectionId ComparativeTableSectionId,', NCHAR(10), N' SectionItems.nComparativeTableItemId ComparativeTableItemId,', NCHAR(10), N' SectionItems.nUnitPrice UnitPrice,', NCHAR(10), N' SectionItems.nQuantity Quantity,', NCHAR(10), N' SectionItems.bItemSuggested ItemSuggested', NCHAR(10), N' FROM buy.buying_ComparativeTableSectionItem as SectionItems', NCHAR(10), N'
INNER JOIN buy.buying_ComparativeTableSection as Sections', NCHAR(10), N' ON Sections.nComparativeTableSectionId = SectionItems.nComparativeTableSectionId', NCHAR(10), N' where Sections.nComparativeTableId=@nComparativeTableId', NCHAR(10), N'
ORDER BY SectionItems.nComparativeTableSectionItemId,SectionItems.nComparativeTableSectionId,SectionItems.nComparativeTableItemId ;', NCHAR(10), N'END')
WHERE [sProcedure] = N'buying_readComparativeTable';
SELECT @@ROWCOUNT;
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 1777
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlInternalConnection.cs:line 606
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) in H:\tsaagent1\_work\21\s\src\Microsof at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\TdsParser.cs:line 2523
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 3043
at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 1483
at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery() in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 1040
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:4627e6ce-75fb-483e-bb06-65c718d27789
Error Number:102,State:1,Class:15
Sintaxis incorrecta cerca de 'nComparativeTab'.
该错误是由 EF 用于更新值的 SQL 语句使用 CONCAT T-SQL 函数引起的，将\n迁移中的每个替换为参数 NCHAR(10)：
...SET [tCreateOrUpdate] = CONCAT(N'/*-----------------------------------------------------------------------------------------------------------------*/', NCHAR(10), N'/* Objetivo : Devolver un cuadro comparativo con todo sus listados correspondiente...
而这种方式有两个问题：
正如 Microsft 在 MSDN CONCAT Entry中解释的那样，所有参数 NVARCHAR <= 4000 的情况的返回值是 NVARCHAR <= 4000，在我的情况下，完整的字符串长度是 4014，因此省略了最后 14 个字符，导致触发器执行带有截断字符串的 sp_executesql
如果我有超过 128 行，它将崩溃导致 CONCAT 的最大参数数为 255，并且每行消耗两个参数，行内容和换行符（NCHAR（10））
那么我该如何解决这个问题呢？改变 EF 处理\n? 我需要一些想法
解决方案
推荐阅读
- bash - 如何在没有“awk”的情况下逐行获取文件信息
- html - 如果其中一些元素具有“display：none”，我如何使元素与“justify-content: space-between”对齐？
- javascript - 从 Gatsby 浏览器 API 触发 Redux 操作？
- python - 如何使用 LaTeX 在 Jupyter Notebook 中水平包含两个图形，准备导出为 PDF？
- java - 在 java 中使用 HashMap 的 getValue 方法时出现 Nullpointer 异常
- r - 用 if 和 else if 条件编写 R 例程
- java - 无法使用 Apache Camel Bindy 向文件添加页眉和页脚
- flutter - Flutter - 如何将值从函数返回到变量
- javascript - 当设备移动时将用户发送到另一个页面
- reactjs - 任何人都可以分享下一个 js 与护照谷歌 oauth2 集成的例子吗？