performance, row number after sort

Question

Hello

I have a team table with an rank column. I want to get the rank of a particular team by sorting the table on rank (several teams can have the same rank) and then returning the row number after the sort. The following code works but I am concerned about performance.

long
 team_id= 645;



var
 query = from
 c in
 owner.database.Team

     orderby
 c.Rank descending
, c.Modified ascending


     select
 c.Team_id;



long
 rank = query.ToList().IndexOf(team_id) + 1;

The ToList will get all rows from the table before it gets IndexOf (?). So how can I do this IndexOf on the database server without getting all the rows?

Thanx

Answer 1

You can use .Skip(n).Take(m) to get to a specific record.

Scratch that, that was not what you asked. (Didn't read the question correctly at first... :) )

You could try something like:

(from c in owner.Database.Team
from pt in owner.Database.Team
where pt.Rank > c.Rank || (pt.Rank == c.Rank && pt.Modified < c.Modified)
select pt).Count();

...or...

(
from c in owner.Database.Team
from pt in owner.Database.Team
where pt.Rank > c.Rank 
select pt
).Union(
from c in owner.Database.Team
from pt in owner.Database.Team
where pt.Rank == c.Rank && pt.Modified < c.Modified
select pt
).Count();

---

Kristofer - Huagati Systems Co., Ltd.
Cool tools for Linq-to-SQL and Entity Framework:
huagati.com/dbmltools (add-in with new features for Visual Studio 2008's L2S and EF designers)
huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)

Answer 2

Hi,

 

Welcome to LINQ to SQL forum!

 

Since SQL queries operate on multisets instead of sequences.  The ORDER BY operator must be the last clause appled to the results.  So, some extension methods based on sequences index like TakeWhile, SkipWhile are not supported in LINQ to SQL.  For detail, please see http://msdn.microsoft.com/en-us/library/bb399342.aspx.

 

If the performance is really the concern, we can try to convert the ORDER BY query into a WHERE query:

=========================================================================
var rank = (from t in db.Team

            let i = db.TestDBs.Single(te => te.Team_id == 645)

            where t.Rank > i.Rank || (t.Rank == i.Rank && t.Modified < i.Modified)

            select t.Team_id).Count();
=========================================================================

 

The limitation of this workaround is that both the Rank and Modified column should be comparable at the LINQ to SQL client side. 

 

 

Another workaround is to use the new Ranking functions introduced in SQL Server 2005.  By LINQ to SQL, we can also directly run T-SQL by DataContext.ExecuteQuery<>.  

=========================================================================
                string cmd = "SELECT c.Ranking " +

                             "FROM " +

                                "(SELECT t.Team_id, ROW_NUMBER() OVER " +

                                "(ORDER BY t.Rank DESC, t.Modified) as Ranking " +

                                    "FROM Team AS t) AS c " +

                             "WHERE c.Team_id = 645";

                var query = db.ExecuteQuery<MyRank>(cmd);

                long index = query.First().Ranking;

 

 

    public class MyRank

    {

        public long Ranking { get; set; }

}
=========================================================================

If you have any questions, please feel free to let me know.

 

Have a nice weekend!

 

 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 3

Thank you both very much for your answers!

Answer 4

You are welcome!  :)
 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.