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

Question

This is my trial project using breeze/angular/EF. I don't understand why I get this error because I thought I had the same structure working before.

public class TshirtOrder
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<OrderItem> OrderItems { get; set; }
}

public class OrderItem
{
    public int Id { get; set; }
    [ForeignKey("Type")]
    public int TshirtTypeId { get; set; }
    public virtual TshirtType Type { get; set; }
    [ForeignKey("Size")]
    public int TshirtSizeId { get; set; }
    public virtual TshirtSize Size { get; set; }
    public double UnitPrice { get; set; }
    public int Quantity { get; set; }

    [ForeignKey("TshirtOrder")]
    public int TshirtOrderId { get; set; }
    public TshirtOrder TshirtOrder { get; set; }
}

The table definition looks like this:

CREATE TABLE [dbo].[TshirtOrder] (
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [Name]       NVARCHAR (MAX) NULL,
    CONSTRAINT [PK_dbo.TshirtOrder] PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE TABLE [dbo].[OrderItem] (
    [Id]            INT        IDENTITY (1, 1) NOT NULL,
    [TshirtTypeId]  INT        NOT NULL,
    [TshirtSizeId]  INT        NOT NULL,
    [UnitPrice]     FLOAT (53) NOT NULL,
    [Quantity]      INT        NOT NULL,
    [TshirtOrderId] INT        NOT NULL,
    CONSTRAINT [PK_dbo.OrderItem] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_dbo.OrderItem_dbo.TshirtType_TshirtTypeId] FOREIGN KEY ([TshirtTypeId]) REFERENCES [dbo].[TshirtType] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_dbo.OrderItem_dbo.TshirtSize_TshirtSizeId] FOREIGN KEY ([TshirtSizeId]) REFERENCES [dbo].[TshirtSize] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_dbo.OrderItem_dbo.TshirtOrder_TshirtOrderId] FOREIGN KEY ([TshirtOrderId]) REFERENCES [dbo].[TshirtOrder] ([Id]) ON DELETE CASCADE
);

This is how it gets saved in Breeze datacontext:

function _createTshirtOrder() {
    var order = manager.createEntity("TshirtOrder");

    order.orderItems.push(createOrderItem(lookups.tshirtTypes[0], lookups.tshirtSizes[0], 10));
    common.saveEntity(order);
    return order;

    function createOrderItem(type, size, unitPrice) {
        var item = manager.createEntity("OrderItem");
        item.type = type;
        item.size = size;
        item.unitPrice = unitPrice;
        item.quantity = 0;
        return item;
    }
}

Here is the exact error:

{"The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_dbo.OrderItem_dbo.TshirtOrder_TshirtOrderId\". The conflict occurred in database \"dbbb\", table \"dbo.TshirtOrder\", column 'Id'.\r\nThe statement has been terminated."}

So, where is the problem?

Dele

It's as if pushing an item in an order doesn't create a proper relationship between them, have you tried explicitly setting item.TshortOrder = order ? I'm not sure about how you entities are tracked in Breeze context. – Biathlon

Reda, thanks for your comment which works as well, but Jay's answer gives me a better solution. – Dele

Answer 1

I don't know what your "saveEntity" method looks like but I'm guessing it calls

entityManager.saveChanges([order]);

If so, then the problem is that you are only saving the order and NOT the orderItem as well, because you told it to only save the one order. Breeze tracks any changes to the entityManager so a better solution is usually to just let Breeze figure it out for you. i.e.

entityManager.saveChanges();  or entityManager.saveChanges(null, ... );

Which will save all added, modified or deleted records in the entityManager.

Alternately you can specify all of the entities you want saved.

entityManager.saveChanges([order, orderItem1, orderItem2, ... ]);

Garzon

You are my hero - Thanks a million! – Dele