Hello

The info that you need: Adapter LLBLGen Pro 2.0.0.0 Final February 14th MS SQL 2005

Here are what I have for the databases.

Enterprise (database) Device (table) DeviceID (field) DeviceType (field)

Zone(database) Consumption (table) DeviceID (field) DateTime (field) Measure (field)

What I am able to do right now is get a DeviceType get all devices for that DeviceType, then go to Zone get the date range that I need and get all the measurements for all devices that match the initial device Type. I in fact only really need the top of the date. For example I want the last reading for the date of 9/30/2007, in this case it might return me the current reading on 9/24/2007 (today's date), but I still need one for each device that I pulled.

Right now I am getting the entire date range for September and sorting by date and getting the last record for each device, but this approach as you can probably image can get really troublesome when I start having 1,500 devices each with about 15,000 readings per month.

Could you point me to the correct way of doing this without doing a fetch for each device? But instead doing a top sorting by date and getting the last date in the range?

I hope the above makes sense.

Please let me know.

Thank you, Bruno Valle

What if you collect the DeviceId's in a List<int> and then use that to fetch the entire collection of Consumption using a FieldCompareRangePredicate? In that way you will hit only once fetch to DB.

That is basically what I am doing right now, but the problem is fetching all the consumptions when in reality I only really need the last consumption entry closest to a certain date.

So I created a filter to get only the collection for the devices by looping trough my list of devices and creating a filter to only pull those devices, however it is pulling all the history of consumption that the device has, that might be 3 years worth of consumption, it is too much. I only need one record.

Is there a way to construct a predicate that only returns me the closest record to a date. Or the top of a record sorted by date?

Please let me know.

Thank you, BFV

You could add a sort clause and in the Fetch statement look for an overload in which you set the max number of records to retrieve, set this parameter to 1 and you'll obtain the top record of the list.

Hope this was helpful.

I guess the easiest way would be to go through each device and get top of the date range.

Thank you, BV

Please post the SQL Query that you want to execute, so we can help you better formulate it.

Hi Walaa

Here is a example query that works:

SELECT * FROM tblConsumption r WHERE r.DeviceID IN (6,9,12) AND ReadingID = (SELECT TOP 1 ReadingID FROM tblConsumption r2 WHERE r2.DeviceID = r.DeviceID AND TimeID = (SELECT MAX(r3.TimeID) FROM tblConsumption r3 INNER JOIN refTime t3 ON r3.TimeID = t3.TimeID WHERE r3.DeviceID = r.DeviceID AND FullDateTime <= '2007-09-27 00:00') ORDER BY MillisecondsMinus30K DESC)

I would like to just pass in the 6,9,12 and the date of 2007-09.27 00:00 myself.

Does this help?

I think you need something that looks like the following (code untested):

EntityField2 timeId = ConsumptionFields.TimeID.SetObjectAlias("r3");
timeid.SetAggregateFunction(AggregateFunction.Max);

RelationCollection relations = new RelationsCollection();
relations.Add(ConsumptionEntity.Relations.RefTimeEntity);

PredicateExpression innerfilter = new PredicateExpression(ConsumptionFields.DevicId.SetObjectAlias("r3") == ConsumptionFields.DevicId);
innerfilter.Add(ConsumptionFields.FullDateTime.SetObjectAlias("r3") == MyDateTime);

IExpression innerExp = new ScalarQueryExpression(timeId, innerfilter, relations);

SortExpression sorter = new SortExpression(ConsumptionFields.MillisecondsMinus30K.SetObjectAlias("r2") | SortOperator.Descending);

PredicateExpression filter = new PredicateExpression
(ConsumptionFields.DevicId.SetObjectAlias("r2") == ConsumptionFields.DevicId);

filter.Add(ConsumptionFields.TimeId.SetObjectAlias("r2") == innerExp );

IExpression outerExp = new ScalarQueryExpression(ConsumptionFields.ReadingId.SetObjectAlias("r2"), filter, null, sorter, null, true);

int[] values = new int[3] {6, 9, 12};
RelationPredicateBucket bucket = new RelationPredicateBucket(ConsumptionFields.DevicId == values);
bucket.PredicateExpression.Add(ConsumptionFields.ReadingId == outerExp);

EntityCollection<ConsumptionEntity> col = new EntityCollection<ConsumptionEntity>();
DataAccesAdapter adapater = new DataAccesAdapter();
adapter.FetchEntityCollection(col, bucket);

Hi Walaa

Thank you for pointing me in the right direction, I am trying to understand some of the code right now, but since it was not tested I would expect some little clean up to be done. I tried to modify the code so it would fit in, but after spending a couple of hours on it, I was unable to do so, here is what I have that is closest to your original with minor grammar fixes and some questions in commented areas. Also as is I get an exception when trying to fetch.

What am I doing wrong?

public void Fetch(DateTime myDateTime)
{
    EntityField2 timeId = ConsumptionFields.TimeID.SetObjectAlias("r3");
    timeId.SetAggregateFunction(AggregateFunction.Max);

    RelationCollection relations = new RelationCollection();
    relations.Add(ConsumptionEntity.Relations.TimeEntityUsingTimeID);

    PredicateExpression innerfilter = new PredicateExpression(
        ConsumptionFields.DeviceID.SetObjectAlias("r3") == ConsumptionFields.DeviceID);

    /// I am assuming that you mean TimeFields insteadof ConsumptionFields for the FullDateTime, since FullDateTime
    /// does not exist on ConsumptionFields
    innerfilter.Add(TimeFields.FullDateTime.SetObjectAlias("r3") == myDateTime);

    IExpression innerExp = new ScalarQueryExpression(timeId, innerfilter, relations);

    SortExpression sorter = new SortExpression(ConsumptionFields.MillisecondsMinus30K.SetObjectAlias("r2") | SortOperator.Descending);

    PredicateExpression filter = new PredicateExpression
    (ConsumptionFields.DeviceID.SetObjectAlias("r2") == ConsumptionFields.DeviceID);

    filter.Add(ConsumptionFields.TimeID.SetObjectAlias("r2") == innerExp);

    /// I had to remove the last true since ScalarQueryExpression had no
    /// signature that had a bool at the end, or 6 parameters
    IExpression outerExp = new ScalarQueryExpression(ConsumptionFields.ReadingID.SetObjectAlias("r2"), filter, null, sorter, null); // , true);

    int[] values = new int[3] { 6, 9, 12 };
    RelationPredicateBucket bucket = new RelationPredicateBucket(ConsumptionFields.DeviceID == values);
    bucket.PredicateExpression.Add(ConsumptionFields.ReadingID == outerExp);

    EntityCollection<ConsumptionEntity> col = new EntityCollection<ConsumptionEntity>();
    using (DataAccessAdapter adapter = new DataAccessAdapter())
    {
        /// Here I get the following exception:
        /// An exception was caught during the execution of a retrieval 
        /// query: Failed to convert parameter value from a ScalarQueryExpression 
        /// to a Int32.. Check InnerException, QueryExecuted and Parameters 
        /// of this exception to examine the cause of this exception. 
        /// 
        /// Any ideas?
        adapter.FetchEntityCollection(col, bucket);
    }
}

Thank you so much for your support. BFV

I think the aliases at relations are missing. Try this:

RelationCollection relations = new RelationCollection();
relations.Add(ConsumptionEntity.Relations.TimeEntityUsingTimeID, "r3", "t3", JoinHint.Inner);

Perhaps I am making the example too complex, because I cannot follow it.

Here is a simple one: You have an invoice table.

Invoice CustomerID (int) InvoiceID (int) InvoiceDate (datetime)

I have the following data on the table:

CustomerID, InvoiceID, InvoiceDate
0,0,1/1/2000
1,1,1/2/2000
1,2,1/3/2000
2,3,1/4/2000
1,4,1/4/2000
3.5.1/5/2000
2.6.1/6/2000

Now I want to create a LLBLGen Pro that takes customer 0,1,2,3 for sales up to 1/6/2000 and therefore my result should be:

0,0,1/1/2000
1,4,1/4/2000
3.5.1/5/2000
2.6.1/6/2000

How would I do this on LLBLGen Pro code?

Please help!!!

Thank you, BFV

Hi BFV, if it that simply, yo can do this:

EntityCollection<InvoiceEntity> invoices = new EntityCollection<InvoiceEntity>(new InvoiceEntityFactory());

IRelationPredicateBucket filter = new RelationPredicateBucket();

int[] customersIDs = new int[3] {0, 1, 2, 3};
filter.Add(InvoiceFields.CustomerID == customersIDs);
filter.Add(InvoiceFields.InvoiceDate <=  new DateTime(2000, 6, 1));

using (DataAccesAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(invoices, filter);
}

Ref: LLBLGenPro Help - Using generated code - Adapter - Filtering and sorting - The predicate system.

Hi daelmo

Wouldn't this return me:

0,0,1/1/2000
1,1,1/2/2000
1,2,1/3/2000
2,3,1/4/2000
1,4,1/4/2000
3,5,1/5/2000
2,6,1/6/2000

since you are not limiting one record per customer?

I need something that when the return is:

1,1,1/2/2000
1,2,1/3/2000
1,4,1/4/2000

it sets the 1 to unique and gives me only the latest date.

For example on the example above it would only return me

1,4,1/4/2000 

since it is the oldest date and does not pass the 1/6/2000 restriction.

Hope this makes more sense.

God bless, BFV

I think you should use a Group By as follows:

SELECT CustomerId, MAX(InvoiceId) AS InvoiceId, MAX(InvoiceDate) AS InvoiceDate
FROM Invoice
GROUP BY CustomerId

And your code should look like the following:

ResultsetFields fields = new ResultsetFields(3);
fields.DefineField(InvoiceFields.CustomerId, 0);
fields.DefineField(InvoiceFields.InvoiceId, 1, "InvoiceId", AggregateFunction.Max);
fields.DefineField(InvoiceFields.InvoiceDate, 2, "InvoiceDate", AggregateFunction.Max);

GroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields[0]);

DataAccessAdapter adapter = new DataAccessAdapter();
DataTable dynamicList = new DataTable();
adapter.FetchTypedList(fields, dynamicList, null, 0, null, false, groupByClause);