Returning a value from an Entity

Question

I have a simple relationship between Orders and OrderItems. That is, the Items that are in a given order. This relationship is maintained on the OrderID.

I need a LINQ that returns the orders along with a summed value of the items in the order.

This is what I got so far..

var o =
            from order in context.POSOrders
            join ooi in context.POSOrderInventoryItems on order.OrderID equals ooi.OrderID
            let orders = new { order.OrderID, Price = ooi.Price * ooi.Quantity }
            group orders by order.OrderID into orderSale
            select new { 
                        OrderID = orderSale.Key, 
                        Price = orderSale.Sum(tr => tr.Price) 
            };

Now the problem with this is that I can not get any values out of the POSOrder object 'order'.

Typing 'order.' does not provide me with any intellisence. There is no value in 'order.DataCreated' or even 'order.OrderID' for example.

The rest of this LINQ does what it is suppose to but what is going on here?

Answer 1

Hi DoblySch;

If in the database you already have defined a relationship between the two tables, 1 to many, then you are making the query more complicated then it needs to be with the join. The thing that is missing from the solution below is the statement, let orders = new { order.OrderID, Price = ooi.Price * ooi.Quantity }, which is really not being used in the query. Can you state in words what the output of the query you are looking for?

// In the select clause the line that starts with Price the POSOrderInventoryItems is the navigation property so you may need to correct the acual name.
var o = from order in context.POSOrders
        select new {
            OrderID = order.OrderID,
            Price = order.POSOrderInventoryItems.Sum(tr => tr.Price)
        };

  

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 2

Hi DoblySch,

Welcome to the MSDN forum.

Please check this:

var o = from o in context.POSOrders
            join ooi in context.POSOrderInventoryItems on o.OrderID equals ooi.OrderID
            let orders = new { POSOrder = o, Price = ooi.Price * ooi.Quantity }
            group orders by o.OrderID into orderSale
            select new { 
                        OrderID = orderSale.Key, 
                        Price = orderSale.Sum(tr => tr.Price) 
                        Order = POSOrder
            };

Then, the Order of o contains all information about POSOrder object.

Have a nice day.

---

Alexander Sun [MSFT]
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 3

Thanks Fernando, that worked great to a point, but there is one more part to this that I forgot to mention.

Each item in OrderInventoryItems is stored with the price it was sold for AND the quantity of how many were sold.

Like this.

OrderID    ItemID     Price     Quantity

1              1              10         5

1              2              15         2

The 'sum' of the above for OrderID 1 would be (10 * 5) + (15 * 2).   $80

Right now I'm just getting 10 + 15.

I'm just not sure how to do this.

Answer 4

Hi DoblySch;

Try the query like this.

var o = from order in context.POSOrders
        select new {
            OrderID = order.OrderID,
            Price = order.POSOrderInventoryItems.Sum(tr => tr.Price * tr.Quantity)
        };

  

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".