How to get a set number of rows after ordering the records using linq?

Question

I am using ASP.NET Core with an EF Model. I would like to display a number of rows by Date and then by Time, but I only want 5 records. What am concerned with is it actually ordering all the rows first before taking the 5 records or is it taking the 5 rows from the table and then ordering them. What I need is the former, them to be ordered, than take the 5 records from that already ordered list in linq.

I have tried:

Model.Meetings.OrderBy(x => x.Date).ThenBy(x => x.Time).Take(5)

and also,

Model.Meetings.Take(5).OrderBy(x => x.Date.ThenBy(x => x.Time)

The first one doesn't seem to display or actually does not return anything.

The second one works and shows 5 records, but what 5 records?

@foreach (var meeting in Model.Meetings.Take(5).OrderBy(x => x.Date).ThenBy(x => x.Time))
{
    @if (meeting.Date > DateTime.Today)
    { 
        <tr>
            <td>@meeting.Name</td>
            <td>@meeting.Date</td>
            <td>@meeting.Time</td>
        </tr>
    }
}

I expect to get 5 records to display in order by Date then by Time, but order the records first before displaying them.

Here is example data set:

Id Date Day IsActive Location ThisEventId Time

1 2019-07-19 00:00:00.0000000 Friday 1 On-Campus 2 09:15:00.0000000

2 2019-07-19 00:00:00.0000000 Friday 1 On-Campus 3 09:00:00.0000000

3 2019-07-19 00:00:00.0000000 Friday 1 On-Campus 4 09:30:00.0000000

4 2019-07-19 00:00:00.0000000 Friday 1 On-Campus 5 10:30:00.0000000

5 2019-07-19 00:00:00.0000000 Friday 1 On-Campus 6 11:00:00.0000000

6 2019-07-19 00:00:00.0000000 Friday 1 On-Campus 7 11:30:00.0000000

7 2019-07-19 00:00:00.0000000 Friday 1 On-Campus 8 13:00:00.0000000

8 2019-06-20 00:00:00.0000000 Thursday 1 On-Campus 9 08:45:00.0000000

9 2019-06-20 00:00:00.0000000 Thursday 1 On-Campus 10 09:00:00.0000000

10 2019-06-20 00:00:00.0000000 Thursday 1 On-Campus 11 09:30:00.0000000

From what I can tell, you want the first query. The second will take 5 items from `Meetings` and then order them. The first will order them and take the first 5. Can you post an example data set of `Model.Meetings`? — ivcubr, Jul 11 '19 at 17:12
that might work Model.Meetings.OrderBy(x => new {x.Date, x.Time}).Take(5) — Power Mouse, Jul 11 '19 at 17:16
The first one should be working as you want, but since you're ordering by `Date`, the oldest will appear first, and based on the results you're seeing it's likely that the top `5` oldest results do not have a `Date` that's greater than `Today`. Perhaps you should use `OrderByDescending` instead, or change your `if` condition? To verify, try commenting out the `if` condition and see what you get. — Rufus L, Jul 11 '19 at 17:21
@PowerMouse Was not able to get that code to work, says it needs at least IComparable. — Juan, Jul 11 '19 at 18:16
@Rufus Yeah, turns out my if statement was causing the problem, I will try OrderByDescending instead. — Juan, Jul 11 '19 at 18:19
@Juan, sorry, i messed up with group by. try DateDescending, since you are looking for latest days: .OrderByDescending(o => o.Date).ThenBy(o=> o.Time) — Power Mouse, Jul 11 '19 at 19:12

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

0

EDIT: See end of post for updated answer after clarification by OP in comments below.

I believe the comment by @RufusL is correct that you want to use OrderByDescending. To provide a solution for this question, see the code below. This will order first by date and then by time, taking the first 5 items.

@{ var meetings = Model.Meetings.OrderByDescending(x => x.Date).ThenByDescending(x => x.Time).Take(5) }
@foreach (var meeting in meetings) {
    @if (meeting.Date > DateTime.Today) {
        <tr>
            <td>@meeting.Name</td>
            <td>@meeting.Date</td>
            <td>@meeting.Time</td>
        </tr>
    }
}

EDIT:

To get the dates that are in the future, simply using > does not work. You will want to use the .CompareTo method. The first step is to filter by times in the future accomplished by the expression below. This question address comparing DateTime in C#. See also the Microsoft docs for the .CompareTo method.

meetings.Where(x => x.Date.CompareTo(DateTime.Today) > 0)

Then OrderBy will sort them with the closest date to today, so the whole expression becomes

meetings.Where(x => x.Date.CompareTo(DateTime.Today) > 0).OrderBy(x => x.Date).ThenBy(x => x.Time)

A full example is below, where the List<DateTime> represents your Model.Meetings.

class Meeting {
    public string Name { get; set; }
    public DateTime Date { get; set; }
    public TimeSpan Time { get; set; }
}

List<Meeting> allMeetings = new List<Meeting>() {
    new Meeting{ Name = "1", Date = new DateTime(2019, 07, 7), Time = new TimeSpan(9, 15, 00) },
    new Meeting{ Name = "2", Date = new DateTime(2019, 07, 17), Time = new TimeSpan(9, 15, 00) },
    new Meeting{ Name = "3", Date = new DateTime(2019, 07, 17), Time = new TimeSpan(11, 15, 00) },
    new Meeting{ Name = "4", Date = new DateTime(2019, 07, 11), Time = new TimeSpan(11, 15, 00) },
    new Meeting{ Name = "5", Date = new DateTime(2019, 07, 12), Time = new TimeSpan(11, 15, 00) },
    new Meeting{ Name = "6", Date = new DateTime(2019, 07, 13), Time = new TimeSpan(11, 15, 00) },
    new Meeting{ Name = "7", Date = new DateTime(2019, 07, 14), Time = new TimeSpan(11, 15, 00) },
};

IEnumerable<Meeting> meetings = allMeetings.Where(x => x.Date.CompareTo(DateTime.Today) > 0).OrderBy(x => x.Date).ThenBy(x => x.Time);
foreach (Meeting meeting in meetings) {
    Console.WriteLine($"{meeting.Name}\t{meeting.Date}\t{meeting.Time}");
}

Since today is July 11th, the output I receive is below, which is in the desired order.

5 7/12/2019 12:00:00 AM 11:15:00

6 7/13/2019 12:00:00 AM 11:15:00

7 7/14/2019 12:00:00 AM 11:15:00

2 7/17/2019 12:00:00 AM 09:15:00

3 7/17/2019 12:00:00 AM 11:15:00

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 11 '19 at 18:25

ivcubr

1,988
9
20
28

This seems to work better, but is there way I can reorder those results so that the closest date to today shows first? – Juan Jul 11 '19 at 18:37
@Juan to clarify, you want only the dates that are in the future, starting with those closest to today? – ivcubr Jul 11 '19 at 18:40
I getting much later dates like 9/16 is why, when 7/19 is actually closer to today but greater than today. – Juan Jul 11 '19 at 18:42
Correct, starting with those closest to today and in the future – Juan Jul 11 '19 at 18:43
@Juan see my updated answer, which should solve your issue. Adding `.Take(5)` will work the same on this collection so I did not include that in my updated solution. – ivcubr Jul 11 '19 at 18:52
I see, I have Time in a separate column, how would I include that? The same thing just with CompareTo for time? – Juan Jul 11 '19 at 18:56
@Juan Yes, that should work. Or my recommendation would be to combine date and time columns into a single column using `DateTime`. – ivcubr Jul 11 '19 at 19:00
Something like this? `Model.meetings.Where(x => x.Date.CompareTo(DateTime.Today) > 0).OrderBy(x => x.Date).ThenBy(x => x.Time)` – Juan Jul 11 '19 at 19:01
I kinda stuck with the Time column being separate due to my requirements. – Juan Jul 11 '19 at 19:03
@Juan What data type are you using for the `Date` and `Time` columns? – ivcubr Jul 11 '19 at 19:06
My final result that works even with the take(5) method: `Model.Meetings.Where(x => x.Date.CompareTo(DateTime.Today) > 0).OrderBy(x => x.Date).ThenBy(x => x.Time).Take(5)` Thank you so much @ivcubr, I will have to learn more about CompareTo, without that I don't think was able to figure it out. – Juan Jul 11 '19 at 19:07
I am using DateTime and TimeSpan, for the Date and Time columns – Juan Jul 11 '19 at 19:07
@Juan I have updated my answer to reflect keeping the `Date` and `Time` columns separate. Glad to have been of help! Feel free to accept my answer if you feel it was useful to you. – ivcubr Jul 11 '19 at 19:18

How to get a set number of rows after ordering the records using linq?

1 Answers1