向数据库添加新行时，Entity Framework 6 DbUpdateConcurrencyException [英] Entity Framework 6 DbUpdateConcurrencyException When Adding New Rows To Database

问题描述

这是我的问题:每当我尝试向数据库中添加一行时，都会出现以下异常: DbUpdateConcurrencyException

Here's my problem: Every time I attempt to add a row to the database, I get this exception: DbUpdateConcurrencyException

除我以外，没有其他人使用此开发数据库.请帮忙.我的智慧到此为止.

No one else uses this development database except me. Please help. I'm at my wits end here.

代码如下:

using (var context = new MyDbContext(connectionString))
{
    context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

    var newProduct = new ProductsEntity { ProductTypeId = 1 };
    context.Products.Add(newProduct);

    // The line below always throws the exception
    context.SaveChanges();
}

上下文 MyDbContext 定义如下:

public class MyDbContext : DbContext
{
    public MyDbContext(string connectionString) : base(connectionString)
    {
        System.Data.Entity.Database.SetInitializer<MyDbContext>(null);
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new ProductsMap());
    }

    public virtual DbSet<ProductsEntity> Products { get; set; }
}

ProductsMap 和 ProductEntity 的定义如下:

public class ProductsMap : EntityTypeConfiguration<ProductsEntity>
{
    HasKey(t => t.ProductId);

    ToTable("Products");

    Property(t => t.ProductId)
        .HasColumnName("ProductId")
        .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

    Property(t => t.ProductTypeId)
        .HasColumnName("ProductTypeId");

    // Stored Procedure Mapping
    MapToStoredProcedures(s =>
        s.Insert((i => i.HasName("InsertProduct")
            .Parameter(p => p.ProductTypeId, "ProductTypeId")
            .Result(r => r.ProductId, "ProductId")
            )));
}

public class ProductsEntity
{
    public int ProductId { get; set; }
    public int ProductTypeId { get; set; }
}

存储过程 InsertProduct 在这里:

CREATE PROCEDURE [dbo].[InsertProduct]    

@ProductTypeId int,  
@Identity int = NULL OUTPUT  

AS  
BEGIN    

INSERT INTO [dbo].[Products]  
    ( [ProductTypeId] ) 
    VALUES 
    ( @ProductTypeId  )    

END    

SET @Identity = SCOPE_IDENTITY()  

RETURN   

并且，表产品的定义如下:

