Remove any records with the same IDs (not removing only the duplicates)-CodePudding

Using C# .Net Core, I'm wanting to remove records/rows with duplicate IDs value, example:

ID    |  Value1
----------------
321   |  data_1
123   |  data_2
321   |  data_3

I want to completely remove BOTH rows with the same ID of 321, results would be:

ID    |  Value1
----------------
123   |  data_2

This is what I have:

IEnumerable<Table_Data> results = _context.Table_Datas.
  .GroupBy(s => new { s.ID })
  .Where(x => x.Count() > 1)
  .SelectMany(g => g)
  .ToList();

if (results != null) {
   _context.Table_Datas.RemoveRange(results);
}

I'm getting an error of

"InvalidOperationException: The LINQ expression 'DbSet<Table_Data>()
.GroupBy(s => new { ID = s.ID })
.Where(x => x
.AsQueryable()
.Count() > 1)
.SelectMany(g => g)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information."

Any help are greatly appreciated

CodePudding user response：

If you want to delete the records from a table which have duplicate records in the table, assuming you're using sql, you can execute this SQL command

Delete from Table_Datas where ID in (     
  Select ID from Table_Datas group by ID having count() > 1
)

CodePudding user response：

results will per definition never be null when you call .ToList(). It will be an empty List. So, the check needs to be:

if (results.Count() > 0)
{
   _context.Accounts.RemoveRange(results);
}

Then, according to MS Docs, there is no .RemoveRange that accepts only 1 parameter so please include your custom method or from which library you're using this function. As you submitted the question, this should/would not compile.

https://docs.microsoft.com/en-us/dotnet/api/system.collections.generic.list-1.removerange?view=net-6.0

That said, you want to exclude all your results from the original list. You can do that like this:

if (results.Count() > 0)
{
  _context.Table_Datas = _context.Table_Datas
    .Except(results)
    .ToList(); // assuming _context.Table_Datas is a list
}

PS: In your examples and coding, please follow C# coding conventions if your team/project permits it: https://docs.microsoft.com/en-us/dotnet/csharp/fundamentals/coding-style/coding-conventions. C# methods put the bracket on the next line.

EDIT: In response to your error message, try changing your LINQ to;

_context.Table_Datas.
  .GroupBy(s => s.ID)
  .Where(x => x.Count() > 1)
  .SelectMany(g => g)
  .ToList();