Clever Geek Handbook

Linq to SQL order by with Distinct

My environment: ASP.net and C # in VS 2013 Express.

I went through many similar SO articles trying to work it out. I'm a hobbyist with Linq for SQL queries and C # in general.

I am trying to use Linq to SQL to get the 5 most recent distinct values ​​from a column and then add them to a list. My asp.net application uses c # and .dbml file for data abstraction.

I've tried this in a variety of ways. I either end up with a fuzzy but sorted list, or I end up with a separate unsorted list. What I am so far below

var Top5MFG = (from mfg in db.orders 
           where mfg.manufacturer.Length > 0 && mfg.customerid == "blahblahblahblahblah"<br />
           select new { 
              manufacturer = mfg.manufacturer,
              date = mfg.date_created 
                       })
        .Distinct()
        .OrderByDescending(s => s.date);

I think my "Distinct" looks at the "ID" column, and maybe I need to say that I want it to look at the "manufacturer" column, but I haven't worked out how / if it can be done.

I could do this with ease using storeproc, but I'm really trying to do it with C # code if possible. This is my first post for SO, I hope I put it right. Any help is greatly appreciated.

thank

+3


source to share


3 answers


I think you can use GroupBy to do what you want.



  var Top5MFG = db.orders
     .Where (x => x.manufacturer.Length > 0 && x.customerid == "blahblahblahblahblah")
     .GroupBy(mfg => mfg.manufacturer)
     .Select(g => g.First())
     .OrderByDescending(d => d.date_created );
     .Take(5);
+1


source


No Distinct

compares pairs manufacturer

and date

. If you want to get different records manufacturer

then I recommend the method DistinctBy

. In MoreLINQ

. Since its third library method, it is not supported in linq for sql, you can still use it by fetching records from DB and doing the rest in memory



(from mfg in db.orders 
where mfg.manufacturer.Length > 0 && mfg.customerid == "blahblahblahblahblah"
select new { 
             manufacturer = mfg.manufacturer,
             date = mfg.date_created 
           })
 .AsEnumerable()
 .DistinctBy(x => x.manufacturer)
 .OrderByDescending(s => s.date)
 .Take(5);
+1


source


One way you can differentiate a specific field is by replacing:

...
.Distinct()
...

from:

...
.GroupBy(x => x.manufacturer )
.Select(g => g.First())
...
+1


source






More articles:


All Articles