Hi,

I am very new to LLBLGEN and am trying to work out a query based on two related entities:

Vouchers and VoucherSales

A Voucher defines a field named VoucherSaleLimit, which is an integer field indicating the maximum number of that voucher which can be sold.

I need a query that returns only Vouchers that have a quantity sold that is less than the VoucherSaleLimit...With quantity sold being the COUNT of related VoucherSale records.

The SQL would look something like:

SELECT v.VoucherId, v.VoucherSaleLimit, COUNT(vs.VoucherId) AS Sales FROM Voucher v LEFT OUTER JOIN VoucherSale vs ON vs.VoucherId = v.VoucherId GROUP BY v.VoucherId, v.VoucherSaleLimit HAVING COUNT(vs.VoucherId) < v.VoucherSaleLimit

This is what I have so far - The "?????" is what I need help with.

public static EntityCollection LoadVouchers() { EntityCollection vouchers = new EntityCollection(new VoucherEntityFactory()); DataAccessAdapter adapter = new DataAccessAdapter();

//add prefetches
IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.VoucherEntity);
prefetchPath.Add(VoucherEntity.PrefetchPathVoucherSale);

//add filters
IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(??????);

//add sorter
ISortExpression sorter = new SortExpression();
sorter.Add(VoucherFields.StartDate | SortOperator.Ascending);
sorter.Add(VoucherFields.VoucherTitle | SortOperator.Ascending);

//get collection and sort
adapter.FetchEntityCollection(vouchers, filter, 0, sorter, prefetchPath);

return vouchers;

}

Can anyone tell me firstly, if this is possible, and secondly, how..??

Cheers, Jack

SELECT v.VoucherId, v.VoucherSaleLimit, COUNT(vs.VoucherId) AS Sales FROM Voucher v LEFT OUTER JOIN VoucherSale vs ON vs.VoucherId = v.VoucherId GROUP BY v.VoucherId, v.VoucherSaleLimit HAVING COUNT(vs.VoucherId) < v.VoucherSaleLimit

I think it would be easier this way:

SELECT * FROM Voucher v 
WHERE v.VoucherSaleLimit > (SELECT Count(*) FROM VoucherSale WHERE VoucherId = v.VoucherId)

And so you can use a ScalarQueryExpression in a FieldCompareExpressionPredicate to implement it.

Thanks Walaa,

Yes that SQL is fine - I wasn't sure if using a subquery would complicate my explanation. As I said I am very new to LLBLGEN and I am having some trouble working out the correct syntax to make your suggestion work.

I have the following:

ScalarQueryExpression saleCount = new ScalarQueryExpression(VoucherSaleFields.VoucherSaleId.SetAggregateFunction(AggregateFunction.Count));

FieldCompareExpressionPredicate sales = new FieldCompareExpressionPredicate(VoucherFields.VoucherSaleLimit, ComparisonOperator.GreaterThan, saleCount);

filter.PredicateExpression.Add(sales);

But the definition of FieldCompareExpressionPredicate is throwing a syntax error. Could you please look at the code I have already from my original post, plus what I have above, and see if you can direct me to where I'm going wrong..?

Thanks very much I appreciate the help..!!

What error is it producing? Also you did not pass a filter to the ScalarPredicateExpression.