LINQ with prefetch and OrderByDescending

Posts   
 
    
Posts: 134
Joined: 10-Jan-2007
# Posted on: 09-Sep-2008 00:22:11   

LLBLGen version is 2.6.8.903.

Trying to get an OrderByDescending to work on a Prefetch path:

public PartEntity PartFetch(int partID) { using (DataAccessAdapter adapter = DataAccessAdapter.CreateForFetch()) { LinqMetaData metadata = new LinqMetaData(adapter); var q = (from p in metadata.Part where p.PartID == partID select p).WithPath<PartEntity>(ppath => ppath .Prefetch<PartTerritorySummaryEntity>(p => p.PartTerritorySummarys).OrderByDescending(pts => pts.CanForecast) .Prefetch<VendorEntity>(p => p.MasterVendor) .Prefetch<VOrdersEntity>(p => p.VOrders).OrderByDescending(o => o.OrderDate));

            return q.First();
        }
    }

OrderByDescending is not applied, if I add a LimitTo(10) that is applied. OrderBy does not work either.

Brian

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 09-Sep-2008 06:39:38   

Reproduced (LinqSupportClasses RTL = 2.6.8.814)

Using Lambda Expressions (generated ORDER BY is missing)

var q = (from c in metaData.Customers
         where c.CustomerId == "ALFKI"
         select c)
         .WithPath(customerPath => 
             customerPath.Prefetch<OrdersEntity>(c => c.Orders).OrderBy(o => o.Freight).LimitTo(2));

List<CustomersEntity> customers = q.ToList();

Generated SQL

SELECT 
[Northwind].[dbo].[Orders].[OrderID] AS [OrderId], 
[Northwind].[dbo].[Orders].[CustomerID] AS [CustomerId], 
[Northwind].[dbo].[Orders].[EmployeeID] AS [EmployeeId], 
[Northwind].[dbo].[Orders].[OrderDate], 
[Northwind].[dbo].[Orders].[RequiredDate], 
[Northwind].[dbo].[Orders].[ShippedDate], [Northwind].[dbo].[Orders].[ShipVia], 
[Northwind].[dbo].[Orders].[Freight], [Northwind].[dbo].[Orders].[ShipName], 
[Northwind].[dbo].[Orders].[ShipAddress], 
[Northwind].[dbo].[Orders].[ShipCity], [Northwind].[dbo].[Orders].[ShipRegion], 
[Northwind].[dbo].[Orders].[ShipPostalCode], 
[Northwind].[dbo].[Orders].[ShipCountry] FROM [Northwind].[dbo].[Orders]  
WHERE ( ( ( [Northwind].[dbo].[Orders].[CustomerID] = @CustomerId1)))

Using WithPath and PathEdges

LinqMetaData metaData = new LinqMetaData(adapter);

var q = (from c in metaData.Customers
         where c.CustomerId == "ALFKI"
         select c)
         .WithPath(new PathEdge<OrdersEntity>(CustomersEntity.PrefetchPathOrders, null,
             new SortExpression((OrdersFields.Freight | SortOperator.Ascending)), 0));

List<CustomersEntity> customers = q.ToList();

Generated SQL

SELECT 
[Northwind].[dbo].[Orders].[OrderID] AS [OrderId], 
[Northwind].[dbo].[Orders].[CustomerID] AS [CustomerId], 
[Northwind].[dbo].[Orders].[EmployeeID] AS [EmployeeId], 
[Northwind].[dbo].[Orders].[OrderDate], 
[Northwind].[dbo].[Orders].[RequiredDate], 
[Northwind].[dbo].[Orders].[ShippedDate], [Northwind].[dbo].[Orders].[ShipVia], 
[Northwind].[dbo].[Orders].[Freight], [Northwind].[dbo].[Orders].[ShipName], 
[Northwind].[dbo].[Orders].[ShipAddress], 
[Northwind].[dbo].[Orders].[ShipCity], [Northwind].[dbo].[Orders].[ShipRegion], 
[Northwind].[dbo].[Orders].[ShipPostalCode], 
[Northwind].[dbo].[Orders].[ShipCountry] FROM [Northwind].[dbo].[Orders]  
WHERE ( ( ( [Northwind].[dbo].[Orders].[CustomerID] = @CustomerId1))) 
ORDER BY [Northwind].[dbo].[Orders].[Freight] DESC

In the meantime please use the PathEdges workaround. We will look into it.

David Elizondo | LLBLGen Support Team
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 10-Sep-2008 11:02:07   

Fixed in next build.

Frans Bouma | Lead developer LLBLGen Pro