Grouping troubles in LINQ

Question

I have some list of structures like this:

struct va_data
{
  public int item_id;
  public int type_id;
  public double item_value;
  public DateTime value_date;
}

I trying group the list by type_id and take items where value_date is maximum then group by item_id and take items only where item_value is minimum

There is my syntax

from x in dataList 
group x by x.type_id into grouped 
select grouped.Where(x => x.value_date == grouped.Max(y => y.value_date))
    .GroupBy(x => x.item_id) // and here i was stuck.


Example

var dataList = new []
{
   new va_data {item_id = 1, type_id = 1, item_value = 0, value_date = "2013.07.29"},
   new va_data {item_id = 1, type_id = 1, item_value = 1, value_date = "2013.07.30"},
   new va_data {item_id = 2, type_id = 1, item_value = 0, value_date = "2013.07.29"},
   new va_data {item_id = 2, type_id = 1, item_value = 1, value_date = "2013.07.29"},
   new va_data {item_id = 4, type_id = 2, item_value = 5, value_date = "2013.07.29"},
   new va_data {item_id = 4, type_id = 3, item_value = 9, value_date = "2013.07.30"},
};

The result must be 

var dataListResult = new []
{
   new va_data {item_id = 1, type_id = 1, item_value = 1, value_date = "2013.07.30"},
   new va_data {item_id = 2, type_id = 1, item_value = 0, value_date = "2013.07.29"},
   new va_data {item_id = 4, type_id = 2, item_value = 5, value_date = "2013.07.29"},
}

Answer 1

Given the following class

class va_data
{
  public int item_id;
  public int type_id;
  public double item_value;
  public DateTime value_date;
}

and your example data, you can use a query like this:

from data in dataList
group data by new {data.item_id, data.type_id} into g
let max_value_date = g.Max(x => x.value_date)
from i in g.Where(x => x.value_date == max_value_date)
group i by i.item_id into g2
let min_item_value = g2.Min(x => x.item_value)
from x in g2
where x.item_value == min_item_value
select x;

to get the following result:

Answer 2

Just split your query into two parts - getting latest of each type, and then getting minimal of each item:

var latestOfEachType = 
             from d in dataList
             group d by d.type_id into typeGroup
             select typeGroup.OrderByDescending(x => x.value_date).First();

var result = from d in latestOfEachType
             group d by d.item_id into itemGroup
             select itemGroup.OrderBy(x => x.item_value).First();

This query will be executed as single query. But in this case it looks much more readable to me. Also don't use mutable structs!. Use classes instead.

---

EDIT: Thus you have several items for max date, then query needs two small tweaks - select all items where date is max, and use SelectMany to iterate over them:

var latestOfEachType =
             from d in dataList
             group d by d.type_id into typeGroup
             let maxDate = typeGroup.Max(x => x.value_date)
             select typeGroup.Where(x => x.value_date == maxDate);

var result = from d in latestOfEachType.SelectMany(g => g)
             group d by d.item_id into itemGroup
             select itemGroup.OrderBy(x => x.item_value).First();