Hello,

I have looked a several posts with subqueries but have not seen this scenario covered for self servicing... Im not sure how to generate this query in code with LLBLGen. I can do it in SQL with a subquery but would like to understand how to do it in the BL.

Lets use an order scenario as the example. Lets say I have several orders by customer. I am trying to get a list that includes only the first most recent order by each customer. In TSQL I could do this with...

select * from orders a where a.orders = (select top 1 orderid from order where customerid = a.customerid order by orderdate desc) order by customerid

thank you...

jaskey wrote:

Hello,

I have looked a several posts with grouping but have not seen this scenario covered... Im not sure how to generate this query in code with LLBLGen. I can do it in SQL with a view but would like to understand how to do it in the BL.

Lets use an order scenario as the example. Lets say I have several orders by customer. I am trying to get a list that includes only the first most recent order by each customer. In TSQL I could do this with...

select * from orders a where a.orders = (select top 1 orderid from order where customerid = a.customerid order by orderdate desc) order by customerid

Could someone point me in the correct direction on how I might do this in code. My resulting data is going to bound to a grid. thank you...

(a.orders ??? )

Do this with a prefetch path. Specify a sortexpression and a limitation of 1 with the prefetchPath.Add( ) call and use the path to fetch the customers and the orders so the path looks something like: PrefetchPath2 path = new PrefetchPath((int)EntityType.Customer); SortExpression sorter = new SortExpression((CustomerFields.OrderDate | SortOperator.Descending)); path.Add(CustomerEntity.PrefetchPathOrders, 1, null, null, sorter);

Sorry, typo on that...

a.orderid

I dont believe this will work a my Date column is not in the customer entity but in the order entity.

Instead of trying to break this down into a common analogy, maybe I should just leave the context so I don't confuse the issue trying to put a northwind twist on it...

What I have are games and serial numbers, games have multiple serial numbers and there may be multiple entries for serial numbers for a particular game that is the same serial number. So, GameEntity 1 may have two serial number entities of 12345. Lets say it also has a single serial number entity of 6789 for comparison. I want my serial collection to only have the most recent serial entity for any uniqe serial number. So for the above scenario my results would be...

gameid serialid serialnumber submitteddate 1 1 6789 1/1/2006 1 3 12345 5/31/2006

the following should be omitted because it is not the most recent serial entity in the game.Seral collection 1 2 12345 2/28/2006

Here is the sql that gives me the results I need...

select * from serial a where a.serialid = (select top 1 serialid from serial where gameid= a.gameid order by submitteddate desc) order by a.gameid

Let me play around with this and try some real code. thank you.

Using Prefetch Path in this case will allow me to get the first serial entity by SubmittedDate by game but that only gives me one result per game from what I can tell. I need to get the first serial by submittedDate grouped by serial number. Do I need to move towards using SerialTypedList instead for this? I was hoping I would be able to filter an existing Game.SerialCollection down to a list of the last unique submitted serial number. I will keep experimenting... if anyone has any suggestions on the best approach I would be very appreciative. thank you again.

A prefetch path avoids that you have to execute a query for every parent. Any other query will require you to retrieve the data per parent. You could then for example use a FieldCompareSetPredicate which will represent the subquery you're using, where you're using a limit of 1, sort descending and where you alias the inner table. Though that will be inefficient, as you've to execute the query for every parent.

In the end, it became easier simply to remove the offending extra entities by copying the entities I wanted into a temporary collection. This allowed a single query from LLBLgen then...

    SerialCollection serials = new SerialCollection();
    IPredicateExpression filter = new PredicateExpression();
    filter.Add(PredicateFactory.CompareValue(SerialFieldIndex.IpdbId, ComparisonOperator.Equal, _game.Ipdbid));
    SortExpression sorter = new SortExpression();
    sorter.Add(SortClauseFactory.Create(SerialFieldIndex.SerialNumber, SortOperator.Ascending));
    sorter.Add(SortClauseFactory.Create(SerialFieldIndex.SubmittedDate, SortOperator.Descending));

    //get all the serials, I may have multiple registrations for a single serial
    //and a game will have multiple unique serials.
    serials.GetMulti(filter, 0, sorter);

    //begin hack...
    string lastSerial = String.Empty;
    SerialCollection filteredSerials = new SerialCollection();
    foreach (SerialEntity serial in serials)
    {
        //only the first serial number of many that are identical is copied into
        //the final collection. This basically allows me to limit one entity per
        //serial. 
        if (serial.SerialNumber != lastSerial) filteredSerials.Add(serial);
        lastSerial = serial.SerialNumber;
    }

    RadGridSerials.DataSource = filteredSerials;
    RadGridSerials.DataBind();