How do I .OrderBy() and .Take(x) this LINQ query?

Question

The LINQ query below is working fine but I need to tweak it a bit.

I want all the records in the file grouped by recordId (a customer number) and then ordered by, in descending order, the date. I'm getting the grouping and the dates are in descending order. Now, here comes the tweaking.

I want the groups to be sorted, in ascending order, by recordId. Currently, the groups are sorted by the date, or so it seems. I tried adding a .OrderBy after the .GroupBy and couldn't get that to work at all.

Last, I want to .take(x) records where x is dependent on some other factors. Basically, the .take(x) will return the most-recent x records. I tried placing a .take(x) in various places and I wasn't getting the correct results.

var recipients = File.ReadAllLines(path)
.Select (record => record.Split('|'))
.Select (tokens => new 
{
FirstName = tokens[2],
LastName = tokens[4],
recordId = tokens[13],
date = Convert.ToDateTime(tokens[17])
}
)
.OrderByDescending (m => m.date)
.GroupBy (m => m.recordId)
.Dump();

Edit #1 - recordId is not unique. There may / will likely be multiple records with the same recordId. recordId is actually a customer number.

The output will be a resultset with first name, last name, date, and recordId. Depending on several factors, there many be 1 to 5 records returned for each recordId.

Edit #2 - The .Take(x) is for the recordId. Each recordId may have multiple rows. For now, let's assume I want the most recent date for each recordId. (select top(1) when sorted by date descending)

Edit #3 -

The following query generates the following results. Note each recordId only produces 1 row in the output (this is okay) and it appears it is the most recent date. I haven't thouroughly checked this yet.

Now, how do I sort, in ascending order, by recordId?

var recipients = File.ReadAllLines(path)
.Select (record => record.Split('|'))
.Select (tokens => new 
{
FirstName = tokens[2],
LastName = tokens[4],
recordId = Convert.ToInt32(tokens[13]),
date = Convert.ToDateTime(tokens[17])
}
)
.GroupBy (m => m.recordId)
.OrderByDescending (m => m.Max (x => x.date ) )
.Select (m => m.First () )
.Dump();


FirstName LastName recordId date 
X      X      2531334      3/11/2011 12:00:00 AM 
X      X      1443809      10/18/2001 12:00:00 AM
X      X      2570897      3/10/2011 12:00:00 AM
X      X      1960526      3/10/2011 12:00:00 AM
X      X      2475293      3/10/2011 12:00:00 AM
X      X      2601783      3/10/2011 12:00:00 AM
X      X      2581844      3/6/2011 12:00:00 AM
X      X      1773430      3/3/2011 12:00:00 AM
X      X      1723271      2/4/2003 12:00:00 AM
X      X      1341886      2/28/2011 12:00:00 AM
X      X      1427818      11/15/1986 12:00:00 AM

Answer 1

If you want something like the first 3 for each group, then I think you need to use a nested query like:

var recipients = File.ReadAllLines(path)
             .Select(record => record.Split('|'))
             .Select(tokens => new
             {
                 FirstName = tokens[2],
                 LastName = tokens[4],
                 RecordId = tokens[13],
                 Date = Convert.ToDateTime(tokens[17])
             }
             )
             .GroupBy(m => m.RecordId)
             .Select(grouped => new 
             {
                 Id = grouped.Key,
                 First3 = grouped.OrderByDescending(x => x.Date).Take(3)
             }
             .Dump();

and if you want this flattened into a record list then you can use SelectMany:

var recipients = var recipients = File.ReadAllLines(path)
             .Select(record => record.Split('|'))
             .Select(tokens => new
             {
                 FirstName = tokens[2],
                 LastName = tokens[4],
                 RecordId = tokens[13],
                 Date = Convert.ToDateTime(tokens[17])
             }
             )
             .GroupBy(m => m.RecordId)
             .Select(grouped => grouped.OrderByDescending(x => x.Date).Take(3))
             .SelectMany(item => item)
             .Dump();

Answer 2

You can't that easily order by a field which is not part of the group by fields. You get a list for each group. This means, you get a list of date for each recordId.

• You could order by Max(date) or Min(date).
• Or you could group by recordId and date, and order by date.

order by most recent date:

.GroupBy (m => m.recordId)
// take the most recent date in the group
.OrderByDescending (m => m.Max(x => x.date))
.SelectMany(x => x.First

The Take part is another question. You could just add Take(x) to the expression, then you get this number of groups.

Edit:

For a kind of select top(1):

.GroupBy (m => m.recordId)
// take the most recent date in the group
.OrderByDescending (m => m.Max(x => x.date))
// take the first of each group, which is the most recent
.Select(x => x.First())
// you got the most recent record of each recordId 
// and you can take a certain number of it.
.Take(x);

---

snipped I had before in my answer, you won't need it according to your question as it is now:

// create a separate group for each unique date and recordId
.GroupBy (m => m.date, m => m.recordId)
.OrderByDescending (m => m.Key)

Answer 3

just add

.OrderBy( g=> g.Key);

after your grouping. This will order your groupings by RecordId ascending.

Last, I want to .take(x) records where x is dependent on some other factors. Basically, the .take(x) will return the most-recent x records.

If you mean by "the most recent" by date, why would you want to group by RecordId in the first place - just order by date descending:

..
.OrderByDescending (m => m.date)
.Take(x)
.Dump();

If you just want to get the top x records in the order established by the grouping though you could do the following:

...
.GroupBy (m => m.recordId)
.SelectMany(s => s)
.Take(x)
.Dump();

Answer 4

This seems very similar to your other question - Reading a delimted file using LINQ

I don't believe you want to use Group here at all - I believe instead that you want to use OrderBy and ThenBy - something like:

var recipients = File.ReadAllLines(path)
.Select (record => record.Split('|'))
.Select (tokens => new 
{
FirstName = tokens[2],
LastName = tokens[4],
recordId = tokens[13],
date = Convert.ToDateTime(tokens[17])
}
)
.OrderBy (m => m.recordId)
.ThenByDescending (m => m.date)
.Dump();

For a simple Take... you can just add this .Take(N) just before the Dump()

However, I'm not sure this is what you are looking for? Can you clarify your question?