6

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:

enter image description here enter image description here

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);
danyloid
  • 1,677
  • 3
  • 21
  • 47
  • What's the definition of `query` in your code? (`x.Id == query.CateringServiceId`) – Richard Deeming Dec 16 '13 at 16:56
  • It is instance of a really simple class - having just one property `public Int32 CateringServiceId { get; set; }` – danyloid Dec 16 '13 at 17:21
  • but I've tried using the hard-coded value, e.g. `x => x.Id == 1`. or any other existing id in the database. still getting the same result. – danyloid Dec 16 '13 at 17:25
  • Have you checked the related tables (`Addresses`, `CateringServiceGroups` and `ContactInformation`) to see if they contain duplicate records? – Richard Deeming Dec 16 '13 at 18:04
  • yes, they don't but it seems that the problem was in a different entity mapping in the following lines: `this.HasOptional(x => x.CateringService).WithOptionalDependent().Map(x => x.MapKey("CateringServiceId"));`. for some reason when querying the `CateringServices` table there was an left outer join performed. – danyloid Dec 16 '13 at 18:23
  • Could you please check the SQL query generated by Entity Framework and may be there will be a clue to what is wrong? Based on your description I think there is more than one relation between `CateringServices` and other entities and this causes duplicates. – Aleksei Poliakov Dec 21 '13 at 18:12

2 Answers2

2

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.

Community
  • 1
  • 1
Ramesh Rajendran
  • 37,412
  • 45
  • 153
  • 234
  • Right. But if you've read the title of the question you could have noticed that this is not quite the case where the FirstOrDefault should have been used. But the problem is that there should be a single record. As it is in the database. And anyway your answer does not solve the issue with the 'A relationship multiplicity constraint violation occurred' error. – danyloid Dec 12 '13 at 18:20
  • Yup, But your screen short have the same value for Id cloumn.So you need want to this – Ramesh Rajendran Dec 12 '13 at 18:22
  • Right. That is what I'm asking about. I've stated that the ID was unique in the database. – danyloid Dec 12 '13 at 18:23
  • "I've checked the database and the parameter - the Id is a primary key, it is unique, and the parameter was valid." – danyloid Dec 12 '13 at 18:24
  • read one more time in my answer. have you set IsIdentity=true in your Id coulumn? – Ramesh Rajendran Dec 12 '13 at 18:25
  • Can you show your primary key setting? and Is Identity setings? – Ramesh Rajendran Dec 12 '13 at 18:26
  • I did. I'm using the EF Code First and Fluent Mappings. That is the code responsible for that - this.HasKey(x => x.Id); – danyloid Dec 12 '13 at 18:28
  • I think the isidentity is not set, So you got the problem.Please make sure the is identity is set :http://www.google.co.in/imgres?sa=X&biw=1024&bih=666&tbm=isch&tbnid=9I8Owmy37WV6nM:&imgrefurl=https://blogs.msdn.com/b/bethmassi/archive/2009/09/15/inserting-master-detail-data-into-a-sql-server-compact-edition-database.aspx&docid=HVN6riU4ED5T-M&imgurl=http://blogs.msdn.com/blogfiles/bethmassi/WindowsLiveWriter/InsertingMasterDetailDataintoaSQLServerC_C610/image_6.png&w=713&h=582&ei=DQCqUvaEDMnprAe-poHgCw&zoom=1&iact=rc&page=1&tbnh=168&tbnw=206&start=0&ndsp=13&ved=1t:429,r:7,s:0&tx=878&ty=425 – Ramesh Rajendran Dec 12 '13 at 18:29
  • I may add the fluent mapping as well, but it will make the question too hard to read. – danyloid Dec 12 '13 at 18:33
  • I have Edit my answer .please check it – Ramesh Rajendran Dec 12 '13 at 18:35
  • I have update the solution, please take a look at now :) – Ramesh Rajendran Dec 12 '13 at 18:40
  • We did not reverse engineered the database. The project was using the EF Code First from the beginning. We've started to experience the issue just after the update to the 6th version. However your solution worked )) – danyloid Dec 12 '13 at 18:47
  • sorry for unmarking question and thanks for the effort. however the project appeared again. it seems like there is another reason in that. – danyloid Dec 16 '13 at 16:21
  • @danyloid - I think your database have old values with same primary key value, So you got the error, clear the table and one more try – Ramesh Rajendran Dec 17 '13 at 04:54
0

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

I am new for Linq but as i know top() will return only specified no of rows.I don't know about FirstOrDefault and SingleOrDefault.

Anand
  • 31
  • 5
  • Actually you should use FirstOrDefault, otherwise top(1) still returns an IEnumberable. FirstOrDefault() will return the first item in the sequence if there are _any_ or will return null SingleOrDefault will return null if there are none, the item if there is one (and only one) and throw an exception if there are multiple items – finman Dec 23 '13 at 09:55