Question 87268 in ASP.NET Question posted in
The official documentation can be found here.

Remove any records with the same IDs (not removing only the duplicates) – Asp.net

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

Tags:

2

Answers


  1. 0

    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://learn.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://learn.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();
    Login or Signup to reply.
  2. 0

    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
)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search