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

hiya,

I have 2x entities:

a) item b) orderItem

<schema>

tblItem

itemId PK isSpecialDelivery specialDeliveryPrice

tblOrderItem

orderId PK itemId FK qty </schema>

I know the "orderId" of the order. I need to be able to calculate the entire "special delivery cost" of the order.The trouble is that the "specialDelivery" field, resides in a DIFFERENT table, ie tblItem.

 dalHamShopEquip.CollectionClasses.CskStoreOrderItemCollection entOrderItems = new CskStoreOrderItemCollection();

So, I have the above as a starting point.I don't even know if that is correct..What I think I have to do is create a predicate that does the following...

1) Given the "orderId", find all the items in tblOrderItem that ALSO have a specialDeliveryPrice in tblItem. 2) grab the tblItem.specialDeliveryPrice of each item and multiply by tblOrderItem.qty

I hope the above is clear, if not, please let me know.I'm quite rusty at the moment.

many thanks,

yogi

Do you only want o fetch that scalar value, or do you want to fetch a resultSet of multiple fields including the ( tblItem.specialDeliveryPrice * tblOrderItem.qty )?

I assume the following SQL is what you need:

SELECT SUM (tblItem.specialDeliveryPrice * tblOrderItem.qty)
FROM tblOrderItem
INNER JOIN tblItem ON ...
WHERE tblOrderItem.OrderId = xx
GROUP BY tblOrderItem.OrderId

If that's not what you need, please post the SQL Query that you want to execute.

Which can be done by a DynamicList, with one field, and its AggregateFunctionToApply is set to SUM, and the ExpressionToApply for the (Multiply operation) ref: manual -> "Generated code - Field expressions and aggregates"

Also a GetScalar() method can perform the same job.

hiya Walaa,

Thanks for the reply.

The query I needed was:

SELECT SUM (tblItem.specialDeliveryPrice * tblOrderItem.qty) FROM tblOrderItem INNER JOIN tblItem ON tblItem.itemId = tblOrderItem.itemId WHERE tblOrderItem.itemId = 13 GROUP BY tblOrderItem.tblItem

This seems to work.

Yes, I simply need the end VALUE, not a collection.

Which can be done by a DynamicList, with one field, and its AggregateFunctionToApply is set to SUM, and the ExpressionToApply for the (Multiply operation)

Are you saying that I can avoid the cost of creating entityCollections etc, and simply use a dynamic list?Previously, I would have created entityCollectiosn and used predicates.

many thanks,

yogi

hiya,

Ok, I have made some progress with the dynamic list:

ResultsetFields fields = new ResultsetFields(1);

fields.DefineField(dalHamShopEquip.TblProductFieldIndex.SpecialDeliveryPrice, 0, "specialDeliveryPrice","specialDeliveryPrice", AggregateFunction.Sum);

fields[0].ExpressionToApply = new DbFunctionCall ??  
    

I can't find the "DbFunctionCall" in intellisense, So I tried

fields[0].ExpressionToApply = "tblProduct.specialDeliveryPrice * tblOrderItem.quantity";

But obviously that didn't work either..

I'm struggling.

Any ideas?

ta,

yogi

Hi yogi, if you only need the final value, you can be achieved that with code like this:

// collection in play
OrderDetailsCollection orderDetails = new OrderDetailsCollection();

// relations to use. Important because we'll refer ProductsFields
RelationCollection relationsToUse = new RelationCollection();
relationsToUse.Add(OrderDetailsEntity.Relations.ProductsEntityUsingProductId);

// get your final value. the field (first parameter) doen't matter, it will be replaced for the expression (qt * up)
decimal orderPrice = (decimal)orderDetails.GetScalar(OrderDetailsFieldIndex.OrderId,
    (OrderDetailsFields.Quantity * ProductsFields.UnitPrice), AggregateFunction.Sum,
    (OrderDetailsFields.OrderId == 10254), relationsToUse, null);

So, adapt the above code to your case and let us know if everything is ok.

thanks bud,

I'm making progress with this...Or, I was until I refreshed the catalogs.I'll have to make a post about that.

ta,

yogi

hiya,

Ok, I can now get the project to compile etc.

I have made good progress but am getting the following error:

