Linq2SQL Help Converting row_number and partition

Question

Can someone please help me convert the following t-sql into c# linq?

select
    clientid,
    orderId 
from 
(
    select
        row_number() over (partition by clientid order by purchasedate desc) as rownum,
        clientid,
        id as orderId   
    from ordertraining
) as x where rownum = 1

Why? Why not use just a view? Why does it absolutely have to be in LINQ2SQL? You'd be surprised at the SQL it generates for something like that. — Arran
– Arran, Commented Sep 12, 2013 at 9:07

MarcinJuraszek · Accepted Answer · 2013-09-12 09:07:45Z

1

You can get the same results with following LINQ query:

from o in Orders
group o by o.clientId into g
select g.OrderByDescending(x => x.purchasedate).FirstOrDefault();

but it will not generate the same SQL. It will use CROSS APPLY instead.

answered Sep 12, 2013 at 9:07

MarcinJuraszek

126k15 gold badges200 silver badges267 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sergey Berezovskiy Over a year ago

Just want to add, that desired SQL cannot be produced by LINQ2SQL

Collectives™ on Stack Overflow

Linq2SQL Help Converting row_number and partition

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related