CODEWITHSUNDEEP
c#linqlinq-to-sqlsql-order-bydistinct
Matheus Simon
Matheus Simon

Reputation: 696

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

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();

Upvotes: 1

Views: 1702

Answers (3)

DVL
DVL

Reputation: 192

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

Upvotes: 0

Md Hasan Ibrahim
Md Hasan Ibrahim

Reputation: 1898

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();

Upvotes: 1

Nguyễn Văn Phong
Nguyễn Văn Phong

Reputation: 14228

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

Upvotes: 2

Related Questions