Entity Framework 6 DbUpdateConcurrencyException When Adding New Rows To Database

Question

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.

Here's the code:

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();
}

The context, MyDbContext is defined as follows:

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; }
}

The ProductsMap and ProductEntity are defined as follows:

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; }
}

The stored procedure, InsertProduct is here:

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   

And, the table Products is defined as below:

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

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.

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.

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:

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

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.

EDIT: FOUND A SOLUTION

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   

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

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

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")
            )));
}

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

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

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

Configures a column of the result for this stored procedure to map to a property. This is used for database generated columns.

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   

And, again, I got this resulting DbUpdateException, :

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

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")
            )));

And guess what? That worked!

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!

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.

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

Answer 1

I think I may know what the problem is.

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")
        )));

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();