EF Core delete one-to-one relation on same table

Question

A model have optional relation to itself

public class Item
{
    public Guid Id { get; set; }
    public string Description { get; set; }
    public Guid StockId { get; set; }

    // optionally reference to another item from different stock
    public Guid? OptionalItemId { get; set; }

    public virtual Item OptionalItem { get; set; }      
}

In DbContext model configured as below:

protected override void OnModelCreating(ModelBuilder builder)
{
     builder.Entity<Item>().HasOne(item => item.OptionalItem)
                           .WithOne()
                           .HasForeignKey<Item>(item => item.OptionalItemId)
                           .HasPrincipalKey<Item>(item => item.Id)
                           .IsRequired(false)
}

I want to replace existed items with new items by deleting existed before updating Stock with new items.

// Given Stock contains only new items
public void Update(Stock stock)
{
    using (var context = CreateContext())
    {
        // Remove old items
        var oldItems = context.Items
                              .Where(item => item.StockId == stock.Id)
                              .Select(item => new Item { Id = item.Id })
                              .ToList();
        context.Items.RemoveRange(oldItems);

        // Remove optional items from another stock
        var oldOptionalItems = context.Items
                                      .Where(item => item.StockId == stock.RelatedStock.Id)
                                      .Select(item => new Item { Id = item.Id })
                                      .ToList();
        context.Items.RemoveRange(oldOptionalItems);   

        context.Stocks.Update(stock);
        context.SaveChanges();         
    }
}

Problems is that when Update method executes, line context.SaveChanges() throws an exception:

SqlException: The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "FK_Item_Item_OptionalItemId". The conflict occurred in database "local-database", table "dbo.Item", column 'OptionalItemId'.

I found another question with similar problem: The DELETE statement conflicted with the SAME TABLE REFERENCE constraint with Entity Framework.
But looks like all answers are Entity Framework (not EF Core) related.

I tried change delete behavior to
- .OnDelete(DeleteBehavior.Cascade)
and
- .OnDelete(DeleteBehavior.SetNull)
but both behaviours will throw an exception below during applying migration to the database.

Introducing FOREIGN KEY constraint 'FK_Item_Item_OptionalItemId' on table 'Item' may cause cycles or multiple cascade paths.
Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Answer 1

As usual when you are not allowed to use cascade delete option (SqlServer limitation btw, some databases like Oracle has no such issues), you need to (recursively) delete the related data before deleting the record.

It could be done one by one or by levels (less SQL commands, but potentially using big IN PK lists). The related data can also be determined with CTE based SQL - the most efficient, but database agnostic way.

The following method implements the second approach:

static void DeleteItems(DbContext context, Expression<Func<Item, bool>> filter)
{
    var items = context.Set<Item>().Where(filter).ToList();
    if (items.Count == 0) return;
    var itemIds = items.Select(e => e.Id);
    DeleteItems(context, e => e.OptionalItemId != null && itemIds.Contains(e.OptionalItemId.Value));
    context.RemoveRange(items);
}

and can be used in your code like this:

using (var context = CreateContext())
{
    // Remove old items
    DeleteItems(context, item => item.StockId == stock.Id);

    // Remove optional items from another stock
    DeleteItems(context, item => item.StockId == stock.RelatedStock.Id);

    // The rest...  
}

Answer 2

Only as an addition to the @Ivan's answer.

Item have a foreign key of OptionalItem, that mean that Item is dependent on OptionalItem.

`Item`(dependent) -> `OptionalItem`(principal)

EF Core supports "cascade deletes" from the principal to the dependent. As Ivan Stoev mentioned, exception during migrations is Sql Server limitation. But EF Core will still supports it, you can try
- Add .OnDelete(DeleteBehavior.Cascade)
- run dotnet ef migrations add <migration-name>
- update generated migration script by removing CASCADE action
- update database with just created migration

You will not get an exception during applying migrations to the database.
Note:
1. (again) EF Core supports cascade deletes from the principal to the dependent
related Item will be deleted when you deleting record of OptionalItem
2. EF Core will automatically delete only related records which already tracked by DbContext(loaded in the memory)

So in your case, you can try to remove principal items (OptionalItem) before dependent Item, but in separated commands.
Execute all in the transaction, so operation will be rolled back when error occurs.

public void Update(Stock stock)
{
    using (var context = CreateContext())
    using (var transaction = context.Database.BeginTransaction())
    {
        // Remove optional items from another stock
        // This is principal record in the items relation
        var oldOptionalItems = context.Items
                                      .Where(item => item.StockId == stock.RelatedStock.Id)
                                      .Select(item => new Item { Id = item.Id })
                                      .ToList();
        context.Items.RemoveRange(oldOptionalItems);

        // Remove them actually from the database
        context.SaveChanges();

        // Remove old items
        var oldItems = context.Items
                          .Where(item => item.StockId == stock.Id)
                          .Select(item => new Item { Id = item.Id })
                          .ToList();
        context.Items.RemoveRange(oldItems);

        context.Stocks.Update(stock);
        context.SaveChanges();         
    }
}