Linq inner join grouping

Question

I'm trying to create an expression tree dynamicly.

Let asume that I have two simple classes:

class CustomerType
{
   public int Id { get; set; }
   public string Name { get; set; }
   public OrderType[] Orders { get; set; }
}

class OrderType
{
   public int Id { get; set; }
   public DateTime Date { get; set; }
   public decimal Price { get; set; }
}

.. and corresponding entity types without any associations (so, I need to use a custom join).

I need to populate a list of Customers with the corresponding Orders. I know that there are two kind of joins in Linq: Left Outer Join and Left Inner Join.

So, using a Left Outer Join I can write the following query (to simplify I will illustrate the question using a Linq expression instead of a custom ExpressionTree generator code):

var query = from c in db.Customers
            join o in db.Orders on c.Id equals o.CustomerId into g
            select new AccountType() 
            {
              Id = c.Id,
              Name = c.Name,
              Orders = g
            };

So, an Orders property of the AccountType object will contain the all corresponding Orders.

I just do not understand how I can use a left inner join to get the same result with the filtration, based on the orders table fields (for example, I need to query all customers that have an order with the price greater than 100.00):

var query = from c in db.Customers
            join o in db.Orders on c.Id equals o.CustomerId 
            where o.Price > 100.00
            select new AccountType() 
            {
              Id = c.Id,
              Name = c.Name,
              Orders = ???
            };

Thanks for the help!

Answer 1

I would do it like that :

var query = from c in db.Customers
            join o in db.Orders on c.Id equals o.CustomerId into g
            select new AccountType() 
            {
              Id = c.Id,
              Name = c.Name,
              Orders = g.Where(o => o.Price > 100.00)
            };

With an inner join, you would have to use a group by clause :

var query = from c in db.Customers
            join o in db.Orders on c.Id equals o.CustomerId 
            where o.Price > 100.00
            group o by c into g
            select new AccountType() 
            {
                Id = g.Key.Id,
                Name = g.Key.Name,
                Orders = g
            }