Duplicate Record issues during Upstate-Database command

Question

This question is related to:

Sequence contains more than one element error in EF CF Migrations

However I am having a different issue. I have duplicate fields being added to separate records as seen in the following code:

context.UnitOfMeasureDefaultBaseUnits.AddOrUpdate(x => x.Name,
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Pound", Active = true, Abbreviation = "lb", DisplayOrder = 1, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Weight")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Ounce", Active = true, Abbreviation = "oz", DisplayOrder = 2, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Weight")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Ton", Active = true, Abbreviation = "ton", DisplayOrder = 3, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Weight")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Gram", Active = true, Abbreviation = "g", DisplayOrder = 4, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Weight")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Kilogram", Active = true, Abbreviation = "kg", DisplayOrder = 5, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Weight")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Metric Ton", Active = true, Abbreviation = "t", DisplayOrder = 6, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Weight")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Other", Active = true, Abbreviation = "", DisplayOrder = 7, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Weight")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Hour", Active = true, Abbreviation = "hr", DisplayOrder = 1, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Time")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Minute", Active = true, Abbreviation = "min", DisplayOrder = 2, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Time")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Day", Active = true, Abbreviation = "day", DisplayOrder = 3, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Time")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Work Day", Active = true, Abbreviation = "wrkday", DisplayOrder = 4, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Time")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Other", Active = true, Abbreviation = "", DisplayOrder = 5, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Time")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Gallon", Active = true, Abbreviation = "gal", DisplayOrder = 1, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Volume")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Fluid Ounce", Active = true, Abbreviation = "floz", DisplayOrder = 2, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Volume")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Quart", Active = true, Abbreviation = "qt", DisplayOrder = 3, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Volume")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Liter", Active = true, Abbreviation = "l", DisplayOrder = 4, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Volume")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Cubic Inch", Active = true, Abbreviation = "cuin", DisplayOrder = 5, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Volume")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Cubic Centimeter", Active = true, Abbreviation = "cucm", DisplayOrder = 6, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Volume")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Other", Active = true, Abbreviation = "", DisplayOrder = 7, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Volume")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Foot", Active = true, Abbreviation = "ft", DisplayOrder = 1, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Length")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Inch", Active = true, Abbreviation = "in", DisplayOrder = 2, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Length")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Yard", Active = true, Abbreviation = "yd", DisplayOrder = 3, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Length")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Mile", Active = true, Abbreviation = "mi", DisplayOrder = 4, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Length")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Millimeter", Active = true, Abbreviation = "mm", DisplayOrder = 5, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Length")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Meter", Active = true, Abbreviation = "m", DisplayOrder = 6, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Length")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Other", Active = true, Abbreviation = "", DisplayOrder = 7, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Length")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Square Foot", Active = true, Abbreviation = "sqft", DisplayOrder = 1, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Area")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Square Inch", Active = true, Abbreviation = "sqin", DisplayOrder = 2, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Area")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Square Yard", Active = true, Abbreviation = "sqyd", DisplayOrder = 3, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Area")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Square Meter", Active = true, Abbreviation = "sqm", DisplayOrder = 4, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Area")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Other", Active = true, Abbreviation = "", DisplayOrder = 5, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Area")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Each", Active = true, Abbreviation = "ea", DisplayOrder = 1, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Count")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Pair", Active = true, Abbreviation = "pr", DisplayOrder = 2, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Count")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Dozen", Active = true, Abbreviation = "dz", DisplayOrder = 3, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Count")).RecordID },
    new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Other", Active = true, Abbreviation = "", DisplayOrder = 4, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Count")).RecordID }
);

The Update-Database command is pukeing because "Other" is used more than once with the following error:

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 Model.Migrations.Configuration.Seed(ApplicationDbContext context) in Z:\_Profile Storage\Projects\Corporate.Web.MVCAPI\Corporate.Web.MVCAPI\Model\Migrations\Configuration.cs:line 6744
   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

Is there a workaround for this issue? And what is it?

Steve Greene · Accepted Answer · 2016-01-28T22:01:26.607

2

The first parameter tells AddOrUpdate the key. You are using Name so Other can't be used multiple times. Switch it to something unique. Perhaps x => x.UnitOfMeasureTypeID ? Otherwise ditch the AddOrUpdate syntax and use conventional LINQ:

var weightUnitTypeId = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Weight")).RecordID;

if (!context.UnitOfMeasureDefaultBaseUnits.Any(u => u.UnitOfMeasureTypeID == weightUnitTypeId && u.Name == "Pound"))
{
     // insert new record
     var pound = new UnitOfMeasureDefaultBaseUnitsModel { Name = "Pound", Active = true, Abbreviation = "lb", DisplayOrder = 1, UnitOfMeasureTypeID = weightUnitTypeId };
     context.UnitOfMeasureDefaultBaseUnits.Add(pound);
}
// repeat for Ounce, Ton, etc.


var timeUnitTypeId = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Time")).RecordID;

if (!context.UnitOfMeasureDefaultBaseUnits.Any(u => u.UnitOfMeasureTypeID == timeUnitTypeId && u.Name == "hr"))
{
     // insert new record
     var time = new UnitOfMeasureDefaultBaseUnitsModel { Name = "hr", Active = true, Abbreviation = "hr", DisplayOrder = 1, UnitOfMeasureTypeID = weightUnitTypeId };
     context.UnitOfMeasureDefaultBaseUnits.Add(time);
}
// repeat for all time units

...
// similar for other units
...
context.SaveChanges();

edited Jan 28 '16 at 22:01

answered Jan 28 '16 at 15:59

Steve Greene

12,029
1
33
54

is there a way to check two keys instead of one? – John Schultz Jan 28 '16 at 20:21
Yes, x => new { x.part1, x.part2 } http://stackoverflow.com/questions/10234912/how-to-seed-data-with-addorupdate-with-a-complex-key-in-ef-4-3 – Steve Greene Jan 28 '16 at 20:24
now I am getting this `The binary operator Equal is not defined for the types 'System.Nullable'1[System.Guid]' and 'System.Guid'.` I think I am going to have to do old fashioned LINQ for this part of the seeding. – John Schultz Jan 28 '16 at 21:20
will this work: `if (!context.UnitOfMeasureDefaultBaseUnits.Any(u => u.UnitOfMeasureTypeID.Equals(context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Weight")).RecordID)) && !context.UnitOfMeasureDefaultBaseUnits.Any(u => u.Name.Equals("Pound"))) {new UnitOfMeasureDefaultBaseUnitsModel() { Name = "Pound", Active = true, Abbreviation = "lb", DisplayOrder = 1, UnitOfMeasureTypeID = context.UnitOfMeasureDefaultType.FirstOrDefault(r => r.Name.Equals("Weight")).RecordID };}` – John Schultz Jan 28 '16 at 21:37
I edited the answer. It can probably be more streamlined, but you get the idea (hopefully). – Steve Greene Jan 28 '16 at 22:02
AHHH, NICE. Thank you. – John Schultz Jan 28 '16 at 22:03

Duplicate Record issues during Upstate-Database command

1 Answers1