Linq in C# Query needed - One to many table relationship, one to many records needed.

Question

need help to accomplish this in linq.

I have 3 tables.

1. Customers

2. CustumerOrder

3. OrderDetails

I need to list in one query all customers ( only if they have placed atleast one order) with all the orders for each customer only if order value is greated than 100.

Regards,

Harsh

Answer 1

Here's a guess based off some assumptions:

var result = 
    from customer in Customers.Where(a => a.CustomerOrder.Count > 0)
    from order in CustomerOrder.Where(a => a.OrderDetails.Sum(b => b.Price) > 100)
    select new
    {
        customer, 
        order.OrderDetails
    }

Answer 2

I'd say you want something like this:

from c in Customers
where c.CustomerOrders.Any()
select new
{
  customer,
  Orders = 
    from co in c.CustomerOrders
    where co.TotalPrice > 100
    select co.OrderDetails
}

Or, if you don't have an "TotalPrice" column in the CustomerOrder table, replace the inner query with this:

Orders =
  from cp in c.CustomerOrders
  where co.OrderDetails.Sum (od => od.Price) > 100
  select co.OrderDetails

Edit: if you want to include customers only with at least one order with OrderTotalPrice more than 100, use a let statement:

from c in Customers
let highValueOrders = 
    from co in c.CustomerOrders
    where co.TotalPrice > 100
    select co.OrderDetails
where highValueOrders.Any()
select new
{
  customer,
  highValueOrders
}

You can add more conditions in a similar fashion.