LINQ - Order By and Distinct by different fields. Skip & Take distinct values

Question

I'm trying to ORDER BY some fields but SELECT DISTINCT by another field, and after that apply a Skip & Take

Code:

var result = entities.OrderBy(e => e.OrderField)
                     .Select(e => e.SelectField)
                     .Distinct()
                     .Skip(skip)
                     .Take(take)
                     .ToArray();

But I'm getting this exception:

System.NotSupportedException: 'The method 'Skip' is only supported for sorted input in LINQ to Entities. The method 'OrderBy' must be called before the method 'Skip'.'

I understand the reason (the set is being changed between the ordering and the skipping) but can I work around this issue without querying the database more than once (edit: and not fetching more entities than its supposed to)?

EDIT: I don't think there's an optimal solution for that, so what I did was fetch the values and then paginated them. *sigh*

var result = entities.OrderBy(e => e.OrderField)
                     .Select(e => e.SelectField)
                     .Distinct()
                     .ToArray()
                     .Skip(skip)
                     .Take(take)
                     .ToArray();

score 2 · Answer 1 · answered Feb 19 '20 at 14:01

2

You should add .OrderBy(e => e) before Skip.

var result = entities.OrderBy(e => e.OrderField)
                 .Select(e => e.SelectField)
                 .Distinct()
                 .OrderBy(e => e)
                 .Skip(skip)
                 .Take(take)
                 .ToArray();

Read the following post to have a better understanding

The method 'Skip' is only supported for sorted input in LINQ to Entities

answered Feb 19 '20 at 14:01

Nguyễn Văn Phong

13,506
17
39
56

Does this answer your question? Let me know if you need any help @Matheus Simon – Nguyễn Văn Phong Feb 19 '20 at 14:03
1

i checked the sql generated, the second order by cancels the first one. its like the first one wasnt made. nice try though. – Matheus Simon Feb 19 '20 at 14:15

Md Hasan Ibrahim · Answer 2 · 2020-02-19T15:00:10.027

1

Edit: Updated based on the comment. The following code should work in your case:

var result = entities
            .OrderBy(e => e.OrderField)
            .Select(e => e.SelectField)
            .GroupBy(e => e)
            .Select(group => group.Key)
            .Skip(skip)
            .Take(take)
            .ToArray();

edited Feb 19 '20 at 15:00

answered Feb 19 '20 at 14:08

Md Hasan Ibrahim

1,868
19
27

Cant order by a property after selected another one, its another set. – Matheus Simon Feb 19 '20 at 14:14
1

@MatheusSimon sorry for not understanding your scenario properly in the first place. Please see the updated code. It will solve you problem. – Md Hasan Ibrahim Feb 19 '20 at 14:42
its not grouping, nice try though – Matheus Simon Feb 19 '20 at 14:59
its throwing the exception. im going to make 2 queries and give up this solution. thanks hasan! – Matheus Simon Feb 19 '20 at 15:14

DVL · Accepted Answer · 2020-02-21T06:22:17.450

0

You can take from Distinct values from database and then work in memory like this:

var result = entities.OrderBy(e => e.OrderField)
                     .Select(e => e.SelectField)
                     .Distinct()
                     .ToArray()
                     .Skip(skip)
                     .Take(take)
                     .ToArray();

You will be querying once as you wanted

edited Feb 21 '20 at 06:22

answered Feb 19 '20 at 13:58

DVL

180
2
11

i edited the condition on the question.. im trying not to fetch all the entities and then applying the pagination. – Matheus Simon Feb 19 '20 at 14:16
3

If you have one zillion values after Distinct() and you want to skip(3) and Take(5), what a waste it would be to transfer all values to local memory! – Harald Coppoolse Feb 19 '20 at 14:31
1

@MatheusSimon edited my answer. Please tell if it will work. I am interested – DVL Feb 20 '20 at 10:31
@DVL it doesnt, i've given up finding an optimal solution for that... im now fetching the ids and then applying the pagination. it looks a little like that first code on your answer, but divided into 2 variables. thanks for the effort though. – Matheus Simon Feb 20 '20 at 14:29
the first code actually follows the same logic of what i had to do so i marked it... you can remove the other code if you'd like. – Matheus Simon Feb 20 '20 at 14:35
was facing same kind of problem some time ago and as I remember also did not find the good solution for that – DVL Feb 21 '20 at 06:25

LINQ - Order By and Distinct by different fields. Skip & Take distinct values

3 Answers3