Linq2Sql: How to join tables on more than one column

You can join two tables in Linq2Sql by using an anonymous type to specify the join. 

var r =
    from o in db.Orders
    join p in db.Products on o.ProductId equals p.ProductId
    join pu in db.ProductUsers on new { p.ProductId, o.UserId } equals new { pu.ProductId, pu.UserId }
    select new {o, p, pu};

This is equivalent to the following SQL:

SELECT * FROM Order o
JOIN Product p ON o.ProductId=p.ProductId
JOIN ProductUser pu ON p.ProductId=pu.ProductId AND o.UserId=pu.UserId

The anonymous type { p.ProductId, o.UserId } is lined up with the second anonymous type { pu.ProductId, pu.UserId } to create the join on both columns.

But what if you are trying to join two tables where the column names do not match?  For instance this SQL:

SELECT * FROM Order o
JOIN Product p ON o.ProductId=p.ProductId
JOIN ProductUser pu ON p.ProductId=pu.ProductId AND o.UserId=pu.CreatedByUserId

You would think you could create two anonymous types just like above and line up the properties like this:
First anonymous type: { p.ProductId, o.UserId } 
Second anonymous type: { pu.ProductId, pu.CreatedByUserId }

But if you try that you will get a compilation error:

The type of one of the expressions in the join clause is incorrect.  Type inference failed in the call to 'Join'.

What is going on here?  Both o.UserId and pu.CreatedByUserId have the same data type so why is there a compilation error? 

Because Linq2Sql lines up the two anonymous types by name and with the different names it cannot resolve the two column join.  Both the order and the name need to match for this to work.  To resolve this just specify an alias property name for one of the anonymous types that do not match like so:
First anonymous type: { p.ProductId, CreatedByUserId = o.UserId }
Second anonymous type: { pu.ProductId, pu.CreatedByUserId }

Now Linq2Sql knows how to completely match up the two column join.  Here is the full Linq2Sql statement for a join with two columns when the column names do not match:

var r =
    from o in db.Orders
    join p in db.Products on o.ProductId equals p.ProductId
    join pu in db.ProductUsers on new { p.ProductId, CreatedByUserId = o.UserId } equals new { pu.ProductId, pu.CreatedByUserId}
    select new {o, p, pu};