none
Compounding group joins. Is it possible? RRS feed

  • Question

  • I have customers with orders and orders with orderlines. I have successfully retrieved a hierarchical resultset of customers with orders using the following LINQ syntax:

    Code Snippet
    var query = from customer in context.Customers
    join orderHeader in context.Orders on customer.CustID equals orderHeader.CustID
    where orderHeader.ENTRY_DATE > start && orderHeader.ENTRY_DATE < end
    join orderHeader in context.Orders on customer.CustID equals orderHeader.CustID into orderGroup
    select new
    {
    Customer = customer,
    Orders = orderGroup.Where<Orders>(o => o.ENTRY_DATE > start && o.ENTRY_DATE < end)
    };


    Now I need to somehow include orderlines in this response but also in a hierarchical manner. So for each order I need to have a list of orderlines. Is this possible and if so how do you do it?
    Friday, May 9, 2008 7:48 PM

Answers

  •  

    I managed to create the following query, which seems to be doing what you want:

     

     

    Code Snippet

    var oodQuery = from order in ctx.Orders.Where(o => o.OrderDate < DateTime.Now)

                   join orderdetail in ctx.Order_Details.Where(od => od.ProductID > 10)

                   on order.OrderID equals orderdetail.OrderID into oodGroup

                   select new

                   {

                       Order = order,

                       OrderDetails = oodGroup

                   };

     

    var query = from customer in ctx.Customers

                join ood in oodQuery

                on customer.CustomerID equals ood.Order.CustomerID into coodGroup

                select new

                {

                    Customer = customer,

                    OrderAndOrderDetails = coodGroup

                };

     

     

    This however send multiple queries to the database, so you might be better off by querying for all customers, orders and orderdetails and then creating the structure using Linq To Objects.

     

    You can do it like so:

     

    Code Snippet

    //get customers

    var customerResults = (from customer in ctx.Customers

                           select customer).ToList();

     

    //get filtered orders

    var orderResults = (from order in ctx.Orders

                        where order.OrderDate < DateTime.Now

                        select order).ToList();

     

    //get filtered order details

    var orderDetailResults = (from orderdetail in ctx.Order_Details

                              where orderdetail.ProductID > 10

                              select orderdetail).ToList();

     

    //create the structure using Linq to Objects

    var oodQuery = from order in orderResults

                   join orderdetail in orderDetailResults

                   on order.OrderID equals orderdetail.OrderID into oodGroup

                   select new

                   {

                       Order = order,

                       OrderDetails = oodGroup

                   };

     

                   var query = from customer in customerResults

                   join ood in oodQuery

                   on customer.CustomerID equals ood.Order.CustomerID into coodGroup

                   select new

                   {

                       Customer = customer,

                       OrderAndOrderDetails = coodGroup

                   };

     

     

     

    Thanks,

    Maurycy

    Friday, May 9, 2008 9:22 PM