Removing duplicate rows in database with primary key using Distinct()

Question

I have some duplicate values in my database so I am using Linq to Entity to remove them with the code below. The problem is that there is an autonumber primary key in RosterSummaryData_Subject_Local, which invalidates the line var distinctRows = allRows.Distinct(); So, even if all the rows are the same, distinct won't work because the pk is different. Is there anyway to discredit the pk in the distinct? Or anyway to remove it from the query so it becomes a non issue. Just to note I want the query to return an IQueryable of my entity type so I can use the RemoveRange() method on the enttiy to remove the duplicates.

var allRows = (from subjLocal in customerContext.RosterSummaryData_Subject_Local
                           select subjLocal);
var distinctRows = allRows.Distinct();

if (allRows.Count() == distinctRows.Count())
{
     return;
}
else
{
     var rowsToDelete = allRows.Where(a => a != distinctRows);
     customerContext.RosterSummaryData_Subject_Local.RemoveRange(rowsToDelete);
}

EDIT

I realized that to properly bring back distinct rows, all I have to do is select all the items except primary key:

var distinctRows = allRows
                   .Select(a => new {a.fkRosterSetID, a.fkTestInstanceID, a.fkTestTypeID, 
                                      a.fkSchoolYearID, a.fkRosterTypeID, a.fkDistrictID, 
                                      a.fkSchoolID, a.fkGradeID, a.fkDepartmentID, 
                                      a.fkCourseID, a.fkPeriodID, a.fkDemoCommonCodeID, 
                                      a.fkDemoCommonCategoryID, a.fkTest_SubjectID})
                   .Distinct();

The problem is that I cannot fetch the duplicate rows with the code below because the ! operator does not work with anonymous types(the variable distinctRows is an anonymous type because I didn't select all the columns):

var rowsToDelete = allRows.Where(a => a != distinctRows);

Any help?

Any reason you need to write this in LINQ? Very straightforward to do in straight SQL. — dbugger, Nov 24 '14 at 02:17
@dbugger well this procress need to occur multiple times in my program. Would I just use a bit of generic ADO.NET to accomplish it? — frontin, Nov 24 '14 at 02:28
Yes. SQL string. cmd.Execute, go about your day. No need to fight EF. — dbugger, Nov 24 '14 at 02:30

score 0 · Answer 1 · answered Nov 24 '14 at 00:53

0

maybe you need to check for each one of the fields in your customerContext.RosterSummaryData_Subject_Local to see which one is different

answered Nov 24 '14 at 00:53

Jack1987

727
1
14
26

score 0 · Accepted Answer · edited May 23 '17 at 12:27

you can try this:

var allRows = (from subjLocal in customerContext.RosterSummaryData_Subject_Local
                       select subjLocal).ToList();

var distinctRows = allRows.Distinct().ToList();

Since you will be dealing with list objects, then in your original else statement you can do this:

else
{
     var rowsToDelete = allRows.Where(a => !distinctRows.Contains(a));
     customerContext.RosterSummaryData_Subject_Local.RemoveRange(rowsToDelete);
}

To handle your issue with Distinct() and the autonumberID in the database, there are two solutions I can think of.

One is you can bring in the MoreLinq library, it's a Nuget package. then you can use the MoreLinq method DistinctBy():

allRows.DistinctBy(a => a.SomePropertyToUse);

Or the other route would be to use an IEqualityComparer with the regular .Distinct() Linq Method. You can check out this SO question for more info on using an IEqualityComparer in the .Distinct() method. using distinct with IEqualityComparer

Removing duplicate rows in database with primary key using Distinct()

2 Answers2