Multiple joins into one list

Question

With the query below I am doing multiple joins and selecting them all. I want to return this result into a list, so in this case I would have a List with a count of three, assuming that there are three addresses associated with this single customer id order...Right now the query works but I put exp.ToList() it gives me essentially a 2d list ( a list with a single element in which this element is a type list of 3 elements. I'm sure there's a good way to do this... thoughts ?

   var exp = (

        from t in this.reposOrders.All()

        join p1 in this.reposAddress.All()
        on t.AddressPrimary equals p1.AddressID into pp1
        from p1 in pp1.DefaultIfEmpty()

        join p2 in this.reposAddress.All()
        on t.AddressSecondary equals p2.AddressID into pp2
        from p2 in pp2.DefaultIfEmpty()

        join p3 in this.reposAddress.All()
        on t.AddressThird equals p3.AddressID into pp3
        from p3 in pp3.DefaultIfEmpty()

        where t.CustomerID == customerID

        select new { p1, p2, p3 }
    );

Answer 1

Can you use SelectMany? There are many linq samples here http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx

Answer 2

What you want to do suffers from poor normalization; AddressPrimary, AddressSecondary, and AddressThird should not be fields of Orders (I'm assuming order from your line from t in this.reposOrders.All()), but should be contained in a weak entity or join table.

That said, you can probably get what you want with the following query:

var primary = from t in this.reposOrders.All() where t.CustomerID == customerID select t.AddressPrimary;
var secondary = from t in this.reposOrders.All() where t.CustomerID == customerID select t.AddressSecondary;
var tertiary = from t in this.reposOrders.All() where t.CustomerID == customerID select t.AddressThird;
var ids = primary.Union(secondary).Union(tertiary);
var addresses = from a in this.reposAddress.All() where ids.Contains(a.AddressID) select a;