First, Entity Framework 6.0 Code - getting duplicate elements in a simple query when filtering with a primary key

Question

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

I upgraded the Entity Framework to version 6. A strange thing, which at some point after the update began to receive duplicate elements when filtering records using the primary key.

First of all, I started getting the exception “Sequence contains more than one element” in the following code

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

I checked the database, and the parameter - Id is the main key , it is marked as unique , and this parameter was valid. Since Id was set as the primary key in the mapping:

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

I replaced the FirstOrDefault call, and the code worked fine. I tried to get all the elements that calculate the predicate using the following code:

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

It seemed that I was getting 13 instances of the CateringService object referring to the same line. See attached screenshots:

I also started getting an exception 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. when accessing CateringService objects through a link to an object. We use a lazy approach, and lazy loading is allowed.

When I try to access the "CateringService" using Include("CateringService") everything works fine, but we cannot just replace all the calls to SingleOrDefault and remove all the lazy loads from the project at this point.

Please inform.

UPDATE

It’s a pity that I’m not entirely clear. There is one entry in the database that matches the condition. The column Id is set as the primary key, so it is unique.

UPDATE 2

Below is the code from the migration generated by EF based on smooth 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

Must use FirstOrDefault instead of SingleOrDefault . Because your short screen has the same meaning for Id cloumn. So you need to do this.

else you need to check the identifier - this is the primary key. and set the identifier yes for the Id column

because

FirstOrDefault ()

- this is when it is expected that the input collection will contain zero or more results, and the call returns the first element if there are several results. By default, if not.

SingleOrDefault ()

for those cases when the input collection expects zero or one result, and the call returns one result if there is only one result, by default , if there are no results and exceptions, if there is more than one result .

and refer to this LINQ: when to use SingleOrDefault and FirstOrDefault () with filter criteria

Updated:

Because you changed the database structure when the ID column was not set as IDENTITY yet, the first code model was generated using the DatabaseGeneratedOption.None set to the Id property for the object.

This forces EF to create an insert statement with a set of identifiers that no longer works after changing the column in IDENTITY.

You must manually fix this configuration by setting it to DatabaseGeneratedOption.Identity or simply deleting it completely, as this is the default value for whole 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 do not update identity property

Entity Framework 5 does not create database at first

Edit: delete the old table values ​​and recreate them or delete the same primary key rows. You will need to restore your CateringServices :) table values ​​if the problems have not yet been removed.

Answer 2

You can use .Top (1) instead of .FirstOrDefault () or .SingleOrDefault ().

I am new to Linq, but since I know that top () will only return the specified number of rows. I do not know about FirstOrDefault and SingleOrDefault.