How can I do SELECT UNIQUE with LINQ?

Question

I have a list like this:

Red
Red
Brown
Yellow
Green
Green
Brown
Red
Orange

I am trying to do a SELECT UNIQUE with LINQ, i.e. I want

Red
Brown
Yellow
Green
Orange

var uniqueColors = from dbo in database.MainTable
                   where dbo.Property == true
                   select dbo.Color.Name;

I then changed this to

var uniqueColors = from dbo in database.MainTable
                   where dbo.Property == true
                   select dbo.Color.Name.Distinct();

with no success. The first select gets ALL the colors, so how do I modify it to only get the unique values?

If there is a better way of structuring this query, more than happy to go that route.

How do I go about editing it so I can have .OrderBy( "column name" ) i.e. alphabetically by color name, so name property?

I keep getting a message:

The type arguments cannot be inferred from the usage. Try specifying the type arguments explicitly.

score 174 · Accepted Answer · edited Sep 02 '20 at 23:35

174

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);

edited Sep 02 '20 at 23:35

Neuron

5,141
5
38
59

answered Aug 19 '10 at 11:11

James Curran

101,701
37
181
258

3

Thanks, this is the correct answer. Can someone please explain what goes in the .OrderBy() parameters. You have name=>name. Is that name coming from the column name in the DB? Because we have `dbo.Color.Name` then just `name=>name` which hints to me it is not the column name ? Bizarre it also sorts properly if I just change that to `.OrderBy(a=>a)` – baron Aug 19 '10 at 23:29
4

The name of the variable is irrelevant. It's just {object passed into function} => {object used to sort}. Since we have already done the Select, the only thing in the collection being sorted is the names. – James Curran Aug 20 '10 at 11:35
Seems like it would be nice if there was an OrderedBy() for ordering by the entire object/record. An extension method would still be calling a delegate for no reason. – NetMage Dec 21 '16 at 19:41
1

@NetMage - clarify what you mean be "the entire object/record". Every field? In what order? Including the primary key? – James Curran Dec 22 '16 at 07:09

score 21 · Answer 2 · edited Jan 07 '13 at 14:12

21

var uniqueColors = (from dbo in database.MainTable 
                    where dbo.Property == true
                    select dbo.Color.Name).Distinct();

edited Jan 07 '13 at 14:12

answered Aug 19 '10 at 06:12

jwendl

942
7
13

score 10 · Answer 3 · answered Aug 19 '10 at 06:42

10

Using query comprehension syntax you could achieve the orderby as follows:

var uniqueColors = (from dbo in database.MainTable
                    where dbo.Property
                    orderby dbo.Color.Name ascending
                    select dbo.Color.Name).Distinct();

answered Aug 19 '10 at 06:42

cordialgerm

8,403
5
31
47

Hmm... that didn't achieve an alphabetic sort - for some reason... I switched ascending and descending and got the same result. Is the distinct statement affecting it? maybe it needs to be orderedby after that ? – baron Aug 19 '10 at 07:06
Could try var uniqueColors = from result in (from dbo in database.MainTable where dbo.Property select dbo.Color.Name).Distinct() orderby result ascending; – cordialgerm Aug 19 '10 at 07:17

How can I do SELECT UNIQUE with LINQ?

3 Answers3

Linked