Entity framework parent -> child linking and foreign key constrain failed error

Question

I'm using entity framework 7 (core) and Sqlite database. Currently using comboBox to change entity category with this method:

/// <summary>
/// Changes the given device gategory.
/// </summary>
/// <param name="device"></param>
/// <param name="category"></param>
public bool ChangeCategory(Device device, Category category)
{
    if (device != null && category != null )
    {
        try
        {
            var selectedCategory = FxContext.Categories.SingleOrDefault(s => s.Name == category.Name);
            if (selectedCategory == null) return false;
            if (device.Category1 == selectedCategory) return true;

            device.Category1 = selectedCategory;
            device.Category = selectedCategory.Name;
            device.TimeCreated = DateTime.Now;
            return true;
        }
        catch (Exception ex)
        {
            throw new InvalidOperationException("Category change for device failed. Possible reason: database has multiple categories with same name.");
        }
    }
    return false;
}

This function changes the category Id for the device just fine. But is this correct way?

After linking and then later on while deleting this category I get an error from the Sqlite database:

{"SQLite Error 19: 'FOREIGN KEY constraint failed'"}

The delete category method

public bool RemoveCategory(Category category)
{
    if (category == null) return false;
    var itemForDeletion = FxContext.Categories
        .Where(d => d.CategoryId == category.CategoryId);
    FxContext.Categories.RemoveRange(itemForDeletion);
    return true;
}

EDIT Here are the structures of device and category:

CREATE TABLE "Category" (
    "CategoryId" INTEGER NOT NULL CONSTRAINT "PK_Category" PRIMARY KEY AUTOINCREMENT,
    "Description" TEXT,
    "HasErrors" INTEGER NOT NULL,
    "IsValid" INTEGER NOT NULL,
    "Name" TEXT
)

CREATE TABLE "Device" (
    "DeviceId" INTEGER NOT NULL CONSTRAINT "PK_Device" PRIMARY KEY AUTOINCREMENT,
    "Category" TEXT,
    "Category1CategoryId" INTEGER,
    CONSTRAINT "FK_Device_Category_Category1CategoryId" FOREIGN KEY ("Category1CategoryId") REFERENCES "Category" ("CategoryId") ON DELETE RESTRICT,
)

Solution

Your SQLite table has a foreign key restriction ON DELETE RESTRICT. This means if any row in Devices still points to the category you are trying to delete, the SQLite database will prevent this operation. To get around this, you can (1) explictly change all Devices to a different category or (2) change the ON DELETE behavior to something else, such as CASCADE or SET NULL. See https://www.sqlite.org/foreignkeys.html#fk_actions

If you have used EF to create your tables, then configure your model to use a different on delete behavior. The default is restrict. See https://docs.efproject.net/en/latest/modeling/relationships.html#id2. Example:

        modelBuilder.Entity<Post>()
            .HasOne(p => p.Blog)
            .WithMany(b => b.Posts)
            .OnDelete(DeleteBehavior.Cascade);

Comments

[ajr]

Aa yes, basically this error is desired. However, in some cases this exception is not always occurring, and database allows the delete of a category where some devices are pointing. For example when I have started the app and remove one category, but if during the runtime I modify devices category field and afterwards try to remove it, this exception is occurring.

[ajr]

Would you happen to have any good source/tutorial to read about the correct ways to handle the exceptions of entity framework SaveChanges() method?