CODEWITHSUNDEEP
c#mysql.netlinq
Benny Ae
Benny Ae

Reputation: 2016

different results between Linq and SQL

i don't know why i get different results between this SQL and LINQ Could you like to tell me why...?

SQL:

select distinct top 50 (id) as d_id
from talbe1
where id<>-1
order by d_id  asc;

Linq:

    IList<int> myResults =
        (from t in dbconn.table1
         where t.id != -1
         orderby t.id ascending
         select t.id
        ).Distinct().Take(50).ToList();

    int callCnt = 0;
    foreach (int row in myResults)
    {
        callCnt++;
        Console.WriteLine(callCnt.ToString() + " " + row.ToString() );
    }

The SQL get the results i want, but the Linq result is like :

1 72662
2 84945
3 264577
4 77655
5 71756
6 76899
7 76719
8 77669
9 152211
10 79168
11 72334
12 71399
13 246031
14 80748
15 77715

.......

Upvotes: 2

Views: 514

Answers (3)

Esteban Elverdin
Esteban Elverdin

Reputation: 3582

Try

var myResults = dbconn.Table1.Where(e => e.id != -1).Select(e => e.id).Distinct()
         .OrderBy(t => t).Take(50).ToList();

Upvotes: 0

Corey
Corey

Reputation: 16574

The problem is the way that the Distinct() method works. Unfortunately it can (and usually does) change the order of the items in the list. You need to order the list after calling Distinct().

Try this:

IList<int> myResults =
    (
        from t in dbconn.table1
        where t.id != -1
        select t.id
    ).Distinct().OrderBy(i => i).Take(50).ToList();

Upvotes: 3

Karl Anderson
Karl Anderson

Reputation: 34846

This is a limitation of LINQ to SQL, where the OrderBy() must occur after the Distinct(), try this:

IList<int> myResults =
    (from t in dbconn.table1
     where t.id != -1
     select t.id
    ).Distinct().OrderBy(t => t).Take(50).ToList();

Upvotes: 3

Related Questions