EF Core Cannot insert explicit value for identity column in table

learner Published at Dev

Learner

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.

Learner

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

Collected from the Internet

Please contact [email protected] to delete if infringement.