Databases
StackOverflow2013
Postsdbo
POHow do I .OrderBy() and .Take(x) this LINQ query?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow do I .OrderBy() and .Take(x) this LINQ query?
    text
    copied!<p>The LINQ query below is working fine but I need to tweak it a bit.</p> <p>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.</p> <p>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.</p> <p>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.</p> <pre><code>var recipients = File.ReadAllLines(path) .Select (record =&gt; record.Split('|')) .Select (tokens =&gt; new { FirstName = tokens[2], LastName = tokens[4], recordId = tokens[13], date = Convert.ToDateTime(tokens[17]) } ) .OrderByDescending (m =&gt; m.date) .GroupBy (m =&gt; m.recordId) .Dump(); </code></pre> <p>Edit #1 - recordId is not unique. There may / will likely be multiple records with the same recordId. recordId is actually a customer number.</p> <p>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.</p> <p>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)</p> <p>Edit #3 -</p> <p>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.</p> <p>Now, how do I sort, in ascending order, by recordId?</p> <pre><code>var recipients = File.ReadAllLines(path) .Select (record =&gt; record.Split('|')) .Select (tokens =&gt; new { FirstName = tokens[2], LastName = tokens[4], recordId = Convert.ToInt32(tokens[13]), date = Convert.ToDateTime(tokens[17]) } ) .GroupBy (m =&gt; m.recordId) .OrderByDescending (m =&gt; m.Max (x =&gt; x.date ) ) .Select (m =&gt; 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 </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload