Fairly new with LLBLGen, so please take it easy on me...

The docs indicate that:

"You can also execute scalar queries, to retrieve a single value"

And gives the following code:

Dim orderDetails As New OrderDetailsCollection() Dim orderPrice As Decimal = CDec(orderDetails.GetScalar(OrderDetailsFieldIndex.OrderId, _ (OrderDetailsFields.Quantity * OrderDetailsFields.UnitPrice), AggregateFunction.Sum, _ (OrderDetailsFIelds.OrderId == 10254)))

I want to do something a little simpler that does not involve an expression. I simply need to return a date field for each record with a max ID field. I can't figure out the syntax. I am wanting to pass in the field I need the max ID on and the date field I want returned. Something like below:

dim someDate as Date = CDate(someFieldIndex.IdFieldINeedTheMaxOn, AggregateFunction.Max, someFieldIndex.DateFieldIWantToReturn)

Is this possible? Am I looking at it incorrectly?

Chuck SQL Server 2005

Hi Chuck,

I want to do something a little simpler that does not involve an expression. I simply need to return a date field for each record with a max ID field. I can't figure out the syntax.

You could use DynamicLists. See this example, it retrieves all the customers and the number of orders of each one:

// C#
ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(CustomerFields.CustomerId, 0);
fields.DefineField(new EntityField2("NumberOfOrders", 
    new ScalarQueryExpression(OrderFields.OrderId.SetAggregateFunction(AggregateFunction.Count),
                (CustomerFields.CustomerId == OrderFields.CustomerId))), 2);
DataTable results = new DataTable();
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchTypedList(fields, results, null);

With a little modification, this code return all customers and the last order date of each one.

// C#
ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(CustomerFields.CustomerId, 0);
fields.DefineField(new EntityField2("NumberOfOrders", 
    new ScalarQueryExpression(OrderFields.OrderDate.SetAggregateFunction(AggregateFunction.Max),
                (CustomerFields.CustomerId == OrderFields.CustomerId))), 2);
DataTable results = new DataTable();
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchTypedList(fields, results, null);

You can see this example in LLBLGenPro Help - Using generated code - Field expressions and aggregates - Scalar query expressions.

If you wish to use EntityClasses and have a customProperty that shows the lastDateOrdered, you can extend the Fields of an Entity. Please read http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=10287

Was this helpful? Can we assist you more on this?

David,

Thanks for the quick reply. I'm already using ResultsetFields, wanted to try something a little more advanced and concise. Return the date that I need in 2 lines of code!!

Thanks a bunch

daelmo wrote:

If you wish to use EntityClasses and have a customProperty that shows the lastDateOrdered, you can extend the Fields of an Entity. Please read http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=10287

What about that? you only need to specify the expression of a new field and then you will fetch the Collection in a normal way (1 line of code) and you will have a aggregate field already there.

If you don't want to use DynamicLists you have to use GetScalar that return ONE value. If you want to return one value per entity, you will need at least 3 or 4 lines of code