Querying Over Multiple Child Collections With NHibernate Part 2

In a previous post, I demonstrated how one could gain access to child collections using LINQ query syntax. I recently answered a question on StackOverflow where someone wanted to know how to do this using lambdas:
Link to Answer On StackOverflow

So here’s a somewhat real world example of how you could use LINQ’s SelectMany() method to gain access to collections of child objects. It can get a little messy with all of the projections into anonymous types so I’ve attempted to name my anonymous type’s properties to something meaningful. I’ve also put directly below the lambda query the equivalent query using LINQ’s query syntax to demonstrate just how much simpler and “cleaner” the query becomes.


    // Get StarPower Corporations pending shipment orders
    // shipping to NewYork or Chicago
    [Test]
    public void Test()
    {
        var listOfCities = new List<string> { "New York", "Chicago" };

        // With lambdas
        var customerAndOrders = session.Query<Customer>()
            .SelectMany( c => c.Orders, ( Customer, Orders ) => 
                new { Customer, Orders } )
            .SelectMany( CustomerAndOrders => 
                CustomerAndOrders.Orders.OrderDetails, 
                ( CustomerAndOrders, OrderDetails ) => 
                    new { CustomerAndOrders, OrderDetails } )
            .Where( x => 
                x.CustomerAndOrders.Customer.CompanyName == "StarPower Corporatation"
                && x.CustomerAndOrders.Orders.Status == "Pending Shipment"
                && listOfCities.Contains( x.OrderDetails.City ) )
            .Select( x => x.CustomerAndOrders.Customer )
            .Fetch( x => x.Orders )
            .ToList();

        // With query syntax
        var customersAndOrders2 =
            ( from c in session.Query<Customer>()
              from o in c.Orders
              from od in o.OrderDetails
              where c.CompanyName == "StarPower Corporation"
                    && o.Status == "Pending Shipment"
                    && listOfCities.Contains( od.City )
              select c )
                .Fetch( c => c.Orders )
                .ToList();
    }

    public class Customer
    {
        public int CustomerId { get; set; }
        public string CompanyName { get; set; }
        public IList<Address> Addresses { get; set; }
        public IList<Order> Orders { get; set; }
    }

    public class Address
    {
        public int AddressId { get; set; }
    }

    public class Order
    {
        public int OrderId { get; set; }
        public string Status { get; set; }
        public IList<OrderDetail> OrderDetails { get; set; }
    }

    public class OrderDetail
    {
        public int OrderDetailId { get; set; }
        public string City { get; set; }
    }

  • http://randyburden.com/ Randy Burden

    testing