Selecting fields in grouping

Question

I have data like the following inside my DataTable:

   id   vrn   seenDate
   ---  ----  --------

    1   ABC   2017-01-01 20:00:05
    2   ABC   2017-01-01 18:00:09
    3   CCC   2016-05-05 00:00:00

I am trying to modify the data to only show vrn values with the most recent date. This is what I have done so far:

myDataTable.AsEnumerable().GroupBy(x => x.Field<string>("vrn")).Select(x => new { vrn = x.Key, seenDate = x.Max(y => y.Field<DateTime>("seenDate")) });

I need to modify the above to also select the id field (i.e. I do not want to group on this field, but I want to have it included in the resulting data set).

I cannot put in x.Field<int>("id") in the Select() part, as the Field clause does not exist.

And what `Id` field you want to include? For instance, `vrn` ABC has 2 id values. — Ivan Stoev, Apr 20 '17 at 10:45
There are lot of questions around this with solutions in sql. Do you need a solution in Linq only, or is it ok to have the solution in sql? — Shadow, Apr 20 '17 at 10:46
@Shadow Unfortunately it has to be done in LINQ in my case as the data is already in the data table. — MathLover, Apr 20 '17 at 10:47
For example, see: http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column — Shadow, Apr 20 '17 at 10:47
@Justcode I need to specify the column name since this is a data table though. — MathLover, Apr 20 '17 at 10:52

score 5 · Accepted Answer · answered Apr 20 '17 at 10:58

You need an equivalent of MaxBy method from MoreLINQ.

In standard LINQ it can be emulated with OrderByDescending + First calls:

var result = myDataTable.AsEnumerable()
    .GroupBy(x => x.Field<string>("vrn"))
    .Select(g => g.OrderByDescending(x => x.Field<DateTime>("seenDate")).First())
    .Select(x => new
    {
        vrn = x.Field<string>("vrn"),
        id = x.Field<int>("id"),
        seenDate = x.Field<DateTime>("seenDate"),
    });

This is exactly what i am looking for. Well done and thank you. — MathLover, Apr 20 '17 at 11:16

score 0 · Answer 2 · edited Apr 20 '17 at 11:03

0

You can use select new like this to select anything you want from your data

var query = from pro in db.Projects
                select new { pro.ProjectName, pro.ProjectId };

edited Apr 20 '17 at 11:03

FelixSFD

6,052
10
43
117

answered Apr 20 '17 at 10:49

M.h Basiri

56
9

Sorry I'm using cell phon to answer thus I could't use the code plugin to specify the code – M.h Basiri Apr 20 '17 at 10:50
Where would that go in my query? – MathLover Apr 20 '17 at 10:51
Var query=from item in MyDataTable select new {vrn=item.Vrn} – M.h Basiri Apr 20 '17 at 10:53
What you are pasting is a separate query though. I need to modify my query pasted above though – MathLover Apr 20 '17 at 11:00

score 0 · Answer 3 · answered Apr 20 '17 at 11:04

If you may have few ids in the same vrn with same max date then the following would work:

IEnumerable<DataRow> rows = myDataTable.AsEnumerable()
    .GroupBy(x => x.Field<string>("vrn"))
    .Select(x => new 
    {
        Grouping = x, 
        MaxSeenDate = x.Max(y => y.Field<DateTime>("seenDate"))
    })
    .SelectMany(arg => 
        arg.Grouping.Where(y => y.Field<DateTime>("seenDate") == arg.MaxSeenDate));

It will retrun an IEnumerable of the original DataRow so you have all your fields there.

Or you can add another select to have only the fields you need.

Selecting fields in grouping

3 Answers3