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();
Add a comment
|
3 Answers
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();
1 Comment
themefield
Not necessarily to be anonymous object, it can also be a model/entity defined in the application.
You can also try
db.Table
.OrderBy(m=>m.Name)
.DistinctBy(m=> new{m.SerialNumber, m.Manufacturer})
.ToList();
3 Comments
Carl Prothman
public static IEnumerable<T> DistinctBy<T, TKey>(this IEnumerable<T> items, Func<T, TKey> property) { return items.GroupBy(property).Select(x => x.First()); }
ChaseHardin
This did the trick, thanks for the assist! Didn't realize that there was a
DistinctBy
Ozan Yasin Dogan
public static IEnumerable<T> DistinctBy<T, TKey>(this IEnumerable<T> items, Func<T, TKey> property) { return items.GroupBy(property).Select(x => x.First()); }
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();
