c# - Sequence contains more than one element error in EF CF Migrations

Question

I created some models, added the migration and then did an update database operation, though at my last update database operation I got the error message saying:

Sequence contains more than one element

Below you can find my migration configuration:

context.Categories.AddOrUpdate(p => p.CategoryName,
    new Category
    {
        CategoryName = "Sport"
    },
    new Category
    {
        CategoryName = "Music"
    }
);

context.Subcategories.AddOrUpdate(p => p.SubcategoryName,
    new Subcategory
    {
        SubcategoryName = "Football"
    },
    new Subcategory
    {
        SubcategoryName = "Basketball"
    },
    new Subcategory
    {
        SubcategoryName = "Piano"
    },
    new Subcategory
    {
        SubcategoryName = "Violin"
    }
);

context.Services.AddOrUpdate(p => p.ServiceType,
    new Service
    {
        ServiceType = "Football player",
        Category = { CategoryName = "Sport" },
        Subcategory = { SubcategoryName = "Football" }
    },
    new Service 
    {
        ServiceType = "Piano lessons",
        Category = { CategoryName = "Music" },
        Subcategory = { SubcategoryName = "Piano" }
    }
);

The problem occurs with when I add new Services. I already have categories and subcategories, and if I do like Category = new Category { CategoryName = "Music" } then it works but I get Music entry twice in my database (for this example). I want to use the already added categories and subcategories. Below also you can find my models definitions.

public class Category
{
    [Key]
    public int CategoryID { get; set; }

    public string CategoryName { get; set; }
}

// Subcategory is defined the same way...

public class Service
{
    public int ServiceID { get; set; }

    public string ServiceType { get; set; }

    public virtual Category Category { get; set; }

    public virtual Subcategory Subcategory { get; set; }

}

Any idea how to solve it?

Answer 1

Sequence contains more than one element

This exception was caused when trying to use AddOrUpdate with specifying identifier expression, like p => p.CategoryName. There might be two Categories that have the same name "Sport" or "Music".

This might also happen on Subcategories and Services, Subcategories uses p => p.SubcategoryName and Services uses p => p.ServiceType.

You need to clean the duplicate entry first on database. This is a must, since you want to use AddOrUpdate, internally this code will use SingleOrDefault and if there is found more than one match element, exception will be thrown.

Object reference not set to an instance of an object

This error is probably caused by this code.

Category = { CategoryName = "Sport" },
Subcategory = { SubcategoryName = "Football" }

Creating new Service will have null Category by default, you can't just directly set the CategoryName.

The problem occurs with when I add new Services. I want to use the already added categories and subcategories.

The Categories and Subcategories should be stored on variables so it can be used by Service.

var sportCategory = new Category { CategoryName = "Sport" };
var musicCategory = new Category { CategoryName = "Music" };
context.Categories.AddOrUpdate(p => p.CategoryName, 
   sportCategory, musicCategory);

var footballSub = new Subcategory { SubcategoryName = "Football" };
var basketballSub = new Subcategory { SubcategoryName = "Basketball" };
var pianoSub = new Subcategory { SubcategoryName = "Piano" };
var violinSub = new Subcategory { SubcategoryName = "Violin" };
context.Subcategories.AddOrUpdate(p => p.SubcategoryName,
   footbalSub, basketballSub , pianoSub, violinSub);

context.Services.AddOrUpdate(p => p.ServiceType,
    new Service
    {
        ServiceType = "Football player",
        Category = sportCategory,
        Subcategory = footballSub
    },
    new Service 
    {
        ServiceType = "Piano lessons",
        Category = musicCategory,
        Subcategory = pianoSub
    }
);

But above code still has problem, if service is new entity, the existing sport category and the existing football subcategory will also be added. You can read this article for further explanation.

Solution

You need to also have foreign key values on Service not only foreign key references to prevent adding existing category and existing subcategory.

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

Then run the migration.

Add-Migration AddServiceFKValues

And assign temporary primary key manually and use it when defining service. This temporary primary keys are not needed when dealing with existing entities, but there might be another problem if there is more than one new category or subcategory. To prevent that, it's better to use temporary primary keys, after calling AddOrUpdate, each entity primary keys will be updated if they are existing entities.

var sportCategory = new Category { CategoryID = 1000, CategoryName = "Sport" };
var musicCategory = new Category { CategoryID = 1001, CategoryName = "Music" };
context.Categories.AddOrUpdate(p => p.CategoryName, 
   sportCategory, musicCategory);

var footballSub = new Subcategory { SubcategoryID = 1000, SubcategoryName = "Football" };
var basketballSub = new Subcategory { SubcategoryID = 1001, SubcategoryName = "Basketball" };
var pianoSub = new Subcategory { SubcategoryID = 1002, SubcategoryName = "Piano" };
var violinSub = new Subcategory { SubcategoryID = 1003, SubcategoryName = "Violin" };
context.Subcategories.AddOrUpdate(p => p.SubcategoryName,
   footbalSub, basketballSub , pianoSub, violinSub);

context.Services.AddOrUpdate(p => p.ServiceType,
    new Service
    {
        ServiceType = "Football player",
        CategoryID = sportCategory.CategoryID,
        SubcategoryID = footballSub.SubcategoryID
    },
    new Service 
    {
        ServiceType = "Piano lessons",
        CategoryID = musicCategory.CategoryID,
        SubcategoryID = pianoSub.SubcategoryID
    }
);

Then update the database.

Update-Database