distinct list LINQ Query

Question

Trying to return a distinct list of SubString in LINQ.

This is returning the list but there are 100 records with the substring. I only want to return the distinct list.

var query = from b in db.Certificates
            select b.CertificateNumber.Substring(0,4);

ViewBag.BoxNumber = new SelectList(query.ToList());

I tried adding the distinct as in

select b.CertificateNumber.Substring(0,4).Distinct();

But it threw

DbDistinctExpression requires a collection argument.

Grant Winney · Accepted Answer · 2015-01-15T01:01:37.933

6

The way you're currently doing it, it's going to attempt to do a Distinct on the first four characters of each certificate number (stripping out duplicate characters), then return all of the results. You're probably getting the error because the driver you're using is unable to create a valid SQL query that way.

Instead, surround the entire first part of the query in parentheses before calling Distinct, like this:

var query = (from b in db.Certificates
             select b.CertificateNumber.Substring(0,4)).Distinct();

Alternatively, using method syntax instead of a mixture of query and method syntax:

var query = db.Certificates
              .Select(cer => cer.CertificateNumber.Substring(0,4))
              .Distinct();

edited Jan 15 '15 at 01:01

answered Jan 15 '15 at 00:54

Grant Winney

65,241
13
115
165

How would I order by the resulting list? – Doug Farrell Jan 15 '15 at 02:11
Thank You! Gonna ask a new question on updating using that value, don't want to do it here, might get yelled at. – Doug Farrell Jan 15 '15 at 02:24
How would I add a where clause to this where Active='Y' – Doug Farrell Jan 15 '15 at 05:01
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/68851/discussion-between-doug-farrell-and-grant-winney). – Doug Farrell Jan 15 '15 at 05:10

score 1 · Answer 2 · answered Jan 15 '15 at 00:55

1

I got it

ViewBag.BoxNumber = new SelectList(query.ToList().Distinct());

answered Jan 15 '15 at 00:55

Doug Farrell

135
1
3
20

http://stackoverflow.com/questions/17401772/iqueryable-distinct-vs-listt-distinct look at this comparison and take my advice running distinct, then tolist – Razvan Dumitru Jan 15 '15 at 00:58
I don't think you got it. If you had got it, you would've chosen Grant's answer as the right answer. Because, his answer does the same thing in a nicer way. – ataravati Jan 15 '15 at 01:08

score 1 · Answer 3 · answered Jan 15 '15 at 00:56

1

 var certificatesList = (from b in db.Certificates
                    select b.CertificateNumber.Substring(0,4)).Distinct().ToList();

answered Jan 15 '15 at 00:56

Razvan Dumitru

11,815
5
34
54

distinct list LINQ Query

3 Answers3