linq query Take() takes precedence over Distinct()?

Question

I have to select distinct 1000 records using LINQ. But when I see the query generated it takes 1000 records and applies distinct over that result.

IQueryable<TestClass> resultSet = (from w in ......).Distinct().Take(1000);

where my TestClass would be like,

public TestClass
{
public string TestPRop { get; set; }
 //..has some 20 properties
}

Any way to solve this to get distinct applied to the resultset and then take 1000 from the distinct result set?

Did you try to add `Skip(0)`? Maybe this will create an "offset fetch next" query and not a top n. — Bjørn-Roger Kringsjå, Sep 02 '15 at 18:05
Have you tried using a group operator instead of distinct to see if this affects the order of operations? — stephen.vakil, Sep 02 '15 at 18:09
Try `var subquery = (from w in ...).Distinct();` and then `result = (from s in subquery where 1==1 select s).Take(1000);`. to see if that forces the distinct to be part of a subquery. — juharr, Sep 02 '15 at 18:09
take a look at jon skeet answer http://stackoverflow.com/a/1011014/1876572 — Eldho, Sep 02 '15 at 18:38
please do `resultSet.ToString()` and paste the result as a edit to your question. That will show us what query it is generating. — Scott Chamberlain, Sep 02 '15 at 18:40
Does `Distinct` have any effect at all? Maybe the query `from w in ...` only returns unique results. — Gert Arnold, Sep 02 '15 at 19:04
What's the source here? This should perform distinct first, but a given query provider could have a bug. — Jon Hanna, Sep 03 '15 at 00:37
@ScottChamberlain sorry i dnt have the exact query with me now. It got generated in such a way that "Limit" got applied to all columns which i wanted to get.. with distinct before the whole query.. — Arvind897, Sep 03 '15 at 01:45
@GertArnold nope it may return duplicate records since the DB has one to many reltnshp plus, i want to do a distinct only comparing with prperties of my TestClass type. The resultset from DB should be a distinct set of values based on the all the columns available in my tables. — Arvind897, Sep 03 '15 at 01:48
@JonHanna the source is Oracle DB which i connect via the entity fw 5.0 ..it contains duplicates because of the reason mentioned in the above comment. — Arvind897, Sep 03 '15 at 01:54
Looks like it's a bug in the Oracle query provider. Which provider is it? — Gert Arnold, Sep 03 '15 at 10:19
Have you tried using the "group" option? I have updated my answer below to provide an example. — joelnet, Sep 04 '15 at 18:26

joelnet · Answer 1 · 2015-09-03T16:25:18.473

1

Distinct will be processed before the take. Double check your Distinct is performing correctly. Here's a working example:

var dataset = new int[]
{
    1, 1, 1, 2, 2, 2, 3, 3, 3, 3, 4, 4, 4, 4, 4, 5, 5, 5, 5, 6, 6, 7, 7, 8, 8, 9, 9, 10, 10
};

var query = from o in dataset select o;
var result = query.Distinct().Take(6);

// result = `1,2,3,4,5,6`

I suspect your issue is with using distinct with SQL. If that is the case, you can also use grouping to get the result you want.

var distinctById = from o in query
                   group o by o.Id into uniqueIds
                   select uniqueIds.FirstOrDefault();

edited Sep 03 '15 at 16:25

answered Sep 03 '15 at 00:17

joelnet

13,621
5
35
49

this works because you have a list of int. It wont work when you have a List of T (T is a class). – Arvind897 Sep 03 '15 at 01:42
2

@Arvind897 it certainly will work when `T` is a class. – Jon Hanna Sep 03 '15 at 09:02
@JonHanna nopes ..what it does is that it takes 1000 records and then does a distinct on that.. When you query it to the DB. – Arvind897 Sep 03 '15 at 14:17
"It wont work when you have a List of T (T is a class)" There's no querying of any DB when you have a List of T. – Jon Hanna Sep 03 '15 at 14:44
The issue might be using Distinct with SQL. If this is the case, you can use "group". I have updated my example. – joelnet Sep 03 '15 at 16:26

score 0 · Answer 2 · answered Sep 02 '15 at 18:03

0

You are only taking 1000 records and then applying distinct over those 1000 records with you code even though you are doing distinct() before Take().

The best way to accomplish this is to get the 1000 distinct records directly from your SQL query rather than from the code I.E

string queryString = "SELECT top 1000 from (select distinct ... from table)";

answered Sep 02 '15 at 18:03

Jack89515

69
8

`You are only taking 1000 records and then applying distinct over those 1000 records` - what are you basing this on? That's not how I read the question. – Kirk Broadhurst Sep 03 '15 at 01:41
Kirk, the point of my post is to use the sql query to get the data – Jack89515 Sep 03 '15 at 16:27

score -1 · Answer 3 · answered Sep 02 '15 at 18:15

-1

you can use nested linq queries

IQueryable<TestClass> resultSet = from x in ((from w in ......).Distinct()).Take(1000);

answered Sep 02 '15 at 18:15

Viru

2,228
2
17
28

2

That won't change the generated query in any way. This will compile down to an identical expression tree before it even gets to the query provider. – Servy Sep 02 '15 at 18:20

linq query Take() takes precedence over Distinct()?

3 Answers3