Linq. Take and OrderBy

[shapper]

Hello,

I have the following Linq Query:

viewData.TagsPapers = (from t in database.Tags
select new TagPaper {
Tag = t,
Count = t.PostsTags.Count
} into tp
where tp.Count != 0
orderby tp.Tag.Name, tp.Count
descending
select tp).Take(20).ToList();

I don't think this is making what I need.
I want to get the 20 records that have an higher value of tp.Count.
Then I need to order those 20 records by their tp.Tag.Name.

What am I doing wrong?

Thanks,
Miguel

 

[Marc Gravell]

I don't think this is making what I need.

I want to get the 20 records that have an higher value of tp.Count.
Then I need to order those 20 records by their tp.Tag.Name.

Didn't we already discuss this? First, the early orderby should be
reversed: tp.Count, tp.Tag.Name
Also, at a minimum, you need to do the OrderBy(tp=>tp.Tag.Name)
*after* the Take(20).
If this doesn't work (i.e. it is incorrectly mangled into the query),
then re-order the list after retreiving it:

var list = {...}.ToList();

list.Sort((x,y) => string.Compare(x.Tag.Name, y.Tag.Name));

Marc

 

[Jon Skeet [C# MVP]]

Hello,

I have the following Linq Query:

          viewData.TagsPapers = (from t in database.Tags
                                 selectnew TagPaper {
                                   Tag = t,
                                   Count = t.PostsTags.Count
                                 } intotp
                                 where tp.Count != 0
                                 orderby tp.Tag.Name, tp.Count
descending
                                 selecttp).Take(20).ToList();

I don't think this is making what I need.
I want to get the 20 records that have an higher value of tp.Count.
Then I need to order those 20 records by their tp.Tag.Name.

What am I doing wrong?

You need to order twice, so:
(from t in database.Tags
select new TagPaper {
Tag = t,
Count = t.PostsTags.Count
} into tp
where tp.Count != 0
orderby tp.Count descending
select tp)
..Take(20)
..OrderBy(tp => tp.Tag.Name)
..ToList();

Jon

 

[Marc Gravell]

For info, I checked whether LINQ-to-SQL does it correctly, and it
seems to; using NWind:

ctx.Log = Console.Out;
var qry = (from order in ctx.Orders
orderby order.Freight, order.OrderID
select new {
Order = order,
Lines = order.Order_Details.Count() })
.Take(20)
.OrderBy(x => x.Order.OrderID)
.ToList();

gives TSQL as below; complex looking, but does what we want in the
right order...

Marc

SELECT [t3].[OrderID], [t3].[CustomerID], [t3].[EmployeeID], [t3].
[OrderDate], [
t3].[RequiredDate], [t3].[ShippedDate], [t3].[ShipVia], [t3].
[Freight], [t3].[Sh
ipName], [t3].[ShipAddress], [t3].[ShipCity], [t3].[ShipRegion], [t3].
[ShipPosta
lCode], [t3].[ShipCountry], [t3].[value] AS [Lines]
FROM (
SELECT TOP (20) [t2].[OrderID], [t2].[CustomerID], [t2].
[EmployeeID], [t2].[
OrderDate], [t2].[RequiredDate], [t2].[ShippedDate], [t2].[ShipVia],
[t2].[Freig
ht], [t2].[ShipName], [t2].[ShipAddress], [t2].[ShipCity], [t2].
[ShipRegion], [t
2].[ShipPostalCode], [t2].[ShipCountry], [t2].[value]
FROM (
SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID],
[t0].[Order
Date], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].
[Freight],
[t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].
[ShipRegion], [t0].[S
hipPostalCode], [t0].[ShipCountry], (
SELECT COUNT(*)
FROM [dbo].[Order Details] AS [t1]
WHERE [t1].[OrderID] = [t0].[OrderID]
) AS [value]
FROM [dbo].[Orders] AS [t0]
) AS [t2]
ORDER BY [t2].[Freight], [t2].[OrderID]
) AS [t3]
ORDER BY [t3].[OrderID], [t3].[Freight]

 

[Glenn F. Henriksen]

First you have to get the twenty items with the highest number, then you have
to order the list again.

