C# Linq. Join collections by ID

Question

I've got three collections with common key:

var shops = new[] { new { shopId = "1", shopName = "shop1" }, new { shopId = "2", shopName = "shop2" } , new { shopId = "3", shopName = "shop3" }};
var sales = new[] { new { shopId = "1", sales = "1000", place = 2 }, new { shopId = "2", sales = "0", place = 1 } , new { shopId = "3", sales = "100", place = 3 }};
var unpostedChecks = new[] { new { shopId = "1", checkQnt = 2 }};

How can I join these collections, using c# LINQ, to get result like that?:

var result = new[] { 
    new { shopId = "1", shopName = "shop1", sales = "1000", place = 2, checkQnt = 2 }, 
    new { shopId = "2", shopName = "shop2", place = 1 , sales = 0, checkQnt = 0},
    new { shopId = "3", shopName = "shop3", sales = "100", place = 3 , checkQnt = 0}
};

Answer 1

If you have three sets, you start with one, and then adding each additional set is an additional Join. SQL is the same way.

Assuming my edit to the question is faithful to the problem, that will look something like this:

var result = shops.
    Join(sales, shop => shop.shopId, sales => sales.shopId,
                 (shop, sales) => new { shopId = shop.shopId, shop = shop, sales = sales } ).
    Join(unpostedChecks, ss => ss.shopId, upc => upc.shopId,
                 (ss, upc) => new {shopId = ss.shopId, shopName = ss.shop.shopName, sales = ss.sales.sales, place = ss.sales.place, checkQnt = upc.checkQnt } );

Note for the intermediate join I only pulled out the shopId to its own property, to make it easy to use as a key for the next step. For the other properties I just packaged the entire objects in the intermediate state, rather than rebuilding individual elements. This is also more efficient, since the intermediate objects only need the object reference to the original item, instead of copying the data.

The problem here is this is still an INNER JOIN, and it seems you want an SQL -style LEFT OUTER JOIN. This can be done in C# using GroupJoin() plus the DefaultIfEmpty() method on the result, but I'm out of time today to take that next step.