Hi,

I need some help with a sorting problem. In my DB I have the folowing relations: Order-Car-Dispatch-Address. An order can contain 1-10 cars which contains 1-3 dispatches. Each dispatch has a destination address (Rel. to Address table) and a driving sequence (1-3). Each car has at least 1 asociated dispatch and maximum 3. The address table contains a "CustomerRefID" column.

What I want is to get a list of cars orderd by "Address.CustomerRefID". Since a car can have up to 3 dispatches (the dispatch has the relation to the Address table) I want to use the one with the higest driving sequence.

This is what I have until now:

            RelationCollection relationsToUse = new RelationCollection();
            IPredicateExpression filter = new PredicateExpression();
            ISortExpression sorter = new SortExpression();

            filter.Add(OrderFields.InvoiceDate >= fromInvoiceDate.Date);
            filter.Add(OrderFields.InvoiceDate <= toInvoiceDate.Date);
            filter.Add(OrderFields.CustomerID == CustomerID);

            relationsToUse.Add(CarEntity.Relations.OrderEntityUsingOrderID);
            relationsToUse.Add(OrderEntity.Relations.CustomerEntityUsingCustomerID);
            relationsToUse.Add(CarEntity.Relations.DispatchEntityUsingCarID);
            relationsToUse.Add(DispatchEntity.Relations.AddressEntityUsingDestinationAddressID);

            sorter.Add(AddressFields.CustomerRefID | SortOperator.Ascending);
            sorter.Add(OrderFields.InvoiceDate | SortOperator.Ascending);
            sorter.Add(OrderFields.OrderID | SortOperator.Ascending);

            CarCollection result = new CarCollection();
            result.GetMulti(filter, 0, sorter, relationsToUse);
            return result;

But it is not sorted the way I want since it does not use the right relation betwen dispatch and address.

What should I try?

I'm lost here, please help me

First I don't think you need the following relation: relationsToUse.Add(OrderEntity.Relations.CustomerEntityUsingCustomerID);

Since a car can have up to 3 dispatches (the dispatch has the relation to the Address table) I want to use the one with the higest driving sequence.

I can't see how the above statement is implemented in code.

Best thing to do is to post the exact SQL Query that you want to execute. Then we can help you convert it to LLBLGen code.

You are right, I do'nt need that line. I'll get back with the SQL

And here the SQL is:

SELECT Car.*
FROM Car
left join [Order] on [Order].OrderID = Car.OrderID
left join Dispatch on Dispatch.DispatchID = (select top 1 DispatchID 
                                                from Dispatch 
                                                where CarID = Car.CarID
                                                order by DrivingSequence desc)
                        
left join [Address] on [Address].AddressID = Dispatch.DestinationAddressID
where [Order].CustomerID = @CustomerID
and [Order].InvoiceDate >= @fromInvoiceDate
and [Order].InvoiceDate <= @toInvoiceDate
order by [Address].CustomerRefID

1- For the left joins, pass a JoinHint to the RelationCollection.Add() method 2- Join on Dispatch without the subSelect 3- Add another predicate to the WHERE clause as follows:

Dispatch.DispatchID = (select top 1 DispatchID 
                                                from Dispatch 
                                                where CarID = Car.CarID
                                                order by DrivingSequence desc)

Which you can implement using a FieldCompareExpressionPredicate, and a ScalarQueryExpression for the expression part.

Then examine the generated SQL query to see if there is any problem with the generated SQL.