How do I transfer this logic into a LINQ statement?

Question

I can't get this bit of logic converted into a Linq statement and it is driving me nuts. I have a list of items that have a category and a createdondate field. I want to group by the category and only return items that have the max date for their category.

So for example, the list contains items with categories 1 and 2. The first day (1/1) I post two items to both categories 1 and 2. The second day (1/2) I post three items to category 1. The list should return the second day postings to category 1 and the first day postings to category 2.

Right now I have it grouping by the category then running through a foreach loop to compare each item in the group with the max date of the group, if the date is less than the max date it removes the item.

There's got to be a way to take the loop out, but I haven't figured it out!

Answer 1

You can do something like that :

    from item in list
    group item by item.Category into g
    select g.OrderByDescending(it => it.CreationDate).First();

However, it's not very efficient, because it needs to sort the items of each group, which is more complex than necessary (you don't actually need to sort, you just need to scan the list once). So I created this extension method to find the item with the max value of a property (or function) :

    public static T WithMax<T, TValue>(this IEnumerable<T> source, Func<T, TValue> selector)
    {
        var max = default(TValue);
        var withMax = default(T);
        var comparer = Comparer<TValue>.Default;
        bool first = true;
        foreach (var item in source)
        {
            var value = selector(item);
            int compare = comparer.Compare(value, max);

            if (compare > 0 || first)
            {
                max = value;
                withMax = item;
            }
            first = false;
        }
        return withMax;
    }

You can use it as follows :

    from item in list
    group item by item.Category into g
    select g.WithMax(it => it.CreationDate);

---

UPDATE : As Anthony noted in his comment, this code doesn't exactly answer the question... if you want all items which date is the maximum of their category, you can do something like that :

    from item in list
    group item by item.Category into g
    let maxDate = g.Max(it => it.CreationDate)
    select new
    {
        Category = g.Key,
        Items = g.Where(it => it.CreationDate == maxDate)
    };

Answer 2

How about this:

    private class Test
    {
        public string Category { get; set; }
        public DateTime PostDate { get; set; }
        public string Post { get; set; }
    }
    private void Form1_Load(object sender, EventArgs e)
    {
        List<Test> test = new List<Test>();
        test.Add(new Test() { Category = "A", PostDate = new DateTime(2010, 5, 5, 12, 0, 0), Post = "A1" });
        test.Add(new Test() { Category = "B", PostDate = new DateTime(2010, 5, 5, 13, 0, 0), Post = "B1" });

        test.Add(new Test() { Category = "A", PostDate = new DateTime(2010, 5, 6, 12, 0, 0), Post = "A2" });
        test.Add(new Test() { Category = "A", PostDate = new DateTime(2010, 5, 6, 13, 0, 0), Post = "A3" });
        test.Add(new Test() { Category = "A", PostDate = new DateTime(2010, 5, 6, 14, 0, 0), Post = "A4" });

        var q = test.GroupBy(t => t.Category).Select(g => new { grp = g, max = g.Max(t2 => t2.PostDate).Date }).SelectMany(x => x.grp.Where(t => t.PostDate >= x.max));
    }

Answer 3

Reformatting luc's excellent answer to query comprehension form. I like this better for this kind of query because the scoping rules let me write more concisely.

  from item in source
  group item by item.Category into g
  let max = g.Max(item2 => item2.PostDate).Date
  from item3 in g
  where item3.PostDate.Date == max
  select item3;