Distinct on Multiple Columns Entity Framework LINQ

Question

What is the LINQ Equivalent of

Select DISTINCT A, B, C from TESTDB WHERE ALPHA =1

I am trying something like this:

var data = TESTDB.WHERE(i=>i.ALPHA==1).SELECT(A,B,C).DISTINCT();

score 33 · Accepted Answer · edited Sep 17 '21 at 15:27

33

Using anonymous objects will do the trick:

var data = TESTDB.Where(i => i.ALPHA == 1).Select(i => new {i.A, i.B, i.C}).Distinct();

To retain the model:

List<Book> books = db.Book.Select(i => new Book {Author = i.Author, Title = i.Title}).Distinct().ToList();

edited Sep 17 '21 at 15:27

Moe Rin

15
1
5

answered Aug 06 '12 at 22:32

Risky Martin

2,491
2
15
16

1

Not necessarily to be anonymous object, it can also be a model/entity defined in the application. – themefield Oct 26 '20 at 03:50

score 14 · Answer 2 · edited Dec 13 '18 at 11:31

14

You can also try

db.Table
  .OrderBy(m=>m.Name)
  .DistinctBy(m=> new{m.SerialNumber, m.Manufacturer})
  .ToList();

edited Dec 13 '18 at 11:31

Majid Basirati

2,665
3
24
46

answered Jun 13 '14 at 23:29

sherebry

184
1
3

1

public static IEnumerable DistinctBy(this IEnumerable items, Func property) { return items.GroupBy(property).Select(x => x.First()); } – Carl Prothman May 08 '15 at 17:47
This did the trick, thanks for the assist! Didn't realize that there was a `DistinctBy` – ChaseHardin Feb 22 '16 at 16:29
public static IEnumerable DistinctBy(this IEnumerable items, Func property) { return items.GroupBy(property).Select(x => x.First()); } – Ozan Yasin Dogan Dec 24 '20 at 13:22

wlabaj · Answer 3 · 2012-08-06T21:55:34.793

If you use it like that:

var list = new List<Pet>()
                       {
                           new Cat() {Name = "Kitty", Id = 1},
                           new Cat() {Name = "Kitty", Id = 1},
                           new Cat() {Name = "Kitty", Id = 1}
                       };

var distinctCount = list.Where(i => i.Id == 1).Distinct().Count();

it turns out that distinctCount equals 3. Why is that? Seems that by default Distinct distinguishes between instances (even though all properties have the same values they're three instances).

You should implement custom comparer, here you'll find the code example: http://msdn.microsoft.com/en-us/library/bb338049.aspx.

Yet I'm not sure why do you want to select three properties (A,B,C). You can access single property in this way:

var data = list.Where(i => i.Id == 1).Distinct().SelectMany(i => i.Name);

However in order to select multiple properties you should cast the whole object to some class containing those properties:

var data = list.Where(i => i.Id == 1).Cast<Pet>().Distinct().ToList();

Distinct on Multiple Columns Entity Framework LINQ

3 Answers3