EF Code First The INSERT statement conflicted with the FOREIGN KEY constraint

Question

When I try to insert a record in the database, I get this error message:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.Order_dbo.AspNetUsers_UserId". The conflict occurred in database "Test", table "dbo.AspNetUsers", column 'Id'. The statement has been terminated.

Disclaimer: I know that this question has been asked before (here, here and here), but none of answers helped me fix this error I get.

Here are the models:

[Table("Order")]
public class Order
{
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity), Key]
    public int Id { get; set; }

    [Required]
    public string UserId { get; set; }      

    public virtual List<OrderDetails> OrderDetails { get; set; }

    [ForeignKey("UserId")]
    public virtual ApplicationUser User { get; set; }
}    

[Table("OrderDetails")]
public class OrderDetails
{
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity), Key]
    public int Id { get; set; }

    [Required]
    public int OrderId { get; set; }

    [ForeignKey("OrderId")]
    public virtual Order Order { get; set; }
}

public class ApplicationUser : IdentityUser
{
    [MaxLength(15)]
    public string FirstName { get; set; }

    [MaxLength(15)]
    public string LastName { get; set; }

    [MaxLength(50)]
    public string EmailAddress { get; set; }

    public virtual List<Order> Orders { get; set; }
}

Code that inserts the new Order:

public OrderDetails Add(dynamic addOrderDetails, string userId)
{
    if (addOrderDetails.OrderId == null)
    {
        var order = new Order()
        {
            UserId = userId,
            CreateDate = DateTime.Now,
            Status = OStatus.InProgress,
            Confirmed = (DateTime?)null,
        };

        var newOrder = _dbContext.Orders.Add(order);
        _dbContext.Entry(order).State = EntityState.Added;
        _dbContext.SaveChanges(); // this is where the error msg is being thrown.

        var orderDetails = new OrderDetails()
        {
            OrderId = newOrder.Id,
            ServiceId = addOrderDetails.ServiceId,
            EventStart = start,
            EventEnd = end
        };

        var newOrderDetails = _dbContext.OrderDetails.Add(orderDetails);
        _dbContext.Entry(orderDetails).State = EntityState.Added;
        _dbContext.SaveChanges();
    }

    return null;
}

The error is being thrown at this line: _dbContext.SaveChanges(); // this is where the error msg is being thrown.

When debugging I can see that UserId = userId, has a value.

So why am I getting this error message?

Answer 1

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.Order_dbo.AspNetUsers_UserId". The conflict occurred in database "Test", table "dbo.AspNetUsers", column 'Id'. The statement has been terminated.

This means that the individual order (row) at Order_dbo CANNOT be created without a reference to an existing user at AspNetUsers.

This error is coming from the actual DB, that's why you're getting it when you reach _dbContext.SaveChanges(), and not before.

Possible solution

Make the column AspNetUsers_UserId nullable.

[Required]
public int? UserId { get; set; } 

That way you´ll be able to create orders without having a previously set user.

I suggest you to use int instead of string type for you Id.

Answer 2

The AspNetUsers table in the database must contain a record whose UserId column has the value you set with userId in the new order. Order.UserId is not only required but also a foreign key to the AspNetUsers table (see the [ForeignKey("UserId")] attribute in Order). So, it's not enough that Order.UserId has any value (!= null), it must have a value that corresponds to a record in the AspNetUsers table.

The exception means that such a record is not present in the AspNetUsers table.