Hi,

I'm having a problem with the following query in LLBLGen v2.6 (latest version of 2.6).

from ClientOrdersEntity co in linqMetaData.ClientOrders
                                  where co.OrderStatus == (int) OrderStatus.NewOrder
                                        && co.RequestedFor < DateTime.Now
                                  group co by co.FkClientLocationId
                                  into grp
                                  select grp.OrderBy(g => g.RequestedFor).First();

Basically it's trying to retrieve the first customer order for a Client Location and can only retrieve the earliest requested order for that Location (so limiting it to one Order per Client Location).

The query works fine under LinqPad but fails under LLBLGen:

"Couldn't create any correlation filter lambda because the nested query didn't have any correlation filters. Please specify a filter in the nested query to tie the nested query to the parent query"

   at SD.LLBLGen.Pro.LinqSupportClasses.ValueListProjectionDefinition.PostProcessNestedQueries(ITemplateGroupSpecificCreator frameworkElementCreator, IElementCreatorCore generatedCodeElementCreator, MappingTracker trackedMappings)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleProjectionExpression(ProjectionExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle, SelectExpression newInstance)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleSelectExpression(SelectExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.HandleExpressionTree(Expression expression)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute()
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

The list should come back with a count of 0 with the current database contents.

Any help would be greatly appreciated.

LinqPad converts the query to the following SQL

-- Region Parameters
DECLARE @p0 Int = 1
DECLARE @p1 DateTime = '2010-10-03 14:13:17.469'
-- EndRegion
SELECT [t3].[test], [t3].[PK_ClientOrderID], [t3].[FK_ClientID], [t3].[FK_ClientLocationID], [t3].[Name], [t3].[PhoneNumber], [t3].[FK_RegisteredDeviceID], [t3].[FK_PublicUserID], [t3].[SpecialRequests], [t3].[CreatedOn], [t3].[RequestedFor], [t3].[TransmittedOn], [t3].[ResponseReceived], [t3].[OrderStatus], [t3].[EzSurcharge]
FROM (
    SELECT [t0].[FK_ClientLocationID]
    FROM [client].[ClientOrders] AS [t0]
    WHERE ([t0].[OrderStatus] = @p0) AND ([t0].[RequestedFor] < @p1)
    GROUP BY [t0].[FK_ClientLocationID]
    ) AS [t1]
OUTER APPLY (
    SELECT TOP (1) 1 AS [test], [t2].[PK_ClientOrderID], [t2].[FK_ClientID], [t2].[FK_ClientLocationID], [t2].[Name], [t2].[PhoneNumber], [t2].[FK_RegisteredDeviceID], [t2].[FK_PublicUserID], [t2].[SpecialRequests], [t2].[CreatedOn], [t2].[RequestedFor], [t2].[TransmittedOn], [t2].[ResponseReceived], [t2].[OrderStatus], [t2].[EzSurcharge]
    FROM [client].[ClientOrders] AS [t2]
    WHERE ([t1].[FK_ClientLocationID] = [t2].[FK_ClientLocationID]) AND ([t2].[OrderStatus] = @p0) AND ([t2].[RequestedFor] < @p1)
    ORDER BY [t2].[RequestedFor]
    ) AS [t3]
ORDER BY [t3].[RequestedFor]

The problem is the select grp.OrderBy(... as It's effectively a nested query, though 'grouping' doesn't exist till the query is executed, so it can't make additional constructs out of it. This is caused by the fact that it's an IGrouping<> typed element, and not just a flat set of data, but a nested structure: for every key there are n rows of data below it in a hierarchy. It might be linq to sql executes this properly, but that's due to the fact it postpones this query for every row till the main query is enumerated (so for every row a query is executed).

What if you code it like this?:

(from ClientOrdersEntity co in linqMetaData.ClientOrders
     where co.OrderStatus == (int) OrderStatus.NewOrder
          && co.RequestedFor < DateTime.Now
     group co by co.FkClientLocationId into grp
     orderby grp.RequestedFor
     select gpr).First();                               

Sorry David - Can't quite work out what the orderby statement should be. Linq doesn't like ordering by the RequstedFor field from the group.

BTW: Linq to SQL works quite nicely and doesn't actually execute multiple SQL Statements for the query - it performs a single select on the database (the second post in the thread).

The following query (on Northwind), groups Orders by CustomerID and returns CustomerId and First OrderDate.

var q = from o in metaData.Orders
                    where o.OrderDate < DateTime.Now
                    group o by o.CustomerId
                    into grp
                        select new {CustomerId = grp.Key, 
                            firstOrder = grp.Min(g => g.OrderDate)};

Should be similar to what you need.

Hi Walaa,

Yes - it's almost what I'm looking for but I don't think it would work for me. I need to be able to return the whole Order record and I need to take the first one ordered by RequestedDate rather than just the oldest Requested DateTime.

The problem I've got is that the RequestedDates are selected from a dropdown and there could be numerous orders with the same request time for a particular Supplier.

For each Supplier I need to only return 1 Order record where that Order record is the oldest in terms of Request DateTime.

It wouldn't be enough to return the date of the oldest request for a Supplier as there could be multiple matches for that DateTime. I'd rather have the query return the actual Order record so I can pass this on to the next processing steps.

I need to take the first one ordered by RequestedDate rather than just the oldest Requested DateTime.

Aren't these the same thing?! I know I'm taking the easy way, but the first order or the oldest order an be determined by the one having the least OrderDate, or having the least OrderId, if this is an Identity.

Having said so the following query retrieves first order placed by each customer.

var q2 = from o in metaData.Orders
                        group o by o.CustomerId
                        into grp
                        select new {CustomerId = grp.Key,
                                    OrderId = grp.Min(g => g.OrderId),
                        };
            var q = from o2 in metaData.Orders
                    join g in q2 on o2.OrderId equals g.OrderId
                    select o2;

Hi Walla,

thanks for the reply. They're almost the same - the only issue with the system is that a later order can be requested for an earlier time (so a check on the autoincrement ID wouldn't work). RequestedDate is the date and time that the customer would like the order to be ready. So an Order placed today could be requested for tomorrow but an order placed earlier in the week might actually be requested for next month.

The issue I've got with selecting purely based upon the date in the queries is that if I select the min request datetime I may have multiple orders for that time and so retrieve multiple orders with a select just based upon the datetime. Eg. if two Customers placed an order at the store for 9:00 AM on 10/10/2010 then a select based on the min date from the group would pull back both orders wouldn't it?

The issue I've got with selecting purely based upon the date in the queries is that if I select the min request datetime I may have multiple orders for that time and so retrieve multiple orders with a select just based upon the datetime. Eg. if two Customers placed an order at the store for 9:00 AM on 10/10/2010 then a select based on the min date from the group would pull back both orders wouldn't it?

No it wouldn't, only one will be retrieved. The resultset always contain a distinct values for the grouped by fields.

e.g. The following query:

select ShipCountry, Min(CustomerID), COUNT(*) from orders Group by ShipCountry

Returns one row for each shipCountry, although many of them had more than one customerId, and many of them has many orders from the same customerId. But since we group by the shipCountry, the resultset should contain distinct values for the shipCountry.

Ok but the query would need to take the oldest RequestedFor date on an Order, group by Supplier and bring back the Order.

Grouping by Supplier and taking the min of the OrderID wouldn't work as an older order may be requested first.

How can we change the query to take the min based upon the RequestedFor date for each supplier and pull back either the Order or Order ID.

It's easy to write a query to bring back the min order id and then select based on the Order id but this wouldn't work in this case.

Can you post the SQL query you need to execute.

Hi Walaa,

I'm trying to replicate the following query:

from co in ClientOrders
where co.OrderStatus == (int) OrderStatus.NewOrder
                                        && co.RequestedFor < DateTime.Now
                                  group co by co.FK_ClientLocationID
                                  into grp
                                  select grp.OrderBy(g => g.RequestedFor).First()

which translates to the following SQL (there's probably a cleaner way of writing it though):

-- Region Parameters
DECLARE @p0 Int = 1
DECLARE @p1 DateTime = '2010-10-05 17:55:32.786'
-- EndRegion
SELECT [t3].[test], [t3].[PK_ClientOrderID], [t3].[FK_ClientID], [t3].[FK_ClientLocationID], [t3].[Name], [t3].[PhoneNumber], [t3].[FK_RegisteredDeviceID], [t3].[FK_PublicUserID], [t3].[SpecialRequests], [t3].[CreatedOn], [t3].[RequestedFor], [t3].[TransmittedOn], [t3].[ResponseReceived], [t3].[OrderStatus], [t3].[EzSurcharge]
FROM (
    SELECT [t0].[FK_ClientLocationID]
    FROM [client].[ClientOrders] AS [t0]
    WHERE ([t0].[OrderStatus] = @p0) AND ([t0].[RequestedFor] < @p1)
    GROUP BY [t0].[FK_ClientLocationID]
    ) AS [t1]
OUTER APPLY (
    SELECT TOP (1) 1 AS [test], [t2].[PK_ClientOrderID], [t2].[FK_ClientID], [t2].[FK_ClientLocationID], [t2].[Name], [t2].[PhoneNumber], [t2].[FK_RegisteredDeviceID], [t2].[FK_PublicUserID], [t2].[SpecialRequests], [t2].[CreatedOn], [t2].[RequestedFor], [t2].[TransmittedOn], [t2].[ResponseReceived], [t2].[OrderStatus], [t2].[EzSurcharge]
    FROM [client].[ClientOrders] AS [t2]
    WHERE ([t1].[FK_ClientLocationID] = [t2].[FK_ClientLocationID]) AND ([t2].[OrderStatus] = @p0) AND ([t2].[RequestedFor] < @p1)
    ORDER BY [t2].[RequestedFor]
    ) AS [t3]
ORDER BY [t3].[RequestedFor]

i.e. I want only the New Orders that are requested prior to the current datetime and I only want the oldest single request per client location.

The query contains a CROSS APPLY, which is a construct our o/r mapper layer doesn't support, as it's a SQL Server only join construct, and the only way to translate the linq query directly to SQL.

I'll look into it some more to see whether a different query is possible. It's not said that CROSS APPLY is required to create the query though.

Thanks Frans!

A Cross Apply probably isn't needed to create the same result.

It just seemed the simplest query / Linq statement without too many nested queries.

CROSS APPLY executes for each row on the left side the right side query and then joins the results together. That's a way of doing it.

However, I think this query is simpler: (SQL)

SELECT  * 
FROM    Orders O
WHERE OrderID = 
(
    SELECT  TOP 1 OrderID
    FROM    Orders O2
    WHERE   O.OrderDate < GETDATE()
            AND O.EmployeeID = O2.EmployeeID
    ORDER BY OrderDate ASC
)

It returns the first Order for each Employee with an orderdate < a given date. This is basically the same thing: it executes the right side query (where) for each row in the left side (the main query).

In LINQ this is:

var q = from o in metaData.Order
        where o.OrderId == 
        (from o2 in metaData.Order
         where o2.OrderDate < DateTime.Now
               && o.EmployeeId == o2.EmployeeId
         orderby o2.OrderDate ascending
         select o2.OrderId).First()
        select o;

so your query becomes: (I think)

from co in ClientOrders
where co.ID == 
(
    from co2 in ClientOrders
    where co2.OrderStatus == (int) OrderStatus.NewOrder
            && co2.RequestedFor < DateTime.Now
            && co2.OrderStatus == co.OrderStatus
    orderby co2.RequestedFor
    select co2.ID
).First()
select co

Sorry for the delay - just wanted to post a message to say thanks!!

That query sorted out the issue Frans!

Thanks for the feedback