CREATE TABLE dbo.Products
(
    ProductId int IDENTITY(1,1) NOT NULL,
    ProductTypeId int NOT NULL,
PRIMARY KEY CLUSTERED
(
    ProductId ASC
) WITH (
    PAD_INDEX  = OFF, 
    STATISTICS_NORECOMPUTE  = OFF, 
    IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS  = ON, 
    ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) 
ON [PRIMARY]

GO

ALTER TABLE dbo.Products WITH CHECK ADD FOREIGN KEY(ProductTypeId)
REFERENCES dbo.ProductTypes (ProductTypeId)

GO

如果有帮助，似乎正在发生的事情是 Products 表的标识种子实际上在每次代码到达 SaveChanges()时都会增加下面的行.我可以说，因为当我在SQL Server Studio中测试运行 InsertProduct 时， ProductId 列会为我每次尝试运行此代码所做的尝试跳过一个整数.

What appears to be happening, if it helps, is that the identity seed of the Products table is in fact getting incremented every time the code hits the SaveChanges() line below. I can tell, because when I test running InsertProduct in SQL Server Studio, the ProductId column skips one integer for every attempt I make at running this code.

我最好的猜测是，Entity Framework执行 InsertProduct 过程，但是以某种方式阻止了事务并使其回滚.老实说，我不知道.

My best guess is that Entity Framework executes the InsertProduct procedure, but somehow the transaction is blocked and gets rolled back. I have no idea though, honestly.

这是控制台的Debug输出内容:

Here's the console's Debug output contents:

Opened connection at 10/25/2016 5:53:43 PM -07:00

Started transaction at 10/25/2016 5:53:43 PM -07:00

[dbo].[InsertProduct]


-- ProductTypeId: '1' (Type = Int32, IsNullable = false)

-- Executing at 10/25/2016 5:53:44 PM -07:00

Application Insights Telemetry (unconfigured): {"name":"Microsoft.ApplicationInsights.Dev.RemoteDependency","time":"2016-10-26T00:53:44.0238952Z","tags":{"ai.operation.parentId":"CTWlUsYMEbU=","ai.device.roleInstance":"MyComputerName.Domain","ai.operation.name":"POST deal","ai.operation.id":"CTWlUsYMEbU=","ai.internal.sdkVersion":"rddf: 2.1.0.363"},"data":{"baseType":"RemoteDependencyData","baseData":{"ver":2,"name":"SERVERNAME| DevDatabase | [dbo].[InsertProduct]","id":"JDD838YyJkc=","value":16.0379,"dependencyKind":0,"success":true,"properties":{"DeveloperMode":"true"}}}}
-- Completed in 28 ms with result: SqlDataReader



Closed connection at 10/25/2016 5:53:44 PM -07:00

Exception thrown: 'System.Data.Entity.Infrastructure.DbUpdateConcurrencyException' in EntityFramework.dll

以下是内部异常的文本:

Here's the text from the inner exception:

存储更新，插入或删除语句影响了意外的行数(0).自加载实体以来，实体可能已被修改或删除."

"Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded."

是的，没有插入任何行.再说一次，没有人使用这个开发数据库.我是唯一拥有dbo权利的人.

And, it's true, no rows are inserted. And again, no one else uses this development database; I'm the only one with dbo rights.

找到了解决方案

FOUND A SOLUTION

好的，非常感谢下面的@Juan向我指出了正确的方向.问题的确是EF需要某种方式来知道主键发生了什么，但这非常微妙.

Ok, big thanks to @Juan below for pointing me in the right direction. The issue was indeed that EF needed some way of knowing what happened to the primary key, but it was pretty subtle.

我做的第一件事就是尝试像这样更改上面的存储过程:

The first thing I did was try changing the stored procedure above like this:

CREATE PROCEDURE [dbo].[InsertProduct]    

@ProductTypeId int,  
@Identity int = NULL OUTPUT  

AS  
BEGIN    

INSERT INTO [dbo].[Products]  
    ( [ProductTypeId] ) 
    VALUES 
    ( @ProductTypeId  )    

END    

SET @Identity = SCOPE_IDENTITY()  
SELECT SCOPE_IDENTITY() // <-- Added this line

RETURN   

然后，发生的事情是引发的异常更改为 DbUpdateException (来自 DbUpdateConcurrencyException ).此外，内部异常文本现在如下所示:

Then, what happened was that the exception thrown changed to DbUpdateException (from DbUpdateConcurrencyException). Additionally, the inner exception text now read as follows:

函数映射指定结果集不包含的结果列'ProductId'.

A function mapping specifies a result column 'ProductId' that the result set does not contain.

好吧，这提示我正在进步.因此，我尝试做的下一件事是更改 ProductsMap 中的Fluent API，如下所示:

Well, this tipped me off that I was making progress. So the next thing I tried was altering the Fluent API in ProductsMap as below:

public class ProductsMap : EntityTypeConfiguration<ProductsEntity>
{
    HasKey(t => t.ProductId);

    ToTable("Products");

    Property(t => t.ProductId)
        .HasColumnName("ProductId")
        .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

    Property(t => t.ProductTypeId)
        .HasColumnName("ProductTypeId");

    // Stored Procedure Mapping
    // ------------------------
    // Changed the "ProductId" below to "Identity", since that is
    // the name of the output variable in the stored procedure
    MapToStoredProcedures(s =>
        s.Insert((i => i.HasName("InsertProduct")
            .Parameter(p => p.ProductTypeId, "ProductTypeId")
            .Result(r => r.ProductId, "Identity")
            )));
}

上述更改引发了相同的 DbUpdateException ，但内部异常文本更改为:

This change above threw the same DbUpdateException, but the inner exception text changed to this:

函数映射指定结果集不包含的结果列"Identity".

A function mapping specifies a result column 'Identity' that the result set does not contain.

这时，我注意到Fluent API中.Result()方法的工具提示如下(强调由我添加):

At this point I noticed that the tool-tip for the .Result() method in Fluent API reads as follows (emphasis added by me):

为此存储过程配置结果列以映射到属性. 这用于数据库生成的列.

所以，这让我想到Fluent API中的.Result()方法可能根本不适合SQL存储过程输出.我试图查看EF6上的文档以了解该方法的工作原理，但找不到任何有用的方法.而且，通过使用SQL存储过程的输出，我无法找到任何的例子.

So, that got me thinking that the .Result() method in Fluent API probably isn't for SQL stored procedure output at all. I tried to look up the documentation on EF6 for how this method is supposed to work, but I couldn't find anything useful. And, I couldn't find any examples of it using a SQL stored procedure output, which is telling.

因此，这引起了我的注意，这是我唯一的数据库生成的列，即:

So instead, that got my attention on my only database generated column, which is this one:

    Property(t => t.ProductId)
        .HasColumnName("ProductId")
        .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

考虑到这一点，我对上面的存储过程进行了如下更改:

With this in mind I changed my stored procedure above as follows:

CREATE PROCEDURE [dbo].[InsertProduct]    

@ProductTypeId int,  
@Identity int = NULL OUTPUT  

AS  
BEGIN    

INSERT INTO [dbo].[Products]  
    ( [ProductTypeId] ) 
    VALUES 
    ( @ProductTypeId  )    

END    

SET @Identity = SCOPE_IDENTITY()  
// Altered the line below a little bit
SELECT ProductId FROM Products WHERE ProductId = SCOPE_IDENTITY()

RETURN   

再次，我得到了这个结果 DbUpdateException ，:

And, again, I got this resulting DbUpdateException, :

函数映射指定结果集不包含的结果列"Identity".

A function mapping specifies a result column 'Identity' that the result set does not contain.

因此，我想到了，我应该将Fluent API中的.Result()方法更改回以前的状态，也许一切都会解决:

So, then I figured, I should just change the .Result() method in Fluent API back to what I had before, and perhaps everything would work out:

    // Stored Procedure Mapping
    // ------------------------
    // Changed the "Identity" back to "ProductId"
    MapToStoredProcedures(s =>
        s.Insert((i => i.HasName("InsertProduct")
            .Parameter(p => p.ProductTypeId, "ProductTypeId")
            .Result(r => r.ProductId, "ProductId")
            )));

你猜怎么着?可行！

出于良好的考虑，我还尝试完全删除.Result()规范，如下所示:

For good measure, I also tried removing the .Result() specification entirely, like this:

    // Stored Procedure Mapping
    // ------------------------
    // No more .Result() specification
    MapToStoredProcedures(s =>
        s.Insert((i => i.HasName("InsertProduct")
            .Parameter(p => p.ProductTypeId, "ProductTypeId")
            )));

而且，有趣的是，这也很好！

And, interestingly, this also worked just fine as well!

因此，最终，我的结论是，将列指定为Fluent API中生成的数据库可能与 MapToStoredProcedures()下的.Result()方法的规范重复.我在EF6文档中找不到任何可以更好地解释这一点的东西，但至少现在我可以解决问题了.

So, ultimately, my conclusion is that specifying a column as database generated in Fluent API is probably redundant with the specification of the .Result() method under MapToStoredProcedures(). I couldn't find anything in the EF6 documentation to better explain this, but at least now I can solve the problem.

再次感谢@Juan，这是个很有帮助的大手指，向我指出了正确的方向！ :)

Thanks again to @Juan for the big helpful finger pointing me in the right direction! : )

推荐答案

我想我可能知道问题所在.

I think I may know what the problem is.

EF没有检测到插入物.我认为这是因为您有一个名为@Identity的OUTPUT参数，但我看不到它被映射回了任何地方:

The EF is not detecting the insert. I think this is because you have an OUTPUT parameter called @Identity but I don't see that being mapped back anywhere:

// Stored Procedure Mapping
MapToStoredProcedures(s =>
    s.Insert((i => i.HasName("InsertProduct")
        .Parameter(p => p.ProductTypeId, "ProductTypeId")
        .Result(r => r.ProductId, "ProductId")
        )));

要么将@Identity参数映射到ProductId字段，要么让SP返回标识以让EF知道插入成功:

Either you map the @Identity parameter to the ProductId field or you have your SP return the identity to let the EF know the insert succeeded:

SELECT SCOPE_IDENTITY();

这篇关于向数据库添加新行时，Entity Framework 6 DbUpdateConcurrencyException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！