Using .Distinct() on certain rows

Question

I have a Linq query which returns all of the data stored in a table based on a where clause as a list:

List<Catalogue> data = context.Catalogue.Where(x=>x.ManID == id).ToList();

This returns 23 items, but some of those items have some columns which contains duplicate data, I'll call them ColumnA, ColumnB and ColumnD. I've tried:

List<Catalogue> data = context.Catalogue.Where(x=>x.ManID == id)
                .Distinct().ToList();

But this just returns the same 23 rows. What I'd like is if I could specify the columns I want to have distinct values, something like:

List<Catalogue> data = context.Catalogue.Where(x=>x.ManID == id)
                .Distinct(x=> new { x.ColumnA, x.ColumnB, x.ColumnD }).ToList();

Is this possible or should I look for a new way of doing this?

Mark Byers · Accepted Answer · 2012-03-19 12:50:35Z

7

Try combining GroupBy and First:

List<Catalogue> data = context.Catalogue
    .Where(x => x.ManID == id)
    .GroupBy(x => new { x.ColumnA, x.ColumnB, x.ColumnD })
    .Select(g => g.First())
    .ToList();

answered Mar 19, 2012 at 12:50

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CallumVass Over a year ago

Just an adjustment to this: I had to use FirstOrDefault() as First() threw an error, but this worked perfectly thanks! I'll accept as answer when I'm able too!

Jon Skeet · Accepted Answer · 2012-03-19 12:58:20Z

It depends on the context. If this is within something like LINQ to SQL, I'd use GroupBy:

List<Catalogue> data = context.Catalogue
                              .Where(x=>x.ManID == id)
                              .GroupBy(x=> new { x.ColumnA, 
                                                 x.ColumnB, 
                                                 x.ColumnD })
                              .Select(g => g.First())
                              .ToList();

(EDIT: Note that using First really should be fine here - you shouldn't need to use FirstOrDefault(), as each group must have at least one entry in order to even exist.)

In LINQ to Objects, I'd use MoreLINQ and its DistinctBy method:

List<Catalogue> data = context.Catalogue
                              .Where(x=>x.ManID == id)
                              .DistinctBy(x=> new { x.ColumnA, 
                                                    x.ColumnB, 
                                                    x.ColumnD })
                              .ToList();

Dummy01 · Accepted Answer · 2012-03-19 12:55:24Z

0

I would create an IEqualityComparer for the 3 properties that can be used with Distinct().

IEqualityComparer

answered Mar 19, 2012 at 12:55

Dummy01

1,9951 gold badge16 silver badges21 bronze badges

Collectives™ on Stack Overflow

Using .Distinct() on certain rows

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related