Entity Framework 6.0 Code first - getting duplicated items in a simple query when filtering by primary key

Question

In my project we are using the EF Code First (v.6.0.0.0) and the MS SQL Server 2012.

I've updated to the Entity Framework to the 6th version. The strange thing that at some point after the update I started getting duplicated items while filtering records by the primary key.

First of all I started to get the 'Sequence contains more than one element' exception in the following code

var cateringService = context.CateringServices
                             .SingleOrDefault(x => x.Id == query.CateringServiceId)

I've checked the database and the parameter - the Id is a primary key, it is marked as unique, and the parameter was valid. As the Id was set as primary key in the mapping:

this.HasKey(x => x.Id);

I've replaces the call with the FirstOrDefault and code worked well. I've tried to retrieve all the items that are mathing the predicate using the following code:

var cateringServices = context.CateringServices
                              .Where(x => x.Id == query.CateringServiceId)
                              .ToList();

It seemed that I'm getting the 13 instances of the 'CateringService' entity referencing the same row. Please look at the screenshots attached:

As well I've started to get the A relationship multiplicity constraint violation occurred: An EntityReference can have no more than one related object, but the query returned more than one related object. exception while accessing the CateringService entities via entity reference. We are using the lazy approach and lazy loading is enabled.

When trying to access the 'CateringService' using the Include("CateringService") everythings works well, but we can not just replace all the SingleOrDefault calls and remove all the lazy loading usages from the project at this point.

Please advise.

UPDATE

Sorry for being not quite clear. There is a single record in the database that matches the condition. The Id column is set as the Primary Key so it is unique.

UPDATE 2

Below is the code from the migration generated by EF based on fluent mappings.

CreateTable(
            "dbo.CateringServices",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Name = c.String(nullable: false, maxLength: 200),
                    CreatedDate = c.DateTime(nullable: false),
                    CultureString = c.String(maxLength: 10),
                    AddressId = c.Int(),
                    CateringServiceGroupId = c.Int(),
                    ContactInformationId = c.Int(),
                })
            .PrimaryKey(t => t.Id)
            .ForeignKey("dbo.Addresses", t => t.AddressId, cascadeDelete: true)
            .ForeignKey("dbo.CateringServiceGroups", t => t.CateringServiceGroupId)
            .ForeignKey("dbo.ContactInformation", t => t.ContactInformationId, cascadeDelete: true)
            .Index(t => t.AddressId)
            .Index(t => t.CateringServiceGroupId)
            .Index(t => t.ContactInformationId);

Answer 1

Should use FirstOrDefault instead of SingleOrDefault.because your screen short have the same value for Id cloumn.So you need want to this .

else you need must check id is a primary key. and check set is identity yes for your Id column

because

FirstOrDefault()

is for when zero or more results are expected to be present in the input collection and the call returns the first item if there are multiple results, Default if none.

SingleOrDefault()

is for when zero or one result is expected in the input collection and the call returns the one result if exactly one result is present, Default if no results and exception if more than one result.

and refer this LINQ: When to use SingleOrDefault vs. FirstOrDefault() with filtering criteria

Updated:

Since you reverse-engineered the database when the Id column was not set as IDENTITY yet, the code first model has been generated with DatabaseGeneratedOption.None set for the Id property on the entity.

That causes EF to create an insert statement with the Id set, which no longer works after changing the column to IDENTITY.

You have to manually fix that configuration, by either setting it to DatabaseGeneratedOption.Identity or just removing it altogether, since that is the default value for integer fields.

You need to change

this.HasKey(e => e.Id);
   Property(e => e.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

More deatisls

EF Code First 5.0.rc Migrations doesn`t update Identity property

Entity Framework 5 code-first not creating database

Edit : Please deleted your old table values and re generate it or remove same primary key rows. You should need to regenerate your CateringServices table values :), if the problems not cleared yet.