CODEWITHSUNDEEP
c#.netlinq
user1238321
user1238321

Reputation: 249

order linq query by descending

I cannot work out how to get the following query to be distinct by LatLong (using the most recent entry):

var query = from ent in db.Entrants
            select new { 
                  ent.idx, 
                  ent.LatLong, 
                  ent.Tip, 
                  ent.FirstName, 
                  ent.City, 
                  ent.Fragrance };

Thanks in advance for any help.

Upvotes: 1

Views: 1002

Answers (6)

Bob Vale
Bob Vale

Reputation: 18474

Sounds like you need to use a grouping

from ent in db.Entrants
     group ent by ent.LatLong into grp
     let b = grp.OrderByDescending(x=>x.idx).First();
      select new { 
                  b.idx, 
                  b.LatLong, 
                  b.Tip, 
                  b.FirstName, 
                  b.City, 
                  b.Fragrance 
                };

Upvotes: 0

Roy Dictus
Roy Dictus

Reputation: 33139

Add this:

var distinct = query.DistinctBy(x => x.LatLong); // requires MoreLinq

or

var sorted = query.OrderBy(x => x.LatLong);

or combine them:

var result = query.DistinctBy(x => x.LatLong).OrderBy(x => x.LatLong);

Upvotes: 0

Rawling
Rawling

Reputation: 50114

This line

var groups = db.Entrants.GroupBy(ent => ent.LatLong);

will give you an enumeration of groups, each of which contains all the entries for a given LatLong. Now, I've got no idea how you tell which entry is most recent, but if you know they're towards the end of db.Entrants then you can use

var mostRecentPerLatLong = groups.Select(g => g.Last());

or if you know they're at the start you can use

var mostRecentPerLatLong = groups.Select(g => g.First());

or maybe it's by idx so you can use

var mostRecentPerLatLong =
    groups.Select(g => g.OrderByDescending(e => e.idx).First());

and then you can do your object creation:

var query = mostRecentPerLatLong.Select(ent => new { ... } );

Upvotes: 0

Doctor Jones
Doctor Jones

Reputation: 21654

This should do it:

var query = from ent in db.Entrants
            //get the most recent entry first
            orderby ent.idx descending
            //group the entries by LatLong so we can get a distinct entry for each latlong
            group ent by ent.LatLong into grouped
            //get the most recent one
            let ent = grouped.First()
            select new 
            { 
                ent.idx, 
                ent.LatLong, 
                ent.Tip, 
                ent.FirstName, 
                ent.City, 
                ent.Fragrance 
            };

I wasn't sure which column you wanted to order by to get the most recent entry, so I ordered by idx descending, but you can change that to whatever column you like.

Upvotes: 2

Jan P.
Jan P.

Reputation: 3297

Your problem is the anonymous type.

Because the Equals and GetHashCode methods on anonymous types are defined in terms of the Equals and GetHashcode methods of the properties, two instances of the same anonymous type are equal only if all their properties are equal. MSDN

Define a proper type with a proper Equals method for this, because the Distinct calls the standard Equals of the nested type.

I've never seen an overload of distinct which receives a lambda.

Upvotes: 0

codingbiz
codingbiz

Reputation: 26386

Try

var retVal = query.OrderByDescending(x => x.LatLong).Distinct(x => x.LatLong);

Upvotes: 0

Related Questions