version 1.0.2005.1 final (self-servicing) VS2005 asp.net 2.0

hiya,

In my database, a product is either "specialDelivery" or not. This ia a BIT field.

I need to be able to SUM the "pricePaid" field of the products whose "isSpecialDelivery" field = 0

This is similar to a post I had a few days ago, so I hope it's maybe still familiar. The sql below seems to give me what I want.

SELECT SUM (CSK_Store_OrderItem.pricePaid * CSK_Store_OrderItem.quantity)
FROM CSK_Store_OrderItem

INNER JOIN CSK_Store_Product
ON CSK_Store_Product.productId = CSK_Store_OrderItem
. productId
WHERE CSK_Store_OrderItem
.OrderId = 13
AND CSK_Store_Product.isSpecialDelivery = 0

GROUP BY CSK_Store_OrderItem.OrderId

So, I think I need a very similar c# code to what I had for a previous query.Below is my attempt:

dalHamilton.CollectionClasses.CskStoreOrderItemCollection collOrderItems = new CskStoreOrderItemCollection();

RelationCollection relationsToUse = new RelationCollection();
   relationsToUse.Add(dalHamilton.EntityClasses.CskStoreOrderItemEntity.Relations.CskStoreProductEntityUsingProductId);

decimal NONspecialDeliveryTotal = (decimal)collOrderItems.GetScalar(CskStoreOrderItemFieldIndex.ProductId, CskStoreOrderItemFields.Quantity * CskStoreOrderItemFields.PricePaid, AggregateFunction.Sum, CskStoreOrderItemFields.OrderId == 13, relationsToUse, null);
    

I am not sure how I add the predicate "AND CSK_Store_Product.isSpecialDelivery = 0" to the existing predicate.

Any help appreciated.

many thanks,

yogi

CskStoreOrderItemCollection collOrderItems = new CskStoreOrderItemCollection();

IRelationCollection relationsToUse = new RelationCollection();
relationsToUse.Add(CskStoreOrderItemEntity.Relations.CskStoreProductEntityUsingProductId);

IPredicateExpression filter = new PredicateExpression();
filter.Add(CskStoreOrderItemFields.OrderId == 13);
filter.Add(CskStoreProductFields.isSpecialDelivery == 0);

decimal NONspecialDeliveryTotal = (decimal)collOrderItems.GetScalar(CskStoreOrderItemFieldIndex.ProductId, CskStoreOrderItemFields.Quantity * CskStoreOrderItemFields.PricePaid, AggregateFunction.Sum, filter, relationsToUse, null);

should do the trick.

There may even be a GetScalar overload that takes a RelationPredicateBucket. it would probally look something like this:

IRelationPredicateBucket bucket = new RelationPredicateBucket ();
bucket.Relations.Add(CskStoreOrderItemEntity.Relations.CskStoreProductEntityUsingProductId);
bucket.PredicateExpression.Add(CskStoreOrderItemFields.OrderId == 13);
bucket.PredicateExpression.Add(CskStoreProductFields.isSpecialDelivery == 0);

decimal NONspecialDeliveryTotal = (decimal)collOrderItems.GetScalar(CskStoreOrderItemFieldIndex.ProductId, CskStoreOrderItemFields.Quantity * CskStoreOrderItemFields.PricePaid, AggregateFunction.Sum, bucket, null);

thanks Jason,

the first bit of code did the trick.I'll stick to that for the moment, before delving into RelationPredicateBuckets.Thanks for the extra advice though.

yogi