locked
Entity Framework Core generates incorrect SQL code which results to Cannot insert explicit value for identity column in table 'AspNetUsers' when IDENTITY_INSERT is set to OFF. RRS feed

  • Question

  • What I want is to save Order and OrderItems. The code of saving looks like this:

    public async Task Add(Order order)
    {  
        await _context.Orders.AddAsync(order);
        await _context.SaveChangesAsync();
    }

    The error says:

    Cannot insert explicit value for identity column in table 'AspNetUsers' when IDENTITY_INSERT is set to OFF.

    T-SQL code looks like this:

    CREATE TABLE Orders
    (
        Id INT IDENTITY(1,1),
        DatePlaced datetime NOT NULL,   
        CONSTRAINT PK_Order_Id PRIMARY KEY (Id)
    )
    
    CREATE TABLE OrderItems
    (
        Id INT IDENTITY(1,1),
        IdOrder INT NOT NULL
            CONSTRAINT FK_OrderItems_IdOrder__Orders_Id FOREIGN KEY(IdOrder) REFERENCES Orders(Id),
        IdProduct INT NOT NULL
            CONSTRAINT FK_OrderItems_IdProduct__Products_Id FOREIGN KEY(IdProduct) REFERENCES Products(Id),
        Quantity INT NOT NULL,
        TotalPrice decimal (18,2),  
        CONSTRAINT PK_OrderItem_Id PRIMARY KEY (Id)
    )
    
    CREATE TABLE Products
    (
        Id INT IDENTITY(1,1),
        Name varchar(100), 
        CONSTRAINT PK_Product_Id PRIMARY KEY (Id)
    )

    And model classes look like this:

    public partial class Order
    {
        public Order()
        {
            OrderItems = new HashSet<OrderItem>();
        }
    
        public int Id { get; set; }
        public DateTime DatePlaced { get; set; }
        public virtual ICollection<OrderItem> OrderItems { get; set; }
    }
    
    public partial class OrderItem
    {
        public int Id { get; set; }
        public int Quantity { get; set; }
        [Column(TypeName = "decimal(18,2)")]
        public decimal? TotalPrice { get; set; }
        public virtual Order Order { get; set; }
        public virtual Product Product { get; set; }
    }
    
    public partial class Product
    {
        public Product() { }
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        public string Name { get; set; }
    }

    and Customer model

    public class Customer : IdentityUser
    {        
        public string FirstName { get; set; }
    
        public string LastName { get; set; }
        public virtual ICollection<Order> Orders { get; set; }
    }


    I've seen the generated incorrect SQL code:

    exec sp_executesql N'SET NOCOUNT ON;
    INSERT INTO [AspNetUsers] ([Id], [AccessFailedCount], [ConcurrencyStamp], [Email], [EmailConfirmed], [FacebookId], [FirstName], [Gender], [LastName], [Locale], 
    [Location], [LockoutEnabled], [LockoutEnd], [NormalizedEmail], [NormalizedUserName], [PasswordHash], [PhoneNumber], [PhoneNumberConfirmed], 
    [PictureUrl], [SecurityStamp], [TwoFactorEnabled], [UserName])
    VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21);
    ',N'@p0 nvarchar(450),@p1 int,@p2 nvarchar(4000),@p3 nvarchar(256),@p4 bit,@p5 bigint,@p6 nvarchar(100),@p7 nvarchar(4000),
    @p8 nvarchar(100),@p9 nvarchar(4000),@p10 nvarchar(4000),@p11 bit,@p12 datetimeoffset(7),@p13 nvarchar(256),@p14 nvarchar(256),@p15 nvarchar(4000),
    @p16 nvarchar(4000),@p17 bit,@p18 nvarchar(4000),@p19 nvarchar(4000),@p20 bit,@p21 nvarchar(256)'
    ,@p0=N'867ea8ff-b8f9-4190-b15e-87da4d787700',@p1=0,@p2=N'ab319baf-fa74-429f-82e1-05f9a1b72f45',@p3='test',@p4='test',@p5='test',@p6='test',@p7='test',@p8='test',@p9=NULL,@p10=NULL,@p11=0,@p12=NULL,@p13=NULL,@p14=NULL,@p15=NULL,@p16=NULL,@p17=0,@p18=NULL,@p19=NULL,@p20=0,@p21=NULL
    go


    Please, tell me how I can correctly insert Order and OrderItems

    The desired generated SQL code by Entity Framework Core looks like this:

    exec sp_executesql N'SET NOCOUNT ON;
    INSERT INTO [Orders] ([CustomerId], [DatePlaced])
    VALUES (@p0, @p1);
    SELECT [Id]
    FROM [Orders]
    WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();
    
    ',N'@p0 nvarchar(450),@p1 datetime2(7)',@p0=N'867ea8ff-b8f9-4190-b15e-87da4d787700',@p1='2020-04-17 16:08:08.9290000'
    
    
    exec sp_executesql N'SET NOCOUNT ON;
    INSERT INTO [OrderItems] ([OrderId], [ProductId], [Quantity], [TotalPrice])
    VALUES (@p2, @p3, @p4, @p5);
    SELECT [Id]
    FROM [OrderItems]
    WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();
    
    ',N'@p2 int,@p3 int,@p4 int,@p5 nvarchar(4000)',@p2=56,@p3=1,@p4=1,@p5=NULL



    • Edited by NiceStepUp Friday, April 17, 2020 9:01 PM
    Friday, April 17, 2020 8:58 PM

Answers

  • The reason of this behaviour is that Automapper creates a new instance and Entity Framework thinks that POCO's are all new entities which should be inserted.

    So the solution is to Attach existing entities to `DbContext. It is described in great tutorial here.

    So the solution looks like this:

    public async Task Add(Order order)
    {            
        _context.Attach(order.Customer);
        foreach (var orderItem in order.OrderItems)
        {
            _context.Attach(orderItem.Product);
        }            
        await _context.Orders.AddAsync(order);
    }
    • Marked as answer by NiceStepUp Sunday, April 19, 2020 1:30 PM
    Sunday, April 19, 2020 1:30 PM

All replies

  • Why is AspNetUsers from the Identity database even involved with an Order in the manner you are trying to use it? At best, you should have gotten the user identity that was authorized becuase the user logged into the application at the UI and just passed it to the Order functionally to be persisted to the database for a Order? There is no need for you to be trying to make any relationship table wise between AspNetUsers and Order.

    What you have done here is very questionable if some how a new user is being added to the Identity database becuase an Order is being created.  

     
    Saturday, April 18, 2020 1:45 PM
  • The reason of this behaviour is that Automapper creates a new instance and Entity Framework thinks that POCO's are all new entities which should be inserted.

    So the solution is to Attach existing entities to `DbContext. It is described in great tutorial here.

    So the solution looks like this:

    public async Task Add(Order order)
    {            
        _context.Attach(order.Customer);
        foreach (var orderItem in order.OrderItems)
        {
            _context.Attach(orderItem.Product);
        }            
        await _context.Orders.AddAsync(order);
    }
    • Marked as answer by NiceStepUp Sunday, April 19, 2020 1:30 PM
    Sunday, April 19, 2020 1:30 PM