實體框架父 - >子鏈接和外鍵約束失敗錯誤

c# entity-framework-core foreign-key-relationship sqlite

我正在使用實體框架7(核心)和Sqlite數據庫。目前使用comboBox使用此方法更改實體類別:

/// <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;
}

此功能改變為類別ID device就好了。但這是正確的方法嗎?

在鏈接之後,然後在刪除此category我從Sqlite數據庫收到錯誤:

{“SQLite錯誤19:'FOREIGN KEY約束失敗'”}

刪除類別方法

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;
}

編輯以下是devicecategory的結構:

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,
)

一般承認的答案

您的SQLite表具有外鍵限制ON DELETE RESTRICT 。這意味著如果“設備”中的任何行仍指向您嘗試刪除的類別,則SQLite數據庫將阻止此操作。要解決此問題,您可以(1)明確地將所有設備更改為其他類別,或(2)將ON DELETE行為更改為其他內容,例如CASCADESET NULL 。請參閱https://www.sqlite.org/foreignkeys.html#fk_actions

如果已使用EF創建表,則將模型配置為使用不同的刪除行為。默認值為restrict。請參閱https://docs.efproject.net/en/latest/modeling/relationships.html#id2 。例:

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


Related

許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
這個KB合法嗎? 是的,了解原因
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
這個KB合法嗎? 是的,了解原因