Sequence contains more than one element c# Entity Seed

Question

Trying to seed Countries from CSV Entity but getting this error when ever i run Update-Database. If i delete the table and run Update-Database then the error doesn't show up but if i run Update-Database again.

CSV file

Name,
China,
India,
United States,
Indonesia,
Brazil,
Pakistan,
United Kingdom,
Bangladesh,
Russia,
Japan,
Mexico,
Philippines,
Vietnam,
Ethiopia,
Egypt,
Germany,
Iran,
Turkey,
Democratic Republic of the Congo,
Thailand,
France,
Italy,
Burma,
South Africa,
South Korea,
Colombia,
Spain,
Ukraine,

My Country Model

namespace Domain
{
    public class Country : BaseModel
    {
        public string Name { get; set; }
    }
}



using System.ComponentModel.DataAnnotations;

namespace Domain
{
    public class BaseModel
    {
        [Key]
        public int Id { get; set; }
    }
}

Seed

var assembly = Assembly.GetExecutingAssembly();

const string country = "Service.Migrations.Seed.countries.csv";
            using (var stream = assembly.GetManifestResourceStream(country))
            {
                using (var reader = new StreamReader(stream, Encoding.UTF8))
                {
                    var csvReader = new CsvReader(reader);
                    csvReader.Configuration.Delimiter = ",";
                    csvReader.Configuration.WillThrowOnMissingField = false;
                    var countries = csvReader.GetRecords<Country>().ToArray();
                }
            }

Error

No pending explicit migrations.
Running Seed method.
System.InvalidOperationException: Sequence contains more than one element
   at System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable`1 source)
   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__2[TResult](IEnumerable`1 sequence)
   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
   at System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.SingleOrDefault[TSource](IQueryable`1 source, Expression`1 predicate)
   at System.Data.Entity.Migrations.DbSetMigrationsExtensions.AddOrUpdate[TEntity](DbSet`1 set, IEnumerable`1 identifyingProperties, InternalSet`1 internalSet, TEntity[] entities)
   at System.Data.Entity.Migrations.DbSetMigrationsExtensions.AddOrUpdate[TEntity](IDbSet`1 set, Expression`1 identifierExpression, TEntity[] entities)
   at Service.Migrations.Configuration.Seed(HotelContext context) in C:\Hotel+\Service\Migrations\Configuration.cs:line 55
   at System.Data.Entity.Migrations.DbMigrationsConfiguration`1.OnSeed(DbContext context)
   at System.Data.Entity.Migrations.DbMigrator.SeedDatabase()
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.SeedDatabase()
   at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
   at System.Data.Entity.Migrations.DbMigrator.UpdateInternal(String targetMigration)
   at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClassc.<Update>b__b()
   at System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
   at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration)
   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration)
   at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.Run()
   at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
   at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
   at System.Data.Entity.Migrations.Design.ToolingFacade.Run(BaseRunner runner)
   at System.Data.Entity.Migrations.Design.ToolingFacade.Update(String targetMigration, Boolean force)
   at System.Data.Entity.Migrations.UpdateDatabaseCommand.<>c__DisplayClass2.<.ctor>b__0()
   at System.Data.Entity.Migrations.MigrationsDomainCommand.Execute(Action command)
Sequence contains more than one element

Answer 1

This is how i solved the problem.

var assembly = Assembly.GetExecutingAssembly();
const string country = "Service.Migrations.Seed.countries.csv";
            using (var stream = assembly.GetManifestResourceStream(country))
            {
                using (var reader = new StreamReader(stream, Encoding.UTF8))
                {
                    var csvReader = new CsvReader(reader);
                    csvReader.Configuration.Delimiter = ",";
                    csvReader.Configuration.WillThrowOnMissingField = false;
                    var countries = csvReader.GetRecords<Country>().ToArray();
                    foreach (var c in countries)
                    {
                        var check = context.Countries.FirstOrDefault(p => p.Name == c.Name);
                        if (check == null)
                        {
                            context.Countries.Add(c);
                            context.SaveChanges();
                        }
                    }
                }
            }

Answer 2

Your ID is auto-increment so if you do not check if the current Country Name is already in your database, EF will create a new entry with a new ID...

You have two solutions to avoid this :

Solution 1 :

Loop inside the Countries in your database and check if the name already exist. If that the case do not add it again.

Solution 2 :

Set the Country Name as unique like this :

namespace Domain
{
    public class Country : BaseModel
    {
        [Index(IsUnique = true)]
        public string Name { get; set; }
    }
}

This will throw an exception when you will try to add a name that already exit in your database. You can manage the error with a try-catch and log the exception for example.