A test I made:
var Tags = new[] {
new { Name = "test1", Count=3 },
new { Name = "test2", Count=7 },
new { Name = "test3", Count=45 },
new { Name = "test4", Count=1 },
new { Name = "test5", Count=6 },
new { Name = "test6", Count=34 },
new { Name = "test7", Count=78 },
new { Name = "test8", Count=32 },
new { Name = "test9", Count=54 },
new { Name = "test10", Count=67 },
new { Name = "test11", Count=32 },
new { Name = "test12", Count=63 },
new { Name = "test13", Count=13 },
new { Name = "test14", Count=86 },
new { Name = "test15", Count=23 },
new { Name = "test16", Count=36 },
new { Name = "test17", Count=26 },
new { Name = "test18", Count=67 },
}.AsQueryable();

var result = (from t in Tags
orderby t.Count descending
select t).Take(10).OrderBy(t => t.Name);

This one just takes 10 since I didn't want to make that many object.

So your thing would be someting like:


viewData.TagsPapers = (from t in database.Tags
orderby t.Count descending
select t).Take(20).OrderBy(t => t.Name);

If you are using LINQ to SQL it will create SQL similar to this
SELECT <fields>
FROM (
SELECT <fields>
FROM Tags
ORDER BY Count DESC
)
ORDER BY Name

 

[shapper]

First you have to get the twenty items with the highest number, then you have
to order the list again.

A test I made:
            var Tags = new[] {
                new  { Name = "test1", Count=3 },
                new  { Name = "test2", Count=7 },
                new  { Name = "test3", Count=45 },
                new  { Name = "test4", Count=1 },
                new  { Name = "test5", Count=6 },
                new  { Name = "test6", Count=34 },
                new  { Name = "test7", Count=78 },
                new  { Name = "test8", Count=32 },
                new  { Name = "test9", Count=54 },
                new  { Name = "test10", Count=67 },
                new  { Name = "test11", Count=32 },
                new  { Name = "test12", Count=63 },
                new  { Name = "test13", Count=13 },
                new  { Name = "test14", Count=86 },
                new  { Name = "test15", Count=23 },
                new  { Name = "test16", Count=36 },
                new  { Name = "test17", Count=26 },
                new  { Name = "test18", Count=67 },
           }.AsQueryable();

            var result = (from t in Tags
                         orderby t.Count descending
                         select t).Take(10).OrderBy(t => t.Name);

This one just takes 10 since I didn't want to make that many object.

So your thing would be someting like:

viewData.TagsPapers = (from t in database.Tags
                                    orderby t.Count descending
                                    select t).Take(20).OrderBy(t => t.Name);

If you are using LINQ to SQL it will create SQL similar to this
SELECT <fields>
FROM (
    SELECT <fields>
    FROM Tags
    ORDER BY Count DESC
)
ORDER BY Name

Thank you very much to all!

Cheers,
Miguel

 

[Guy]

I see a lot of LINQ results using reflection to get values now a day. How
slow are these kind of late binding operation? Anyone has some scientific
comparison results.

Regards,

Hello,

I have the following Linq Query:

viewData.TagsPapers = (from t in database.Tags
select new TagPaper {
Tag = t,
Count = t.PostsTags.Count
} into tp
where tp.Count != 0
orderby tp.Tag.Name, tp.Count
descending
select tp).Take(20).ToList();

I don't think this is making what I need.
I want to get the 20 records that have an higher value of tp.Count.
Then I need to order those 20 records by their tp.Tag.Name.

What am I doing wrong?

You need to order twice, so:
(from t in database.Tags
select new TagPaper {
Tag = t,
Count = t.PostsTags.Count
} into tp
where tp.Count != 0
orderby tp.Count descending
select tp)
..Take(20)
..OrderBy(tp => tp.Tag.Name)
..ToList();

Jon

 

[Marc Gravell]

LINQ isn't necessarily using reflection; it can use dynamic methods to
use regular IL to access members. As for UI binding - that is going to
be the same either way. Even with untyped objects and no LINQ you can
speed things up *if* you know this is your bottleneck (bulk import/
export etc) - search for HyperDescriptor, for example.

In most cases, the work done in reflection is small in scope, and so
not a performance issue.

Marc