What's a Linq query that will get each objX where objX.created = {newest}?

Question

This one's turning out to be a brain teaser for me. I almost hate to ask for help for fear I might miss out on the endless, sleepless night trying to cypher this mystery. JK

I've got a C# project where I need to display a list of unique objects, but only the newest one based on the type of object. For discussion purposes, let's talk "fruit". Suppose I have a list of fruits, each with a "picked date". There is no primary key. So, my generic list of type "Fruit" might look like...

{'Apple','1/2/2010'}
{'Apple','11/12/2009'}
{'Apple','2/14/2010'}
{'Grape','5/2/2009'}
{'Orange','10/30/2009'}
{'Mango','2/13/2010'}
{'Apple','6/30/2009'}
{'Orange','10/5/2009'}
{'Grape','2/1/2010'}

I need to be able to cut down that list to only the newest of each type of fruit. The results should be...

{'Apple','2/14/2010'}
{'Orange','10/30/2009'}
{'Mango','2/13/2010'}
{'Grape','2/1/2010'}

In my real world situation, I'm using Linq to SQL. So, I'd like to stay in-bounds with what I've been doing.

This is probably something SO simple and, later on, I'm going to be embarassed I even asked. But, I need to know, so I guess I'll just have to make the sacrifice.

Answer 1

I'm not really familiar with the Linq to SQL syntax, but in SQL you would use a distinct select, combined with a Order on the creation-date.

Answer 2

You want to perform a grouping on the key (in this case, Fruit), and then order the group on the values (in this case, Created), selecting the first item from each group, ordered, like so:

from each f in FruitCreatedDates
group f by f.Created into g
select new 
{ 
    Fruit = g.Key, 
    Newest = g.OrderedByDescending(fr => fr.Created).First() 
};

Answer 3

it looks like you need a groupby and a max(date). I'm thinking purely in an SQL way, but I'm sure you'd be able to figure out the Linq for that.

Answer 4

Something like this should do it:

var query = from item in db.Items
            group item by item.Fruit into grouped
            select grouped.OrderByDescending(x => x.Date)
                          .First();

In other words, group by fruit name, and for each group order by date and take the first result.

Answer 5

Use the Min method, providing a selector lambda like this:

var list = new List<Fruit>() { 
    new Fruit() {Name = "Apple", Created = new DateTime(2010, 1, 2)},
    new Fruit() {Name = "Apple", Created = new DateTime(2010, 2, 2)},
    new Fruit() {Name = "Apple", Created = new DateTime(2010, 3, 2)},
    new Fruit() {Name = "Grape", Created = new DateTime(2011, 4, 2)},
    new Fruit() {Name = "Grape", Created = new DateTime(2011, 5, 2)},
    new Fruit() {Name = "Grape", Created = new DateTime(2011, 6, 2)},
};

var query =
        from fruit in list
        group fruit by fruit.Name into grouped
        select grouped.Min(f => f.Created);

Answer 6

If I was you, I'd create a custom object to encapsulate this data. This way you can run LINQ queries to your heart's content.

public struct Fruit
{
    public string Name;
    public string FreshDate;
}

Then I would run the query like this:

List<Fruit> fruitArray = new List<Fruit>
{
new Fruit { Name = "Apple", FreshDate = "1/2/2010"},
new Fruit { Name = "Apple", FreshDate = "11/12/2009"},
new Fruit { Name = "Apple", FreshDate = "2/14/2010"},
new Fruit { Name = "Grape", FreshDate = "5/2/2009"},
new Fruit { Name = "Orange", FreshDate = "10/30/2009"},
new Fruit { Name = "Mango", FreshDate = "2/13/2010"},
new Fruit { Name = "Apple", FreshDate = "6/30/2009"},
new Fruit { Name = "Orange", FreshDate = "10/5/2009"},
new Fruit { Name = "Grape", FreshDate = "2/1/2010"}
};

var resultArray = fruitArray.GroupBy(f => f.Name).Select(g => g.OrderBy(f => DateTime.Parse(f.FreshDate)).Last());

You shouldn't run the query on a multi-dimensional string array. Easier to read on a custom object.