Linq to Sql: select query with a custom order by

Question

We have following table, name it Products:

   UserName    ProductName    SortOrder
   --------------------------------------
   U1          U1P1           2
   U1          U1P2           3
   U1          U1P3           5
   U2          U2P1           1
   U2          U2P2           2
   U3          U3P1           4          

UserName and ProductName can be anything. SortOrder for each UserName is incremental but can start at any index and there may be gap between them.
I need a query that sorts the data this way:

   UserName    ProductName    SortOrder
   --------------------------------------
   U1          U1P1           2
   U2          U2P1           1
   U3          U3P1           4  
   U1          U1P2           3
   U2          U2P2           2
   U1          U1P3           5         

First row for each user is added then the second row and so on.
I am not sure if this is possible to be done in Linq to Sql or not.
Also keep in mind that there may be more that 10000 rows in the table so performance is important.

Answer 1

Pure linq (i.e. query syntax)? Not that I know of.

Linq-to-SQL? Sure - thanks to TSQL support:

var query = ctx.ExecuteQuery<Product>(@"
    select x.UserName, x.ProductName, x.SortOrder
    from (
        select p.UserName, p.ProductName, p.SortOrder,
            ROW_NUMBER() over (partition by p.UserName order by p.SortOrder)
                         as [Rank]
        from Products p) x
    order by x.[Rank], x.UserName, x.SortOrder");

As with any non-trivial query, the table's index strategy may be important here. You may want to try an index spanning UserName and SortOrder (measure the stats-IO), then remove that and try an index spanning SortOrder and UserName (other way around; again, measure the stats-IO).