search

dotnetcore / FreeSql

🦄 .NET aot orm, C# orm, VB.NET orm, Mysql orm, Postgresql orm, SqlServer orm, Oracle orm, Sqlite orm, Firebird orm, 达梦 orm, 人大金仓 orm, 神通 orm, 翰高 orm, 南大通用 orm, 虚谷 orm, 国产 orm, Clickhouse orm, QuestDB orm, MsAccess orm.
http://freesql.net
MIT License
4.08k stars 851 forks source link

使用动态类型Insert从基类AsType 为子类后会向主键列插入数据 问题 #1496

Open hyzx86 opened 1 year ago

hyzx86 commented 1 year ago

最新版 freesql + SQLServer 2016

核心代码如下：

allModelList 是 从json 转换过来然后动态生成的类型 传入数据类型转换没有问题， 过程中为了方便进行条件更新和条件删除，将对象集合转换为基类进行处理了（类型装箱应该不会影响数据内容） 然后在插入时 如果使用 DIndexBase 作为初始类型 会导致主键列在生成SQL时被赋值

                        var insertQuery = _fsql.Insert<DIndexBase>().AsType(type).AppendData(updateList);

如果使用object 则不存在这种问题 var insertQuery = _fsql.Insert().AsType(type).AppendData(updateList);


                var config = _dynamicIndexAppService.GetDynamicIndexConfig(typeName, true);
                if (config != null)
                {
                    var type = _dynamicIndexAppService.GetDynamicIndexType(config);
                    var table = _fsql.CodeFirst.GetTableByEntity(type);
                    var typeFilterdList = contentItems.Where(x => x.ContentItem.ContentType == typeName)
                        .Select(x => x.ContentItem);
                    _logger.LogWarning(
                        "数据导入，更新动态索引DIndex ContentType: {typeName} 动态索引 ,Table:{tableName}, 共：{count}条, 分页大小：{pageSize}",
                        config.TypeName, config.TableName, typeFilterdList.Count(), DefaultPageSize);
                    var allModelList = typeFilterdList.ToModel(config, type, table);
//此处转换为 基类方便操作数据
                    var updateList = allModelList.Take(DefaultPageSize).OfType<DIndexBase>();
                    var pageIndex = 0;
                    totalUpdated[typeName] = 0;
                    try
                    {
                        while (updateList.Any())
                        {
                            var ids = updateList.Select(x => x.DocumentId).Distinct();
                            if (config.IndexLastVersion)
                            {
                                //更新现有文档
                                var deletePreviewVersions = _fsql.Delete<DIndexBase>().AsType(type)
                                     .Where(x => ids.Contains(x.DocumentId));

                                if (_session.CurrentTransaction != null)
                                {
                                    deletePreviewVersions.WithTransaction(_session.CurrentTransaction);
                                }
                                await deletePreviewVersions.ExecuteAffrowsAsync();
                            }
                            else
                            {
                                //更新现有文档
                                var updateCmd = _fsql.Update<DIndexBase>().AsType(type)
                                     .Set(x => new { Latest = false, Published = false })
                                     .Where(x => ids.Contains(x.DocumentId));

                                if (_session.CurrentTransaction != null)
                                {
                                    updateCmd.WithTransaction(_session.CurrentTransaction);
                                }
                                await updateCmd.ExecuteAffrowsAsync();
                            }

                            var insertQuery = _fsql.Insert<object>().AsType(type).AppendData(updateList);
                            if (_session.CurrentTransaction != null)
                            {
                                insertQuery.WithTransaction(_session.CurrentTransaction);
                            }
                            switch (_fsql.Ado.DataType)
                            {
                                case FreeSql.DataType.SqlServer:
                                    _logger.LogWarning("检测到 SqlServer 数据库，已开启SqlBulkCopy");
                                    await insertQuery.ExecuteSqlBulkCopyAsync();
                                    totalUpdated[typeName] += updateList.Count();
                                    break;
                                case FreeSql.DataType.MySql:
                                    _logger.LogWarning("检测到 MySql 数据库，已开启 ExecuteMySqlBulkCopy ");
                                    await insertQuery.ExecuteMySqlBulkCopyAsync();
                                    totalUpdated[typeName] += updateList.Count();
                                    break;
                                case FreeSql.DataType.PostgreSQL:
                                    _logger.LogWarning("检测到 PostgreSQL 数据库，已开启 ExecutePgCopy ");
                                    await insertQuery.ExecutePgCopyAsync();
                                    totalUpdated[typeName] += updateList.Count();
                                    break;
                                default:
                                    totalUpdated[typeName] += await insertQuery.ExecuteAffrowsAsync();
                                    break;
                            }

DIndexBase 类型


public abstract class EocDocumentIndex : MapIndex, IFreeSqlMapDocumentIndex
    {
        [Column(IsPrimary = true, IsIdentity = true, IsNullable = false)]
        new public virtual long Id { get => base.Id; set { base.Id = value; } }

        public virtual long DocumentId { get; set; }

    }

//这里实际就是Table
    [EOCTable(Name = "ContentItemIndex")]
    public class DIndexBase : EocDocumentIndex //
    {
        [Column(IsPrimary = true, IsIdentity = true, CanInsert = false, CanUpdate = false)]
        public override long Id { get => base.Id; set { base.Id = value; } }

        [Column(StringLength = 26)]
        public string ContentItemId { get; set; }
        [Column(StringLength = 26)]
        public string ContentItemVersionId { get; set; }
        public bool Published { get; set; }
        public bool Latest { get; set; }
        [Column(StringLength = 255)]
        public string DisplayText { get; set; }
    }
hyzx86 commented 1 year ago

SQL日志

SET IDENTITY_INSERT [DIndex_CustomerPayer] ON;
MERGE INTO [DIndex_CustomerPayer] t1 
USING (SELECT 50322 as [DocumentId], 0 as [Id], N'4p2eh1ryy8akg50fv31rcp3h5z' as [ContentItemId], N'4q89s47a7rfvrxb8mp5ckqkydz' as [ContentItemVersionId], 1 as [Published], 1 as [Latest], NULL as [DisplayText], N'xxxx区城x号' as [Address], N'上海xxxxxx有限公司' as [Company], 1 as [CreditLimit], xx' as [CreditPeriod], N'xxx' as [DutyParagraph], N'RG' as [Payer], N'xxx' as [PayerCode], N'' as [PayerContactEmail], N'xxx' as [Phone], N'' as [SendStatementOfAccountEmail], N'' as [InvoiceType], N'' as [InvoiceReciverEmail] ) t2 ON (t1.[Id] = t2.[Id]) 
WHEN MATCHED THEN 
  update set [DocumentId] = t2.[DocumentId], [ContentItemId] = t2.[ContentItemId], [ContentItemVersionId] = t2.[ContentItemVersionId], [Published] = t2.[Published], [Latest] = t2.[Latest], [DisplayText] = t2.[DisplayText], [Address] = t2.[Address], [Company] = t2.[Company], [CreditLimit] = t2.[CreditLimit], [CreditPeriod] = t2.[CreditPeriod], [DutyParagraph] = t2.[DutyParagraph], [Payer] = t2.[Payer], [PayerCode] = t2.[PayerCode], [PayerContactEmail] = t2.[PayerContactEmail], [Phone] = t2.[Phone], [SendStatementOfAccountEmail] = t2.[SendStatementOfAccountEmail], [InvoiceType] = t2.[InvoiceType], [InvoiceReciverEmail] = t2.[InvoiceReciverEmail] 
WHEN NOT MATCHED THEN 
  insert ([DocumentId], [ContentItemId], [ContentItemVersionId], [Published], [Latest], [DisplayText], [Address], [Company], [CreditLimit], [CreditPeriod], [DutyParagraph], [Payer], [PayerCode], [PayerContactEmail], [Phone], [SendStatementOfAccountEmail], [InvoiceType], [InvoiceReciverEmail]) 
  values (t2.[DocumentId], t2.[ContentItemId], t2.[ContentItemVersionId], t2.[Published], t2.[Latest], t2.[DisplayText], t2.[Address], t2.[Company], t2.[CreditLimit], t2.[CreditPeriod], t2.[DutyParagraph], t2.[Payer], t2.[PayerCode], t2.[PayerContactEmail], t2.[Phone], t2.[SendStatementOfAccountEmail], t2.[InvoiceType], t2.[InvoiceReciverEmail]);;
SET IDENTITY_INSERT [DIndex_CustomerPayer] OFF;

2023-04-25 00:11:53.6175|SalesPortal|00-35feaab0fce16eb9d77ba496326edd08-ebbea6a88640651e-00||EasyOC.ContentExtensions.AppServices.ContentManagementAppService|ERROR|IContentHandler thrown from EasyOC.DynamicTypeIndex.Handlers.DynamicIndexTableHandler by Exception System.Exception: 当 IDENTITY_INSERT 设置为 ON 或某个复制用户向 NOT FOR REPLICATION 标识列中插入内容时，必须为表 'DIndex_CustomerPayer' 中的标识列指定显式值。
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): 当 IDENTITY_INSERT 设置为 ON 或某个复制用户向 NOT FOR REPLICATION 标识列中插入内容时，必须为表 'DIndex_CustomerPayer' 中的标识列指定显式值。
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, Boolean isInternal, String endMethod)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location ---
   at FreeSql.Internal.CommonProvider.AdoProvider.ExecuteNonQueryAsync(DbConnection connection, DbTransaction transaction, CommandType cmdType, String cmdText, Int32 cmdTimeout, Func`2 cmdAfterHandler, DbParameter[] cmdParms, CancellationToken cancellationToken)
ClientConnectionId:0f118b4d-033f-4af7-a846-09eb055f13fc
Error Number:545,State:1,Class:16
   --- End of inner exception stack trace ---
   at FreeSql.Internal.CommonProvider.AdoProvider.LoggerException(IObjectPool`1 pool, PrepareCommandResult pc, Exception ex, DateTime dt, StringBuilder logtxt, Boolean isThrowException)
   at FreeSql.Internal.CommonProvider.AdoProvider.ExecuteNonQueryAsync(DbConnection connection, DbTransaction transaction, CommandType cmdType, String cmdText, Int32 cmdTimeout, Func`2 cmdAfterHandler, DbParameter[] cmdParms, CancellationToken cancellationToken)
   at FreeSql.Internal.CommonProvider.InsertOrUpdateProvider`1.RawExecuteAffrowsAsync(CancellationToken cancellationToken)
   at FreeSql.Internal.CommonProvider.InsertOrUpdateProvider`1.ExecuteAffrowsAsync(CancellationToken cancellationToken)
   at EasyOC.DynamicTypeIndex.Handlers.DynamicIndexTableHandler.UpdateIndex(ContentContextBase context) in D:\SourceCodes\JZSoft\EasyOC.OrchardCore\EasyOC\src\Modules\EasyOC.DynamicTypeIndex\Handlers\DynamicIndexTableHandler.cs:line 244
   at EasyOC.DynamicTypeIndex.Handlers.DynamicIndexTableHandler.PublishedAsync(PublishContentContext context) in D:\SourceCodes\JZSoft\EasyOC.OrchardCore\EasyOC\src\Modules\EasyOC.DynamicTypeIndex\Handlers\DynamicIndexTableHandler.cs:line 154
   at EasyOC.Core.Overrides.HandleExecutorBase`1.InvokeAsync[TEvents,T1](IEnumerable`1 events, Func`3 dispatch, T1 arg1) in D:\SourceCodes\JZSoft\EasyOC.OrchardCore\EasyOC\src\Core\EasyOC.Core\Overrides\HandleExecutorBase.cs:line 172    at FreeSql.Internal.CommonProvider.AdoProvider.LoggerException(IObjectPool`1 pool, PrepareCommandResult pc, Exception ex, DateTime dt, StringBuilder logtxt, Boolean isThrowException)
   at FreeSql.Internal.CommonProvider.AdoProvider.ExecuteNonQueryAsync(DbConnection connection, DbTransaction transaction, CommandType cmdType, String cmdText, Int32 cmdTimeout, Func`2 cmdAfterHandler, DbParameter[] cmdParms, CancellationToken cancellationToken)
   at FreeSql.Internal.CommonProvider.InsertOrUpdateProvider`1.RawExecuteAffrowsAsync(CancellationToken cancellationToken)
   at FreeSql.Internal.CommonProvider.InsertOrUpdateProvider`1.ExecuteAffrowsAsync(CancellationToken cancellationToken)
   at EasyOC.DynamicTypeIndex.Handlers.DynamicIndexTableHandler.UpdateIndex(ContentContextBase context) in D:\SourceCodes\JZSoft\EasyOC.OrchardCore\EasyOC\src\Modules\EasyOC.DynamicTypeIndex\Handlers\DynamicIndexTableHandler.cs:line 244
   at EasyOC.DynamicTypeIndex.Handlers.DynamicIndexTableHandler.PublishedAsync(PublishContentContext context) in D:\SourceCodes\JZSoft\EasyOC.OrchardCore\EasyOC\src\Modules\EasyOC.DynamicTypeIndex\Handlers\DynamicIndexTableHandler.cs:line 154
   at EasyOC.Core.Overrides.HandleExecutorBase`1.InvokeAsync[TEvents,T1](IEnumerable`1 events, Func`3 dispatch, T1 arg1) in D:\SourceCodes\JZSoft\EasyOC.OrchardCore\EasyOC\src\Core\EasyOC.Core\Overrides\HandleExecutorBase.cs:line 172
2023-04-25 00:11:53.6391|SalesPortal|00-35feaab0fce16eb9d77ba496326edd08-ebbea6a88640651e-00||FreeSql.FreeSqlBuilder|WARN|Executing Log :dbName:ShellDb
DELETE FROM [DIndex_CustomerPayer] WHERE ([ContentItemId] = N'4p2eh1ryy8akg50fv31rcp3h5z')

报错：

hyzx86 commented 1 year ago

InsertOrUpdate也有类似问题

  • © Githubissues.
  • Githubissues is a development platform for aggregating issues.