linq to entities skip after concat

Question

I want to display results that don't have a secondary ID displayed first and then display items that do have a secondary ID. But then I need to Skip and Take.

IQueryable<thing> result;
IQueryable<thing> result2;

result2 = result
    .Where(t => !(t.second_id == null || t.second_id.Trim() == string.Empty))
    .OrderBy(t => t.second_id);

result = result
    .Where(t => (t.second_id== null || t.second_id.Trim() == string.Empty))
    .OrderBy(t => t.first_id);

result = result.Concat(result2);

return result
    .Select(t => t.primary_key)
    .Skip(pageSize * pageNumber)
    .Take(pageSize)
    .ToList();

The problem is that after Concat the IQueryable is no longer technically ordered so Skip and Take throw an error. Like this:

PagedList error: The method 'OrderBy' must be called before the method 'Skip'

What's your question? Why can't you simply add one or more `OrderBy()` calls after the last `Select()`? — Dan Wilson, Mar 30 '18 at 16:21
Also, `(t.second_id == null || t.second_id.Trim() == string.Empty)` could have just been `string.IsNullOrWhiteSpace(t.second_id)` — Camilo Terevinto, Mar 30 '18 at 16:23
@CamiloTerevinto I don't think that works with entity framework — Sam I am says Reinstate Monica, Mar 30 '18 at 16:29
@SamIam It can translate `IsNullOrWhiteSpace` just as it can translate `Trim`... — Camilo Terevinto, Mar 30 '18 at 16:30
@CamiloTerevinto Samlam is right. It doesn't work. I've tried. — BlueCardinal, Mar 30 '18 at 17:26
@CamiloTerevinto EF isn't as comprehensive as LINQ to SQL on function translations unfortunately. — NetMage, Mar 30 '18 at 17:53

score 2 · Answer 1 · answered Mar 30 '18 at 17:52

You can do it in one query with the conditional operator:

return result.OrderBy(t => (t.second_id != null && t.second_id.Trim() != String.Empty))
             .ThenBy(t => (t.second_id != null && t.second_id.Trim() != String.Empty) ? t.second_id : t.first_id)
             .Select(t => t.primary_key)
             .Skip(pageSize * pageNumber)
             .Take(pageSize)
             .ToList();

It would need some adjustment if you need to order duplicate second_id in some way, but your original code doesn't.

PS I folded in the negation operator since I think it reads more clearly.

score 0 · Accepted Answer · answered Mar 30 '18 at 16:45

0

You can do the job with a single query

result = result
.OrderByDescending(t => (t.second_id== null || t.second_id.Trim() == 
 string.Empty))
.ThenBy(t => t.second_id)
.ThenBy(t => t.first_id)
.Select(t => t.primary_key)
.Skip(pageSize * pageNumber)
.Take(pageSize)
.ToList();

answered Mar 30 '18 at 16:45

M Bakardzhiev

632
5
13

I think your first `ThenBy` will have a problem if `second_id` has multiple different length space only strings, or has null and empty strings. – NetMage Mar 30 '18 at 17:49
@NetMage I have tested it with different strings, including null and empty, and it works. After all, OrderBy just follows the pattern described in IComparable implementation for the order key, and I believe the string implementation covers all cases. – M Bakardzhiev Mar 30 '18 at 18:19
2

Try testing with `second_id` = " " and `second_id` = `null` and `second_id` = " " (two spaces) and you'll see they aren't sorted by `first_id` in that case but they should be. – NetMage Mar 30 '18 at 18:21
@NetMage Yes, you are right. Null and string.Empty are not equal and it places nulls above string.Empty. So it looks like they aren't sorted. I tested either only nulls or only string.Empty. I thought the string comparer treats them as equal. – M Bakardzhiev Mar 30 '18 at 18:43
Still in the general case it shall work, as it is unlikely that there are empty strings as keys in the database. As a whole, I would avoid string keys. – M Bakardzhiev Mar 30 '18 at 18:52
All true, but the specific inclusion of the `Trim` test makes me think there was a reason (e.g. perhaps they are using a char(N) field?). – NetMage Mar 30 '18 at 18:56
@NetMage No idea, OP accepted it so maybe it works for him. However your answer covers this edge case, so I upvoted it. – M Bakardzhiev Mar 30 '18 at 18:57
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/167898/discussion-between-m-bakardzhiev-and-netmage). – M Bakardzhiev Mar 30 '18 at 19:44

linq to entities skip after concat

2 Answers2