entity-frameworkentity-framework-coreentity-framework-extensionsef-bulkinsert

BulkMerge with child entity gives FK constraint


I'm using Entity Framework Extensions but can't get the simplest BulkMerge to work.

Basically I have a DbContext with Stock. Each Stock has a IList<StockPrice>. Whenever a the stock price is updated, it is added to the list of prices. It should also check if the price actually changed since last update. If the value is the same as the previous item, no update is necessary. The stock itself can also be updated with a new name.

My problem is that whenever I try to update the item I get a foreign key constraint error:

The MERGE statement conflicted with the REFERENCE constraint "FK_StockPrice_Stock_StockId". The conflict occurred in database "Stock", table "dbo.StockPrice", column 'StockId'

To make it somewhat more difficult, I don't know the ID/PK of the Stock, which forces me to use ColumnPrimaryKeyExpression, which does seem to work.

However, I can't get the related entity to be saved. But whenever the child entity is involved I get a foreign key error.

Complete reproducible example

I currently have a semi-working way of doing this with EF which currently does the following steps:

  1. Fetch the Stock-item from DB based on non-clustered index.
  2. Check if the item exist. If it exists - update, otherwise create.
  3. Check child items (price). If exists with same value - do nothing, otherwise create.
  4. SaveChanges()

SaveChanges can handle several items at the time. But querying every item to memory (or a couple at the time) is not always very efficient. It handles millions of rows each run (in a transaction). Therefore I'm looking for a solution for doing it more efficient.


Solution

  • Disclaimer: I'm the owner of Entity Framework Extensions and Entity Framework Plus

    Here are 2 working fiddles for your scenario (I forked your reproducible example):

    The problem in your reproducible example is that your StockPrice need to be updated using the StockID value but when you insert/update your Stock entity, you use a custom primary key and doesn't return the StockID value.

    Solution #1

    Fiddle: https://dotnetfiddle.net/aaNFDG

    In the solution #1, we added a reference to our other library: Entity Framework Plus, which has a dependency to Entity Framework Extensions.

    In this solution, we choose to retrieve the Id (StockID) first to the database so as not to have to handle a custom primary key logic.

    For this, we use the SyncPropertiesFromDatabaseValues method that allows you to sync the value you want.

    So the following code has been added:

    context.SyncPropertiesFromDatabaseValues(items, options =>
    {
        options.ColumnPrimaryKeyExpression = x => new { x.MarketId, x.SectorId };
        options.SyncPropertyExpression = x => new { x.Id };
    });  
    

    After it run, your Stock entity will now have the right Id populated and you don't have to handle a custom key logic.

    For the StockPrice part, we modified it to get these 2 lines:

    bulk.ColumnPrimaryKeyExpression = x => new { x.Price, x.StockId };
    bulk.IgnoreOnMergeUpdateExpression = x => x.Id;
    

    Since you want to add a new row and not update the existing row, you can use the combination of Price and StockId as your primary key.

    And we ignore the Id as you don't want to update it.

    Solution #2

    Fiddle: https://dotnetfiddle.net/zFiPOT

    In the solution #2, we added this 2 lines for the Stock part:

    bulk.ColumnOutputExpression = x => x.Id;
    bulk.IgnoreOnMergeUpdateExpression = x => x.Id;
    

    Since you use a custom key, we specifying our library that you want to return the Id and never update the value in the database which is very important as well.

    For the StockPrice part, we did exactly what we did in the solution #1. We modified it to get these 2 lines:

    bulk.ColumnPrimaryKeyExpression = x => new { x.Price, x.StockId };
    bulk.IgnoreOnMergeUpdateExpression = x => x.Id;
    

    Since you want to add a new row and not update the existing row, you can use the combination of Price and StockId as your primary key.

    And again, we ignore the Id as you don't want to update it.


    BONUS: If you wish to update the price only when it's modified instead then you need to use MergeMatchedAndOneNotConditionExpression option such as:

    bulk.ColumnPrimaryKeyExpression = x => new { x.StockId };
    bulk.MergeMatchedAndOneNotConditionExpression = x => new { x.Price};
    

    So you use the StockId as you key and the row will only be updated if the price is different than the one in the database.