I found two identical entities in the database with the same Primary Key

Question

I'm using Entity Framework 6 with Code First on an SQL server. Today, with my greatest surprise, I received an exception Sequence contains more than one element on a a simple query by ID (in my domain, the primary Key of each object). After debugging, I found that in my database 2 identical entities with the same Primary Key exist.

Now, I have no idea how that could happen, but my biggest problem right now is how to solve the issue: I cannot just delete them both, since they are 2 users with important data associated to them. So I tried to remove just one, but I receive an exception due to the fact that some other object references this user (and again, I cannot delete those objects because they contain important data).

var users = _userService.GetAllBy().ToList();
var duplicatedUsers = users.Where(x => users.Count(y => y.Id == x.Id) > 1);

foreach (var user in duplicatedUsers)
{
    try
    {
        dbContext.Users.Remove(user);
        dbContext.SaveChanges();
    }
    catch (Exception e)
    {
        // it always enters here because of the foreign keys
    }
 }

Basically, since the 2 identical objects have the same foreign key, they also share the same relationships with the other related entities. Therefore, I cannot just simply delete one of them because that causes an exception. But I don't want to delete them both either, because that would cause data loss. Any suggestion?

Which database? sql server? oracle? And solving it as a one-off directly in the db possible? or are you looking to accomplish this in code using EF? And also what is the full definition of the table? — user1327961, Mar 30 '17 at 18:45
that said, if sql server, see http://stackoverflow.com/questions/16195838/how-do-i-delete-duplicate-rows-in-sql-server-using-the-over-clause — user1327961, Mar 30 '17 at 18:53
Database is sql server. There is no EF solution? Running a sql query is the only way? — tocqueville, Mar 31 '17 at 10:00
http://stackoverflow.com/questions/4502982/using-linq-to-find-delete-duplicates — user1327961, Mar 31 '17 at 14:04

score 0 · Answer 1 · edited May 23 '17 at 12:02

If sql server you can use a window function to identify a row and delete it that way -- see How do I delete duplicate rows in SQL Server using the OVER clause? as an example. Alternatively, if the table has more columns that what is defined in the key, you can hopefully use the other columns to more uniquely identify the "duplicate" row.

If it is, say, Oracle, you can get the ROWID of the row (just do a select rowid, t.* from table_name_here t WHERE conditions_here) and then when you find the right rowid, just do a straight DELETE FROM table_name WHERE rowid = XYZ123

I found two identical entities in the database with the same Primary Key

1 Answers1