Linq to SQL order by with Distinct

问题:

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

I have been through many similar SO articles trying to work this out. I am amateur with Linq to SQL queries and c# in general.

I'm trying to use Linq to SQL to get the top 5 most recent distinct values from a column, then add them to a list. My application is asp.net using c# and a .dbml file for data abstraction.

I've tried it many different ways. I either get non-distinct yet sorted list, or I get a distinct unsorted list. What I have so far is 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'm thinking my "Distinct" is looking at the "ID" column, and perhaps I need to tell it I want it to look at the "manufacturer" column, but I haven't worked out how / if it's possible to do that.

I could do this with ease by using a storedproc, but I'm really trying to do it with c# code directly if possible. This is my first post to SO, I hope I have put it together properly. Any help much appreciated.

Thanks

回答1:

I think you can use the 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);

回答2:

No the Distinct compares manufacturer and date pairs.If you want to get distinct records by manufacturer then I recommend DistinctBy method.It's in the MoreLINQ library.Since its a third library method it's not supported in linq to sql, you still can use it by fetching the records from DB and do 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);

回答3:

One way you can distinct by a certain field is to replace:

...
.Distinct()
...

with:

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