multi-part identifier "dbo.CSK_Store_Product.specialDeliveryPrice" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

This is the code I am using, based on advice from David.

dalHamilton.CollectionClasses.CskStoreOrderItemCollection collOrderItems = new CskStoreOrderItemCollection();
    
// relations to use. Important because we'll refer ProductsFields
RelationCollection relationsToUse = new RelationCollection();
        relationsToUse.Add(dalHamilton.EntityClasses.CskStoreOrderEntity.Relations.CskStoreOrderItemEntityUsingOrderId);    

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

I knwo that the param is ok. I'm struggling on this one.Any ideas?I think I am nearly there..

cheers,

yogi

Hi yogi, I think you are adding the wrong relation. You must be reachable your CskStoreProduct table, so you must add the relation CskStoreOrderItem -> CskStoreProduct.

hiya David,

you must add the relation CskStoreOrderItem -> CskStoreProduct

I have no idea how to do this.Do you mean I should do it in code?

If so, I tried the below.

relationsToUse.Add(dalHamilton.EntityClasses.CskStoreOrderItemEntity.Relations.CskStoreOrderEntityUsingOrderId); 

When I do this, "CskStoreOrderEntityUsingOrderId" is the ONLY relations option that I get in intellisense.When I run the code, I get the SAME error as before.

Please could you possibly post the code that I should use?I am struggling on this one :-0

many thanks,

yogi

Your retrieval collection is CskStoreOrderItemCollection, but at your _GetScalar _expression you are using fields of CskStoreProductEntity. So for obtain the _JOIN _expected at SQL (CskStoreOrderItem inner join CskStoreProduct on ...) you must add:

relationsToUse.Add(dalHamilton.EntityClasses.CskStoreOrderItemEntity.Relations.CskStoreProductEntityUsingProductId);

note that the starting entity in relation is _CskStoreOrderItemEntity _and not _CskStoreOrderEntity _that is not used at all in this case.

So, your final code would be:

dalHamilton.CollectionClasses.CskStoreOrderItemCollection collOrderItems = new CskStoreOrderItemCollection();
    
// relations to use. Important because we'll refer ProductsFields
RelationCollection relationsToUse = new RelationCollection();
relationsToUse.Add(dalHamilton.EntityClasses.CskStoreOrderItemEntity.Relations.CskStoreProductEntityUsingProductId); 

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

Cheers,

David

hiya David,

Thanks for the reply. Unfortunately, the code does not allow me to use the "CskStoreProductEntityUsingProductId" from intellisense:

Below is the code that you sent:

relationsToUse.Add(dalHamilton.EntityClasses.CskStoreOrderItemEntity.Relations.CskStoreProductEntityUsingProductId);

It seems that the "relations" will only let me select "CskStoreORDERntityUsingOrderId"

Actually, I have looked up the database and seen that (for some reason, there is no relatioship between:

CSK_Store_Product.productId

and

CSK_Store_OrderItem.productId

Maybe that is why llblGenPro can't see it?Below is the sql that gives me what I want llblGenPro to do.

What should I do now?Should I add the relationship in the sqlServer, or do it in llblGenPro designer..Maybe I can get llblGenPro to work without having to make any changes to the database?

SELECT SUM (CSK_Store_Product.specialDeliveryPrice * 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
GROUP BY CSK_Store_OrderItem.OrderId

Many thanks,

yogi

Hi,

The best thing to do if you can is adding the relation in the DB.

hiya,

Ok, I: 1) added the relationship

CSK_Store_Product.productId PK CSK_Store_OrderItem.productId FK

there were no errors.

2) refreshed the catalog (no errors) 3) re-generated the c# project (no errors)

Yet, the relations will still only let me use "CskStoreOrderEntityUsingOrderId" I still get the same error as before.

Why? What can i do to troubleshoot this.Please remember that the sql statement works, and this is exactly what I want llblGenPro to do.

Where do I go from here?

many thanks,

yogi

cheers David and Aurelien,

I eventually go the relations to give me the correct property:

Relations.CskStoreProductEntityUsingProductId)

I don't know why I have so many problems with the intellisnse in my projects, but for the moment, that's the problem solved.

thanks,

yogi