Filtering for duplicate rows

Question

I have a piece of code here:

var grouped = nonGrouped.GroupBy(x => new
{
    x.Id,
    x.Col1,
    x.Col2
}).Select(x => new MyDbTable
{
    Id = x.Key.Id,
    VALUE = x.Sum(y => y.Value),
    Col1 = x.Key.Col1,
    Col2 = x.Key.Col2
}).ToList();

//Filter out rows with the same Col1/Col2 combination
var dbTableList = new List<MyDbTable>();
grouped.ForEach(x =>
{
    if (!dbTableList.Any(a => a.Col1 == x.Col2 && a.Col2 == x.Col1))
    {
        dbTableList.Add(x);
    }
});

I would like to remove the code under the comment "//Filter out rows with the same Col1/Col2 combination" and somehow add this functionality to my LINQ statement above the comment

Possible duplicate of [Remove duplicates in the list using linq](https://stackoverflow.com/questions/1606679/remove-duplicates-in-the-list-using-linq) — SᴇM, Jun 08 '18 at 10:57
Why not just call `Distinct()` before `ToList()`? It should work especially with the custom fields returned in `Select()`. — ViRuSTriNiTy, Jun 08 '18 at 10:58
Do you need `Id`? Because your logic actually removes rows which are not duplicate because they have different `Id`. If you don't need `Id`, you can completely remove it, and you won't have duplicates anymore — Darjan Bogdan, Jun 08 '18 at 10:58
Do you _really_ only want to add the 1st id (and its _sum_) for each col combo, if so you can group by the cols only, Distinct(), and take the 1st ID and the Sum for that id. — tolanj, Jun 08 '18 at 11:01

score 0 · Answer 1 · answered Jun 08 '18 at 11:11

Can't you just do this?

var grouped = nonGrouped.GroupBy(x => new
            {
                x.Id,
                x.Col1,
                x.Col2
            }).Select(x => new MyDbTable
            {
                Id = x.Key.Id,
                VALUE = x.Sum(y => y.Value),
                Col1 = x.Key.Col1,
                Col2 = x.Key.Col2
            }).Where(z => z.Col1 != z.Col2).ToList();

or did I misunderstand your question entirely? It has happened before :)

score 0 · Accepted Answer · answered Jun 08 '18 at 11:45

I think you are looking for custom distinct values of grouped List

class MyDbTableComparer : IEqualityComparer<MyDbTable>
{
    public bool Equals(MyDbTable x, MyDbTable y)
    {
        if (x.Col1 == y.Col2 && x.Col2 == y.Col1) return true;
        return false;
    }
}

and after that, change upper statement to this:

.Select(x => new MyDbTable
{
    Id = x.Key.Id,
    VALUE = x.Sum(y => y.Value),
    Col1 = x.Key.Col1,
    Col2 = x.Key.Col2
}).ToList().Distinct(new MyDbTableComparer());

but I don't know whether it would work fine if you use Distinct() before ToList()

brijber · Answer 3 · 2021-12-16T07:09:24.943

0

nonGrouped
    .GroupBy(x => new { x.Col1, x.Col2 })
    .Where(group => !group.Take(1).Any())
    .SelectMany(group => group)
    .GroupBy(x => x.Id, x.Col1,x.Col2);

might work.

Not sure why you what the logic here is though (and why grouping by Id, Col1 and Col2 is neccesary if you want to remove all rows with equal col1 and col2 anyway).

edited Dec 16 '21 at 07:09

answered Jun 08 '18 at 11:56

brijber

711
5
17

score 0 · Answer 4 · answered Jun 08 '18 at 12:00

This should work nicely for you:

var dbTableList =
    nonGrouped
        .GroupBy(x => new
        {
            x.Id,
            x.Col1,
            x.Col2
        })
        .Select(x => new MyDbTable
        {
            Id = x.Key.Id,
            VALUE = x.Sum(y => y.Value),
            Col1 = x.Key.Col1,
            Col2 = x.Key.Col2
        })
        .GroupBy(x => new
        {
            x.Col1,
            x.Col2
        })
        .SelectMany(xs => xs.Take(1))
        .ToList();

The key to making this work is the GroupBy/SelectMany/Take(1) combo.

Filtering for duplicate rows

4 Answers4