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.
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();
I got it
ViewBag.BoxNumber = new SelectList(query.ToList().Distinct());
var certificatesList = (from b in db.Certificates
select b.CertificateNumber.Substring(0,4)).Distinct().ToList();