How to select the next and previous entities via Entity Framework?

Question

I have a web app that displays the details of some entity, let's call it Log. The entity is loaded from SQL Server via Entity Framework 4.

I would like to provide 'next' and 'previous' links to walk through logs bidirectionally.

Logs are ordered by two properties/columns:

Date
Time

Both of these columns may contain null, and there is no guarantee of uniqueness. If both of these values are null, then in order to guarantee a stable sort I order by the database Id, which is guaranteed to be non-null and unique.

Furthermore there may not actually be an entity before or after a given Log.

There are some other questions that address doing this with SQL directly. I'd like to know how to do this with the Entity Framework, ideally making only a single trip to the DB to bring back a few fields for this pair of Logs (id, title, etc).

If both Data and Time can be null you can't guarante that previous and next value really is previous and next log record of the current record. — Ladislav Mrnka, Mar 21 '11 at 18:44
To guarantee a stable order I fall back to the row ID if Date and Time are equal across two rows (including if they're both null). I've updated the question, thanks. — Drew Noakes, Mar 21 '11 at 19:15
So wouldn't something like this work? `.OrderBy(x => x.Id).FirstOrDefault(x => x.Id > currentSequenceId)` This says give me the `ID` of the current record and I'll search the database for the first record that is greater than the ID you gave me. — The Muffin Man, Sep 28 '12 at 04:00
@Nick, I am ordering on two fields, and these are ordered independently of the table id. — Drew Noakes, Oct 01 '12 at 12:14

John Leidegren · Answer 1 · 2011-03-21T22:21:13.087

Doesn't EF support Take and Skip?

The beauty of LINQ was that you could describe this complex sort criteria and the just page the result by saying q.Skip(50).Take(50). That would take you the second page if each page displayed 50 results. And it's of course translated to effective T-SQL that uses the ROW_NUMBER window function to instruct the database to seek out the result using the order you specified.

You can even have a very complex query with lots of filters. The end result is still manageable as you'll either have rows or you won't. All you need to think about is that the result could be empty.

A note about identity, as Ladislav pointed out the order is not guaranteed between entries of the exact same sort key (i.e. Date and Time both being null). So what you do is that you add an identity column which is your least important sort column. A log table/entity that doesn't have an identity can be somewhat argued to be improperly designed because the growth of the data is unpredictable when Date and Time can be null. It will result in bad page splits. The rule of thumb is that a table should have a narrow and unique clustering primary key. The identity column fits this quite nicely. It will also ensure that inserts are fast operations something your log table will appreciate.

With the help of a view you can put the order by and row_number stuff in plain T-SQL then query that using EF like this:

var q = from x in source
        join y in source on x.RowNumber equals y.RowNumber - 1 into prev
        join z in source on x.RowNumber equals z.RowNumber + 1 into next
        from p in prev.DefaultIfEmpty()
        from n in next.DefaultIfEmpty()
        select new { Current = x, Previous = p, Next = n }
        ;

...or possibly:

var q = from x in source
        join y in source on x.RowNumber equals y.RowNumber - 1 into prev
        join z in source on x.RowNumber equals z.RowNumber + 1 into next
        select new { 
            Current = x, 
            Previous = prev.DefaultIfEmpty(), 
            Next = next.DefaultIfEmpty() 
        }
        ;

Thanks John. Yes EF supports Take and Skip. How can I use them when I don't know what row number my entity is in the sorted query? If I did, I could use `Skip(N-1).Take(3)`, but in this case I can't. I have the `Id` of the item being viewed and need to find the other two. As for Ladislav's point, I updated the question to point out that I use the `Id` column as a third tier sort discriminator which guarantees a stable sort. — Drew Noakes, Mar 21 '11 at 19:20
The real question I have here is: how can I do this with a single trip to SQL? I've got an implementation working using `FirstOrDefault` on two sets of filtered and ordered `Log`s, but this requires two round trips to the DB. In plain SQL I could just use a `union`, but I don't know how to do a union in Linq when you use `FirstOrDefault` as that method is a terminator that evaluates the `IQueryable` and returns a single object, not a set that can be unioned. — Drew Noakes, Mar 21 '11 at 19:22
You kinda need to keep track of the current row number, if you don't have it and need to compute the current row number based of the id the `ROW_NUMBER` window function can do this, but it requires writing some T-SQL, if your really want to do it with EF I suppose it depends whether the Select overload that produces an index can project results from something like the `ROW_NUMBER` function but I doubt it. — John Leidegren, Mar 21 '11 at 22:05
You could consider setting up a special purpose view that computes the ROW_NUMBER so that it's accessible in an EF context. Then select from that view the current row by id and use left outer joins for the previous and next row based on the current row number. — John Leidegren, Mar 21 '11 at 22:11
Thanks for your update. If it comes to doing anything special in SQL just to prop up EF, then I'd probably write a tailored stored proc for this though. — Drew Noakes, Mar 22 '11 at 06:19
That sounds reasonable considering the limitations of EF. I say go for it. — John Leidegren, Mar 22 '11 at 08:05

score 1 · Accepted Answer · answered Mar 21 '11 at 20:36

I'm not sure if this works but let get it a try:

var query =(
        from l in context.Logs
        where l.UserId == log.UserId &&
             (    l.Date < log.Date
              || (l.Date == log.Date && l.Time < log.Time)
              || (l.Date == log.Date && l.Time == log.Time && l.Id < log.Id)
             )
        orderby l.Date descending, l.Time descending
        select l
    ).Take(1)
    .Concat((
        from l in context.Logs
        where l.UserId == log.UserId &&
             (    l.Date > log.Date
              || (l.Date == log.Date && l.Time > log.Time)
              || (l.Date == log.Date && l.Time == log.Time && l.Id > log.Id)
             )
        orderby l.Date, l.Time
        select l
    ).Take(1));

This does seem to work as `Concat` is available for `IQueryable`. Interesting. I'll have to introduce an extra property to indicate which row is which, as either `Take(1)` might return an empty sequence in which case it wouldn't be clear whether a single row was the previous or next log. `Concat` does seem to be what I was missing (and it seems kind of obvious now!) Thanks. — Drew Noakes, Mar 22 '11 at 06:23

score 0 · Answer 3 · answered Mar 13 '13 at 23:26

0

Put the record selection logic into the prev and next buttons seperatly. This way you only need to call the database one time for each button click.

answered Mar 13 '13 at 23:26

Tyson G

1

Thanks for your answer. This may help others out, but in my case I want to include details of the next/prev record in the button (link actually) itself. – Drew Noakes Mar 14 '13 at 01:49

score 0 · Answer 4 · answered Mar 21 '11 at 19:51

Here is the solution I have been running with. Ideally I'd like to do this with a single DB call, so if anyone can show me how to do that I'll accept their answer.

// Prev
var previousLog = (
        from l in context.Logs
        where l.UserId == log.UserId &&
             (    l.Date < log.Date
              || (l.Date == log.Date && l.Time < log.Time)
              || (l.Date == log.Date && l.Time == log.Time && l.Id < log.Id)
             )
        orderby l.Date descending, l.Time descending
        select l
    ).FirstOrDefault();

// Next
var nextLog = (
    from l in context.Logs
    where l.UserId == log.UserId &&
             (    l.Date > log.Date
              || (l.Date == log.Date && l.Time > log.Time)
              || (l.Date == log.Date && l.Time == log.Time && l.Id > log.Id)
             )
    orderby l.Date, l.Time
    select l
).FirstOrDefault();

How to select the next and previous entities via Entity Framework?

4 Answers4

Linked