Save multiple items that share the same categoryId-CodePudding

I am using SQLite with EFCore. I am trying to save several items that are part of the same category, but when I try to save them I am getting a foreign key constraint error.

The Error: "SQLite Error 19: 'FOREIGN KEY constraint failed'."

The models look like this:

public class Item
{
    public int ItemId { get; set; }
    public string ItemName {get; set; }
    [ForeignKey("CategoryId")]
    public int CategoryId { get; set; }
}

public class Category
{
    public int CategoryId { get; set; }
    public string CategoryName { get; set; }
    public List<Item>? Items { get; set; }
}

So a category can have a lot of items and many of them will each have the same category ID as a foreign key. Is this possible with SQLite?

CodePudding user response：

Your models are not configured right. Please, check the following article and configure your models as described.

public class Item
{
    public int Id { get; set; }
    public string Name {get; set; }

    public int CategoryId { get; set; }
    [ForeignKey("CategoryId")]
    public int Category { get; set; }
}

 public class Category
 {
    public int Id { get; set; }
    public string CategoryName { get; set; }
    public List<Item> Items { get; set; }
 }

CodePudding user response：

Problem might be in the [ForeignKey] attribute. Models like this should configure themselves by convention (you have List<Item> on Category, that is enough for EF Core to know what you mean).

I never used the ForeignKeyAttribute to configure my models, but documentation sounds like it specifies name of the property in the OTHER table that it should use as foreign key to reference this entity. Used like this I would expect EF to just reject the model as invalid, or create some sort of self-referencing Item. Try moving it to Category.Items. Or just get rid of it.

For comparison, equivalent fluent configuration would be:

public void Configure(EntityTypeBuilder<Category> builder)
{
    builder
        .HasMany(x => x.Items)
        .WithOne(/* x => x.Category - this would apply if you had dual Category/CategoryId on Item */)
        .HasForeignKey(x => x.CategoryId);
}

Here we start on category table, specify the other table and HasForeignKey says what column in the other table it should use.