linq to sql Distinct and orderby

Question

var result = table1.Join(table2, o => o.ProgramID, t => t.ProgramID, (o, t) => new { o.ProgramID, t.Program })
         .OrderBy(t => t.Program)
         .Distinct();

the above linq statement actually returns the correct result, but he sql generated (below) is not as simple as it could be

SELECT [t2].[ProgramID], [t2].[Program]
FROM (
    SELECT DISTINCT [t0].[ProgramID], [t1].[Program]
    FROM [table1] AS [t0]
    INNER JOIN [table2] AS [t1] ON [t0].[ProgramID] = [t1].[ProgramID]
    ) AS [t2]
ORDER BY [t2].[Program]

I would have thought the sql below is far cleaner but I'm not sure of the linq statement to achieve it.

select distinct 
    o.ProgramID, 
    t.Program 
from 
    table1 0 
    inner join table2 t on t.ProgramID = o.ProgramID 
order by t.Program

Thanks in advance

Have you tried messing with the order of method invocations? I.e. swap orderby and distinct? — marr75, Nov 30 '10 at 21:41
new sql looks like SELECT DISTINCT [t0].[ProgramID], [t1].[Program] FROM [tbl_Batch] AS [t0] INNER JOIN [tbl_Program] AS [t1] ON [t0].[ProgramID] = [t1].[ProgramID] — Ian, Dec 01 '10 at 02:10
hmm... answered too soon, swapping them around drops the orderby :( — Ian, Dec 01 '10 at 02:19

score 36 · Answer 1 · answered Nov 30 '10 at 21:47

36

I don't know if it will help, but you can try something like this;

var result = (from o in table1
              join t in table2 on o.ProgramID equals t.ProgramID
              orderby t.Program
              select new { o.ProgramID, t.Program }).Distinct();

answered Nov 30 '10 at 21:47

Alex Mendez

5,120
1
25
23

1

Yeah that was what I was trying, but unfortunately the Distinct() totally wipes out the orderby. but as Reese suggested if you do this: var result = (from o in table1 join t in table2 on o.ProgramID equals t.ProgramID select new { o.ProgramID, t.Program }).OrderBy(t => t.Program).Distinct(); it works – Ian Dec 01 '10 at 02:15
And what do you have to do, when you want to Order more than one attributes? Something like this: OrderBy(t => new {t.Program, t.ProgramName, t.Description}).Distinct(); ? – user1531040 Dec 14 '12 at 12:19

score 12 · Answer 2 · answered Dec 14 '12 at 12:49

12

I tried this and that works:

var result = (from o in table1
              join t in table2 on o.ProgramID equals t.ProgramID
              select new { o.ProgramID, t.Program })
              .Distinct().OrderBy(t => t.Program)
                         .ThenBy(t => t.ProgramName)
                         .ThenBy(t => t.Description);

First you do Distinct and then OrderBy, then OrderBy works.

answered Dec 14 '12 at 12:49

user1531040

2,143
6
28
48

Thank you, big help. – Jeremy Ray Brown Mar 18 '21 at 17:28

score 5 · Answer 3 · answered Nov 30 '10 at 21:39

Profile the two queries, comparing stats-IO and the actual execution plan. It is entirely possible that it makes zero difference to the SQL server.

If you really want known TSQL, use ExecuteQuery-of-T and pass in the TSQL yourself. Maybe include some lock hints too (most commonly: NOLOCK)

linq to sql Distinct and orderby

3 Answers3

Linked