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"},
}

Can you show an example of the expected outcome? (And what input.) — Mario S, Jul 29 '13 at 09:38
It looks like you're almost there. Looks like you just have to `Select` the items `Where` the item_value is equal to the minimum of the group. One thing that's likely confusing you is that you're switching from two different notations for linq. (From expanded notation to Dot notation.(It's personal preference, I prefer Dot)) One thing with linq, is since everything can 'chain' you can save the results into a variable to 'start over' if it makes it easier to understand. Good Luck! — Bob2Chiv, Jul 29 '13 at 10:19
thats the point. I can't simple select item where item_value is minimum becaus my item is still a group of items so I'm confused. — Miles, Jul 29 '13 at 10:29
Your sample does not fit your description: group the list by type_id and take items where value_date is maximum This filters out every item with type_id == 1 except the one where value_date == "2013.07.30" ("2013.07.30" is the maximum), but in your sample you say you want two items with type_id == 1, one with `value_date = "2013.07.30"` and one with `value_date = "2013.07.29"` — sloth, Jul 29 '13 at 13:16

sloth · Accepted Answer · 2013-07-29T14:58:58.293

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:

enter image description here

edited Jul 29 '13 at 14:58

answered Jul 29 '13 at 10:34

sloth

99,095
21
171
219

looks great but I'm wondering, can I get the simple List through this syntax? – Miles Jul 29 '13 at 11:10
@user2629487 `var result = above_query.SelectMany(t => t.items.SelectMany(i => i.Items)).ToList()` – sloth Jul 29 '13 at 11:13
It seems this query is losing data var count1 = dataList.Where(x => x.item_id == 7643).Count(); // 8 var query = (from x in dataList group x by x.type_id into g let max_value_date = g.Max(x => x.value_date) select new {items = g.Where(x => x.value_date == max_value_date)}).ToArray(); var count2 = query.SelectMany(t => t.items).Where(x => x.item_id == 7643).Count(); // 0 – Miles Jul 29 '13 at 12:29
@user2629487 The items are propbaly filtered out because they belong to a `type_id`-group where their `value_date` is not the maximum. Hard to say without an input-/output-sample. – sloth Jul 29 '13 at 12:33
Here, I made a sample – Miles Jul 29 '13 at 13:04
@user2629487 Your sample does not fit your description: *group the list by type_id and take items where value_date is maximum* This filters out every item with `type_id == 1` except the one where `value_date == "2013.07.30"` (`"2013.07.30"` is the maximum), but in your sample you say you want two items with `type_id == 1`, one with ` value_date = "2013.07.30"` and one with ` value_date = "2013.07.29"`. – sloth Jul 29 '13 at 13:11
I'm really sorry for this misunderstandings. How can you see on example I'm trying get unique item_id with minimum value and maximum date from their type_id – Miles Jul 29 '13 at 14:03
*I'm trying get unique item_id with minimum value and maximum date from their type_id* This does also not fit the sample you've given. *unique item_id with minimum value* filters out `{item_id = 1, type_id = 1, item_value = 1, value_date = "2013.07.30"}` because the minimum `item_value` in the `item_id = 1` is `0`, not `1`. – sloth Jul 29 '13 at 14:15
But this item_id have the same type_id so considering the second condition I chose the one who has above date – Miles Jul 29 '13 at 14:28
OK, I created a query that gives you the results you want. – sloth Jul 29 '13 at 14:59
You want to group your data by `item_id` **and** `type_id` first, filtered by maximum date, **then** grouped by `item_id` again and filtered by minimum item_value. – sloth Jul 29 '13 at 15:01
Thank you very much, I am very grateful for you help – Miles Jul 30 '13 at 05:55

score 0 · Answer 2 · edited May 23 '17 at 12:11

0

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();

edited May 23 '17 at 12:11

Community

1
1

answered Jul 29 '13 at 10:43

Sergey Berezovskiy

232,247
41
429
459

there is one thing. latestOfEachType will contain only one item of type_id group, but in my case the value_date equal for many items and firstly i need take all of them and then get list of unique item_id where item_value is minimum – Miles Jul 29 '13 at 11:05
@user2629487 so there are several items with max date in each typeGroup and you want to select them all? – Sergey Berezovskiy Jul 29 '13 at 11:07
Yes. The first step is select them all and then get only one item from the each group by minimum item_value – Miles Jul 29 '13 at 11:12
@user2629487 OK, updated query to match your needs, as I understand them now :) – Sergey Berezovskiy Jul 29 '13 at 11:19

Grouping troubles in LINQ

2 Answers2