Discovered this problem while working on a project at work, but have managed to replicate it using the DocCode sample from Breeze. I am using the latest 1.3.0. Basically the wrong records are being returned by the query when using 'expand', 'orderby' and 'take'. The problem can be demonstrated with the 'products sorted by related Category descending' test in queryTests.js:
The query in the test is:
var query = EntityQuery.from("Products")
.expand("Category")
.orderBy("Category.CategoryName desc, ProductName");
Which causes the following SQL to be issued to SQL Server:
SELECT
[Extent1].[ProductID] AS [ProductID],
[Extent1].[ProductName] AS [ProductName],
[Extent1].[SupplierID] AS [SupplierID],
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[QuantityPerUnit] AS [QuantityPerUnit],
[Extent1].[UnitPrice] AS [UnitPrice],
[Extent1].[UnitsInStock] AS [UnitsInStock],
[Extent1].[UnitsOnOrder] AS [UnitsOnOrder],
[Extent1].[ReorderLevel] AS [ReorderLevel],
[Extent1].[Discontinued] AS [Discontinued],
[Extent2].[CategoryID] AS [CategoryID1],
[Extent2].[CategoryName] AS [CategoryName],
[Extent2].[Description] AS [Description],
[Extent2].[Picture] AS [Picture]
FROM [dbo].[Products] AS [Extent1]
LEFT OUTER JOIN [dbo].[Categories] AS [Extent2] ON [Extent1].[CategoryID] = [Extent2].[CategoryID]
ORDER BY [Extent2].[CategoryName] DESC, [Extent1].[ProductName] ASC
Which is fine and gives the correct results sorted by CategoryName. However, if I change the query to the following:
var query = EntityQuery.from("Products")
.expand("Category")
.orderBy("Category.CategoryName desc, ProductName").take(10);
I have added the '.take(10)'. The SQL becomes:
exec sp_executesql N'SELECT
[Limit1].[ProductID] AS [ProductID],
[Limit1].[ProductName] AS [ProductName],
[Limit1].[SupplierID] AS [SupplierID],
[Limit1].[CategoryID1] AS [CategoryID],
[Limit1].[QuantityPerUnit] AS [QuantityPerUnit],
[Limit1].[UnitPrice] AS [UnitPrice],
[Limit1].[UnitsInStock] AS [UnitsInStock],
[Limit1].[UnitsOnOrder] AS [UnitsOnOrder],
[Limit1].[ReorderLevel] AS [ReorderLevel],
[Limit1].[Discontinued] AS [Discontinued],
[Extent3].[CategoryID] AS [CategoryID1],
[Extent3].[CategoryName] AS [CategoryName],
[Extent3].[Description] AS [Description],
[Extent3].[Picture] AS [Picture]
FROM (SELECT TOP (@p__linq__0) [Extent1].[ProductID] AS [ProductID], [Extent1].[ProductName] AS [ProductName]\, [Extent1].[SupplierID] AS [SupplierID], [Extent1].[CategoryID] AS [CategoryID1], [Extent1].[QuantityPerUnit] AS [QuantityPerUnit], [Extent1].[UnitPrice] AS [UnitPrice], [Extent1].[UnitsInStock] AS [UnitsInStock], [Extent1].[UnitsOnOrder] AS [UnitsOnOrder], [Extent1].[ReorderLevel] AS [ReorderLevel], [Extent1].[Discontinued] AS [Discontinued], [Extent2].[CategoryName] AS [CategoryName]
FROM [dbo].[Products] AS [Extent1]
LEFT OUTER JOIN [dbo].[Categories] AS [Extent2] ON [Extent1].[CategoryID] = [Extent2].[CategoryID]
ORDER BY [Extent1].[ProductID] ASC ) AS [Limit1]
LEFT OUTER JOIN [dbo].[Categories] AS [Extent3] ON [Limit1].[CategoryID1] = [Extent3].[CategoryID]
ORDER BY [Limit1].[CategoryName] DESC, [Limit1].[ProductName] ASC',N'@p__linq__0 int',@p__linq__0=10
Which is wrong because Extent1 is being ordered by ProductID instead of CategoryName which leads to the wrong records being returned.
So is this a bug or am I doing something wrong?
