DbSet.AddAsync is setting an id value on an identity column causing an exception

Question

I have this a class Order, it has relation with other 2 classes but the issue is with the Id here

public class Order 
{
    public int Id { get; set; }
    public bool IsDeleted { get; set; }
    public string CreatedBy { get; set; }
    public string UpdatedBy { get; set; }
    public DateTime? CreatedOn { get; set; }
    public DateTime? UpdatedOn { get; set; }
    public string Symbol { get; set; }
    //public string SymbolName { get; set; }
    public int Quantity { get; set; }
    public decimal Price { get; set; }
    public int StatusId { get; set; }
    public OrderStatus Status { get; set; }
    public int TradingActionId { get; set; }
    public TradingAction TradingAction { get; set; }
    public string Notes { get; set; }

}

which I map on this way:

public class OrderMap : IEntityTypeConfiguration<Order>
{
    public override void Configure(EntityTypeBuilder<Order> builder)
    {
        builder.ToTable("Order");

        builder.HasKey(p => p.Id);
        builder.Property(dt => dt.Id).UseIdentityColumn();

        builder.Property(x => x.CreatedBy).HasMaxLength(50).IsRequired();
        builder.Property(x => x.CreatedOn).IsRequired();
        builder.Property(x => x.UpdatedBy).HasMaxLength(50).IsRequired();
        builder.Property(x => x.UpdatedOn).IsRequired();

        builder.HasQueryFilter(app => !app.IsDeleted);
        builder.Property(je => je.Symbol).HasMaxLength(10).IsRequired();
        //builder.Property(je => je.SymbolName).HasMaxLength(300).IsRequired();
        builder.Property(je => je.Quantity).IsRequired();
        builder.Property(je => je.Price).IsRequired();
        builder.Property(je => je.Notes).HasMaxLength(300);

        builder.HasOne<OrderStatus>(order => order.Status)
            .WithMany(os => os.Orders)
            .HasForeignKey(order => order.Id)
            .OnDelete(DeleteBehavior.Restrict)
            .IsRequired(false); ;

        builder.HasOne<TradingAction>(order => order.TradingAction)
            .WithMany(ta => ta.Orders)
            .HasForeignKey(orders => orders.Id)
            .OnDelete(DeleteBehavior.Restrict)
            .IsRequired(false); ;
    }
}

on my repository, I am doing this approach to save, as you can see the properties TradingAction and Status are not filled by me but their Ids are passed to the order to be created.

  var entity = new Order
  {
   TradingActionId = 1,
   StatusId = 2,
   Notes = source.Notes,
   Price = source.Price,
   Symbol = source.Symbol,
   Quantity = source.Quantity,
   CreatedOn = dateTimeNow,
   UpdatedOn = dateTimeNow,
   UpdatedBy = "test",
   CreatedBy = "test"
};
_context.Set<TEntity>().AddAsync(entity);

In this context _context.Set<TEntity>().AddAsync(entity); throws an exception saying:

Cannot insert explicit value for identity column when IDENTITY_INSERT is set to OFF.

Running a SqlProfiler I get this query:

exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [Order] ([Id], [CreatedBy], [CreatedOn], [IsDeleted], [Notes], [Price], [Quantity], [StatusId], [Symbol], [TradingActionId], [UpdatedBy], [UpdatedOn])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11);
',N'@p0 int,@p1 nvarchar(50),@p2 datetime2(7),@p3 bit,@p4 nvarchar(300),@p5 decimal(3,1),@p6 int,@p7 int,@p8 nvarchar(10),@p9 int,@p10 nvarchar(50),@p11 datetime2(7)',@p0=-2147482647,@p1=N'test',@p2='2021-02-26 09:10:21.8811175',@p3=0,@p4=N'test',@p5=20.2,@p6=1000,@p7=1,@p8=N'AAL',@p9=2,@p10=N'test',@p11='2021-02-26 09:10:21.8811175'

which tells me that @p0=-2147482647 when it should be identity autogenerated.

If I change on my mappings this line to this it works and sets the property to identity:

builder.Property(dt => dt.Id).ValueGeneratedOnAdd();

which is the issue here that doesn't allow me to use .UseIdentityColumn() method.

Answer 1

First off, the PK fluent configuration

builder.HasKey(p => p.Id);
builder.Property(dt => dt.Id).UseIdentityColumn();

is redundant, since both are true by convention. So the problem should be somewhere else.

And indeed it is in the improper many-to-one FK mapping here (also both redundant)

builder.HasOne<OrderStatus>(order => order.Status)
    .WithMany(os => os.Orders)
    .HasForeignKey(order => order.Id) // <--
    .OnDelete(DeleteBehavior.Restrict)
    .IsRequired(false);

builder.HasOne<TradingAction>(order => order.TradingAction)
    .WithMany(ta => ta.Orders)
    .HasForeignKey(orders => orders.Id) // <--
    .OnDelete(DeleteBehavior.Restrict)
    .IsRequired(false);

Why? Because many-to-one FKs are never auto-generated, they "point" to existing keys in another table, so these misconfiguration calls in fact negate the effect of the previous PK configuration.

To fix the issue (because it also would cause incorrect querying), simply remove them or use respectively

.HasForeignKey(order => order.StatusId)

and

.HasForeignKey(orders => orders.TradingActionId)

---

Also note that ÌsRequired(false) has no effect on non nullable type FKs like int in yours - they still will be treated as required because the non nullable field cannot have null value. Hence remove them as well, and if you really want optional FKs, change the type of FK properties to the corresponding nullable type (in this case int? (Nullable<int>)).