Entity Framework inserts the same entity twice

Question

I have the following structure, mapped with Entity Framework 6 using the Database First principle:

Here is the source database:

CREATE TABLE `Foo` (
  `Guid` VARCHAR(36),
  `Name` VARCHAR(500) NOT NULL,
  `Author` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`Guid`),
  UNIQUE KEY `unique_fooname` (`Name`,`Author`));

CREATE TABLE `FooVersion` (
  `Guid` VARCHAR(36),
  `Version` INT,
  `RefFooGuid` VARCHAR(36) NOT NULL,
  PRIMARY KEY (`Guid`),
  UNIQUE KEY `unique_fooversion` (`Version`,`RefFooGuid`),
  CONSTRAINT `fk_foo_version`
    FOREIGN KEY (`RefFooGuid`)  
    REFERENCES `Foo` (`Guid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE `FooVersionPart` (
  `Name` VARCHAR(250) NOT NULL,
  `RefFooVersionGuid` VARCHAR(36) NOT NULL,
  PRIMARY KEY (`Name`, `RefFooVersionGuid`),
  INDEX `fk_fooversion_fooversionpart_idx` (`RefFooVersionGuid` ASC),
  CONSTRAINT `fk_fooversion_fooversionpart`
    FOREIGN KEY (`RefFooVersionGuid`)
    REFERENCES `FooVersion` (`Guid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

At one point in my code, I am creating a new foo like this:

var dbContext = new DbContext();
var newVersion = new FooVersion();

newVersion.Guid = Guid.NewGuid().ToString()
newVersion.Parts = sourceParts.Select(s => new FooVersionPart
{
    Name = s.Name,
    RefFooVersionGuid = newVersion.Guid
}.ToList(); 

var foo = new Foo
{
    Author = "Me"
    Guid = Guid.NewGuid().ToString(),
    Name = "Foo"
};

dbContext.Foos.Add(foo);

foo.Versions.Add(newVersion);

dbContext.SaveChanges();

I am getting the following error during the SaveChanges:

Duplicate entry 'dim.proran.db.tmp.dataCallistHDay -9e6620f4-227d-44de-b781-5fd67' for key 'PRIMARY'

The errors occurs more specifically when EF is trying to insert one of the FooVersionPart (dim.proran.db.tmp.dataCallistHDay is the Name of that part and 9e6620f4-227d-44de-b781-5fd67 is the -truncated- RefFooVersionGuid of that part).

I have the absolute certainty sourceParts have no duplicate and neither in the database.

Here is the generated SQL:

INSERT INTO Foo [..];

INSERT INTO FooVersion [..];

INSERT INTO FooVersionPart [..];

INSERT INTO FooVersionPart [..];

INSERT INTO FooVersionPart [..];

INSERT INTO FooVersionPart [..];

Etc

The exception always occurs on the same FooVersionPart (dim.proran.db.tmp.dataCallistHDay). It is the 1910th elements of 2435. So EF is not trying to insert twice all parts, just one at the middle.

The weirdest thing is that it worked a while ago, and it does not work anymore with no changes in all the related stuff (no changes in the schema, no library update, no changes in the code). It works well in one of my environment, and it does not work with the same code in my dev environment.

One last thing, it is not specific to that Guid. At each attempt, the Guid is different (not the other inputs, so it still fails on dim.proran.db.tmp.dataCallistHDay), and at each attempt I get the same error.

Do you have any idea of what could cause that?

Without seeing your code or source data, how can we really help? — DavidG, Jul 18 '17 at 13:35
How can I show you the 2435 elements I am inserting? I show you what I though was relevent of my code. If you want more specific elements, just ask. — fharreau, Jul 18 '17 at 13:38
Well you need to debug this yourself. Look at the error, what is `dim.proran.db.tmp.dataCallistHDay`? That object isn't mentioned in the question. — DavidG, Jul 18 '17 at 13:40
@DavidG I did not clearly explain where the error occurs. I tried to be clearer in my last edit. — fharreau, Jul 18 '17 at 13:48
@mjwills The FooVersionPart has a composite primary key (`Name` and `RefFooVersionGuid`). As I said (possibly mis-said, I am not a native speaker), there is no record with that key in the database. The problem is coming from EF executing the same insert twice (there is no duplicate in the `sourceParts` neither. — fharreau, Jul 18 '17 at 13:51
@fharreau that's not how SO works. It's a Q&A site, not a discussion forum. A Question should describe the problem not only to make finding a solution easier for *you*, but anyone else that may have the same problem. It's not hard to post code that *reproduces* the problem. At the very least, post the *actual* class and table names. Where did `dataCallistHDay` come from ? — Panagiotis Kanavos, Jul 18 '17 at 13:51
I confirm, I do not have any table with that Guid. By the way, the exception is truncating the full guid. I tried with this one and the full one. No record matchs this guid! — fharreau, Jul 18 '17 at 13:58
@PanagiotisKanavos I have an error while saving changes with entity framework. I show you all the code relative to entity framework. What can I show you more? I even show you the SQL schema and the SQL logs. I know how SO works, thank you. I recognize I do not explain well on what object the insert failed, but I tried to fix. Have you even try to understand my issue before thinking I am not knowing what I am doing? — fharreau, Jul 18 '17 at 14:07
@mjwills I show you my code. There is really just that. I could share my true business names but what would it changes? I did not simplify the SQL Script, I did not simplify the c# code. All the constraints are here. @DavidG said `Well you need to debug this yourself`. It has been 2 days I am strugguling with this issue. I even explore the DbSet local cache to check if it does not create a duplicate of the FooPart causing the trouble. EF just execute the same insert twice. — fharreau, Jul 18 '17 at 14:12
@fharreau no, you didn't post code that reproduces the issue. Just snippets. How can *anyone* guess what's wrong? EF works. People would have noticed if it didn't many years ago. — Panagiotis Kanavos, Jul 18 '17 at 14:14
@fharreau for example, what are the *inputs*? You are using the *same* GUID in all pars. What if the *names* are duplicate too? That would be *very* easy to spot if you actually posted the INSERT statements — Panagiotis Kanavos, Jul 18 '17 at 14:15
@PanagiotisKanavos I don't show you snippets. I really show you all my code (forget to add the new DbContext at the very start, just fixed), but that's all. I don't show you the inputs because 2435 elements won't be very relevant, but I assert in the question that there is no duplicate in it and not in the database neither. — fharreau, Jul 18 '17 at 14:19
`The exception always occurs on the same FooVersionPart (dim.proran.db.tmp.dataCallistHDay). It is the 1910th elements of 2435. ` Please show us that line, and the 10 lines before it and the 10 lines after it. — mjwills, Jul 18 '17 at 14:25
@fharreau where are the `sourceParts`? What do they contain? I bet the same name is used twice. Where are the **queries**? You didn't post any INSERT query, just the words `INSERT INTO FooVersionPart `. That doesn't help unless you want to say that `sourceParts` contains fewer items than INSERT statements. — Panagiotis Kanavos, Jul 18 '17 at 14:27
Your primary key doesn't allow multiple parts with the same name to be assigned to a version, is that what you want here? — DavidG, Jul 18 '17 at 14:29
@fharreau you can find the duplicate `sourcePart` objects with `sourceParts.GroupBy(p=>p.Name).Where(g=>g.Count()>1).ToArray()` — Panagiotis Kanavos, Jul 18 '17 at 14:30
@PanagiotisKanavos The `sourceParts` are the results of a huge and complex computing that has nothing to do with my problem. But I assert in the question (and also in the comments) that there is **NOT ANY** duplicate in it (i already use this linq query at least 15th times before posting here). As I said, the queries I showed you are the SQL generated by the SaveChanges methods. I do not show the 1910 inserts because it is not relevant but I told you it failed at the 1910th elements of 2435. — fharreau, Jul 18 '17 at 14:36
I would bet good money that your source data has a duplicate value in it, maybe you don't realise it though, but it may be that one entry has a trailing space that lets it past your checks but not the database check? Who knows. Either way, the code you show can only throw the exception you show *if you have duplicates in your source*. — DavidG, Jul 18 '17 at 14:39
@fharreau you keep repeating unhelpful information. Only you can check those 2435 objects, so why do you keep repeating that the 1910th element failed? 2435 is a *tiny* number anyway. It's easy to run that `GroupBy` call. In fact, since you already know the faulting name, you could just look for any element with the same name. Repeating that there's nothing wrong with your code won't help you find the problem. Asking people to debug *your* code *blind*, isn't going to help either — Panagiotis Kanavos, Jul 18 '17 at 14:40
@PanagiotisKanavos Do you read my comments entirely? I **DO** run the group by before comming here! And I never said there was nothing wrong with my code ... I just said that I can't show more of it because there not more of it. — fharreau, Jul 18 '17 at 14:42
@DavidG Yes that is what I want. Two different versions can have a part with the same name (and it happens very often) but one version can only have one part with the same name. And it is this constraint that cause my issue. Because EF is trying to insert twice one of the part. So MySql throw the query away. And as I said, I checked for duplicates before coming here. — fharreau, Jul 18 '17 at 14:46
So what is the content of `sourceParts` when the exception is thrown here? — DavidG, Jul 18 '17 at 14:48
@fharreau I suggest you read [What should I do if no one answers my question?](https://stackoverflow.com/help/no-one-answers) and [How do I ask a good question?](https://stackoverflow.com/help/how-to-ask) — Panagiotis Kanavos, Jul 18 '17 at 14:48
@DavidG you were right, there are some trailing spaces into my inputs. I did not check for it because I didn't think EF or MySQL (or the MySQL implementation for EF?) would deal with them without asking... Thank you for your constructive help! — fharreau, Jul 18 '17 at 15:17
Perhaps next time don't be so quick to assume you don't have duplicates when you have multiple people suggesting you have! :) — DavidG, Jul 18 '17 at 15:19
There is different ways to suggest things. But in SO, a lot of people start by being condescending. — fharreau, Jul 18 '17 at 15:43

score 1 · Accepted Answer · answered Jul 18 '17 at 15:35

The exception message (Duplicate entry 'dim.proran.db.tmp.dataCallistHDay -9e6620f4-227d-44de-b781-5fd67' for key 'PRIMARY') combined with the primary key for the table it refers to (PRIMARY KEY ('Name', 'RefFooVersionGuid')) tells us that you are attempting to insert duplicate data into the table, specifically multiple FooVersionParts with the same name.

Now you say you have done a duplicate check on your source data, but what you may not know is that many (all?) SQL database don't count trailing spaces as part of the record. For example this query will actually return a record:

SELECT 1 
WHERE 'abc' = 'abc        '

So, as you confirmed, your data does have duplicate(s) that won't be spotted by a C# GroupBy but will be caught by the database engine. An easy solution is to trim the data before you group it, a good habit to get into, particularly with data inputted manually.

As you said, got caught by MySql ignoring trailing spaces... — fharreau, Jul 18 '17 at 15:39

Entity Framework inserts the same entity twice

1 Answers1