EF Core Cannot insert explicit value for identity column in table

Question

I've seen this question, this and this, however the following error is not gone:

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

What I've tried is:

[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)] 
public int Id { get; set; }

Moreover, I've tried to set to None:

[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int Id { get; set; }

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

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

public async Task Add(Order order)
{
    order.Customer = _context.Customers.Find(order.Customer.Id);    
    await _context.AddAsync(order);
    await _context.SaveChangesAsync();
}

and OrderService class:

    public async Task<OrderDto> Add(OrderDto orderDto)
    {
        var order = _mapper.Map<Order>(orderDto);
        await _orderRepository.Add(order);        
        orderDto.Id = order.Id;
        return orderDto;

    }

Please, tell me what I am doing wrong? Please, do not close my question as it is not duplicate.

The error is for the Products table, but the code you've shown is for the Orders and OrderItems tables. Can you show the code for Products? — devNull
Looks like it's an error in itself that Products get inserted at all. — Gert Arnold
Not being an expert with EF, I will comment instead of answer, but I believe AddAsync adds the entity to the context in the Added state so it's trying to insert it. It already has an id because you just did a Find of it (presumably). I think you can just remove the second line of code in the Add() method. — Crowcoder

Learner Learner · Accepted Answer · 2020-04-17T20:39:38

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

The whole code looks like this.

Service Layer:

public async Task<OrderDto> Add(OrderDto orderDto)
{
    var order = _mapper.Map<Order>(orderDto);
    await _orderRepository.Add(order);        
    orderDto.Id = order.Id;
    return orderDto;
}

Repository Layer:

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

EF Core Cannot insert explicit value for identity column in table

2 Answers