dotnetcore / dotnetcore/FreeSql

同步实体类型到数据库时存在问题 fsql.CodeFirst.SyncStructure<Topic>();

Open
#2,225 1 comment 0 reactions 0 assignees View on GitHub
Dominant language
C#
Stars
4.4k
Forks
910
PR merge metrics
No merged PRs in 30d

Description

#### 问题描述及重现代码:
以SQL Server数据库为例,系统参数 XACT_ABORT 默认值为 OFF。
同步实体到数据库时,会出现:
当插入数据出错时(如存在主键重复数据等),仍然继续执行后面的SQL代码(删除原表,重命名临时表),最终导致数据库表中数据全部丢失。
详见下面SQL代码。

解决方案:生成实体同步SQL时,建议在数据库事务前,执行SET XACT_ABORT ON。

```c#
// c# code
// 直接执行,会出现问题
fsql.CodeFirst.SyncStructure();

// 如果在C#层面对实体同步代码添加事务也可防止上面的问题出现导致数据丢失
fsql.Transaction(() =>
{
fsql.CodeFirst.SyncStructure(...);
});
```

```实体同步生成的SQL代码
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET XACT_ABORT ON -- 建议生成SQL时增加
COMMIT
BEGIN TRANSACTION;

CREATE TABLE [website].[dbo].[FreeSqlTmp_t_counter] (
[id] VARCHAR(50) primary key,
[can_post] INT,
[can_receive] INT,
[counter] INT,
[journal_id] VARCHAR(50),
[journal_id2] VARCHAR(50)
);
ALTER TABLE [website].[dbo].[FreeSqlTmp_t_counter] SET (LOCK_ESCALATION = TABLE);
IF EXISTS(SELECT 1 FROM [website].[dbo].[t_counter])
EXEC('INSERT INTO [website].[dbo].[FreeSqlTmp_t_counter] ([id], [can_post], [can_receive], [counter], [journal_id], [journal_id2])
SELECT [id], [can_post], [can_receive], [counter], isnull([journal_id],''''), NULL FROM [website].[dbo].[t_counter] WITH (HOLDLOCK TABLOCKX)');
DROP TABLE [website].[dbo].[t_counter];
EXECUTE sp_rename N'[website].[dbo].[FreeSqlTmp_t_counter]', N't_counter', 'OBJECT';
COMMIT;
```

#### 数据库版本
Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) Apr 29 2016 23:23:58 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows 10 Pro 6.3 (Build 19045: )

#### 安装的Nuget包
FreeSql.dll 3.5.307

#### .net framework/. net core? 及具体版本
.net 10.0

Contributor guide

No contributing guide indexed for this repository

Research direction

Start at the SQL Server path used by CodeFirst.SyncStructure and reproduce the duplicate-key failure with the SQL shown in the issue. Check the generated transaction SQL and verify that XACT_ABORT is enabled before the migration work begins. Done means a failed insert does not continue to drop or rename the existing table.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, sql
Domain
database
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Quiet
Clarity
Clearly specified
Newbie friendliness
52/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.