How can I do SELECT UNIQUE with LINQ?
The Distinct()
is going to mess up the ordering, so you'll have to the sorting after that.
var uniqueColors =
(from dbo in database.MainTable
where dbo.Property == true
select dbo.Color.Name).Distinct().OrderBy(name=>name);
Distinct in Linq based on only one field of the table
Try this:
table1.GroupBy(x => x.Text).Select(x => x.FirstOrDefault());
This will group the table by Text
and use the first row from each groups resulting in rows where Text
is distinct.
Select All distinct values in a column using LINQ
To have unique Categories:
var uniqueCategories = repository.GetAllProducts()
.Select(p => p.Category)
.Distinct();
LINQ's Distinct() on a particular property
EDIT: This is now part of MoreLINQ.
What you need is a "distinct-by" effectively. I don't believe it's part of LINQ as it stands, although it's fairly easy to write:
public static IEnumerable<TSource> DistinctBy<TSource, TKey>
(this IEnumerable<TSource> source, Func<TSource, TKey> keySelector)
{
HashSet<TKey> seenKeys = new HashSet<TKey>();
foreach (TSource element in source)
{
if (seenKeys.Add(keySelector(element)))
{
yield return element;
}
}
}
So to find the distinct values using just the Id
property, you could use:
var query = people.DistinctBy(p => p.Id);
And to use multiple properties, you can use anonymous types, which implement equality appropriately:
var query = people.DistinctBy(p => new { p.Id, p.Name });
Untested, but it should work (and it now at least compiles).
It assumes the default comparer for the keys though - if you want to pass in an equality comparer, just pass it on to the HashSet
constructor.
Select distinct using linq
myList.GroupBy(test => test.id)
.Select(grp => grp.First());
Edit: as getting this IEnumerable<>
into a List<>
seems to be a mystery to many people, you can simply write:
var result = myList.GroupBy(test => test.id)
.Select(grp => grp.First())
.ToList();
But one is often better off working with the IEnumerable
rather than IList
as the Linq above is lazily evaluated: it doesn't actually do all of the work until the enumerable is iterated. When you call ToList
it actually walks the entire enumerable forcing all of the work to be done up front. (And may take a little while if your enumerable is infinitely long.)
The flipside to this advice is that each time you enumerate such an IEnumerable
the work to evaluate it has to be done afresh. So you need to decide for each case whether it is better to work with the lazily evaluated IEnumerable
or to realize it into a List
, Set
, Dictionary
or whatnot.
LINQ query to return distinct field values from list of objects
objList.Select(o=>o.typeId).Distinct()
Linq query to get the distinct values in a list
This should cover your needs:
var grouped = list.GroupBy(item => item.CategoryId);
var shortest = grouped.Select(grp => grp.OrderBy(item => item.Distance).First());
It first groups the items with the same CategoryId
, then selects the first from each group (ordered by Distance
).
Update:
You could chain all of these together too, if you prefer:
var shortest = list.GroupBy(item => item.CategoryId)
.Select(grp => grp.OrderBy(item => item.Distance)
.First());
Select unique members using LINQ
Here is one way to do it:
var result = myClubs
.SelectMany(x => x.Members)
.GroupBy(x => x.Id)
.Select(x => x.First())
.ToList();
Based on the comments, members are equal if they have the same Id
.
So, first you use SelectMany
to select all members from all clubs, then you group them by the Id
.
Now each group will just contain multiple instances of the same member (or just a single member if there are no duplicates of such member).
Then you would just select the first member of each group to get the unique members.
LINQ: Distinct values
Are you trying to be distinct by more than one field? If so, just use an anonymous type and the Distinct operator and it should be okay:
var query = doc.Elements("whatever")
.Select(element => new {
id = (int) element.Attribute("id"),
category = (int) element.Attribute("cat") })
.Distinct();
If you're trying to get a distinct set of values of a "larger" type, but only looking at some subset of properties for the distinctness aspect, you probably want DistinctBy
as implemented in MoreLINQ in DistinctBy.cs
:
public static IEnumerable<TSource> DistinctBy<TSource, TKey>(
this IEnumerable<TSource> source,
Func<TSource, TKey> keySelector,
IEqualityComparer<TKey> comparer)
{
HashSet<TKey> knownKeys = new HashSet<TKey>(comparer);
foreach (TSource element in source)
{
if (knownKeys.Add(keySelector(element)))
{
yield return element;
}
}
}
(If you pass in null
as the comparer, it will use the default comparer for the key type.)
Distinct in LINQ-C#
The 3 records that you see at debug time is your existing list. I think all that you've missed is an assignment.
List<LinqTest> myList = new List<LinqTest>();
myList.Add(new LinqTest() { id = 1, value = "a" });
myList.Add(new LinqTest() { id = 1, value = "b" });
myList.Add(new LinqTest() { id = 2, value = "c" });
// not sure why new {m.id} was used in this context
List<int> distinctList = myList.Select(m => m.id).Distinct().ToList();
Related Topics
How to Call Oracle Md5 Hash Function
SQL Update Woes in Ms Access - Operation Must Use an Updateable Query
How to Determine If a String Is Numeric in SQL
How to Pivot Dynamically with Date as Column
Combine Two Tables in Select (SQL Server 2008)
What Is the Internal Representation of Datetime in SQL Server
Is Natural (Join) Considered Harmful in Production Environment
Postgres Interval Using Value from Table
Select Distinct Is Slower Than Expected on My Table in Postgresql
SQL Server: Two-Level Group by with Xml Output
Generate Unique Random Numbers Using SQL
Convert Postgres Geometry Format to Wkt
How to Pass Table Name as a Parameter in Oracle
Fast Way to Generate Concatenated Strings in Oracle
Split String by Space and Character as Delimiter in Oracle with Regexp_Substr