Joining twice on the same table

I am using Lightspeed v4.0.1476.20948 (nightly 6 sept 2012) on the Nortwind database. I try to join on the same table twice:

dataGridView1.DataSource = uow.Categories
                    .Join(uow.Products, cat => cat.Id, p => p.CategoryId, (cat, p) => new { cat, p })
                    .Join(uow.Products, join => join.cat.Id, p => p.CategoryId, (c, p) => new { c.cat, product1 = c.p, product2 = p })
                    .Where(o => o.product2.ProductName == "Chai")
                    .Select(o => new { o.cat.CategoryName, Product1Name = o.product1.ProductName, Product2Name = o.product2.ProductName })
                    .ToList();

I expect the following result:

Beverages   Chai    Chai
Beverages   Chang   Chai
Beverages   Guaraná Fantástica  Chai
Beverages   Sasquatch Ale   Chai
Beverages   Steeleye Stout  Chai
Beverages   Côte de Blaye   Chai
Beverages   Chartreuse verte    Chai
Beverages   Ipoh Coffee Chai
Beverages   Laughing Lumberjack Lager   Chai
Beverages   Outback Lager   Chai
Beverages   Rhönbräu Klosterbier    Chai
Beverages   Lakkalikööri    Chai

But I get this result:

Beverages   Chai    Chai
Beverages   Chai    Chai
Beverages   Chai    Chai
Beverages   Chai    Chai
Beverages   Chai    Chai
Beverages   Chai    Chai
Beverages   Chai    Chai
Beverages   Chai    Chai
Beverages   Chai    Chai
Beverages   Chai    Chai
Beverages   Chai    Chai
Beverages   Chai    Chai

Can you explain this? Is this a bug?

It uses the following query:

SELECT
  t0.CategoryName AS [t0.CategoryName],
  t1.ProductName AS [t1.ProductName]
FROM
  Categories t0
INNER JOIN
  Products t1
ON
  t0.CategoryID = t1.CategoryId
INNER JOIN
  Products t2
ON
  t0.CategoryID = t2.CategoryId
WHERE
  t1.ProductName = 'Chai'

As you can see, it only selects t1.ProductName, not t2.ProductName, and it uses t1.ProductName in the WHERE instead of t2.ProductName.

This type of translation is not well handled by our LINQ provider as we dont have enough context to correctly determine which instance of Product is being referred to. Generally if you are writing these types of queries you will want to use the native querying API where you can more precisely specify the aliases being used in a query. We do have an existing item on our backlog to improve this but this will be part of a much wider effort to update the LINQ provider and its handling of alias tracking within queries. This is something we will be looking at in a later release.

In this particular case you can get this to work by making sure you align the name of the lambda parameters with the name of the property you are selecting into.

e.g.

unitOfWork.Categories
    .Join(unitOfWork.Products, cat => cat.Id, product1 => product1.CategoryId, (cat, p) => new { cat, p })
    .Join(unitOfWork.Products, join => join.cat.Id, product2 => product2.CategoryId, (c, p) => new { c.cat, product1 = c.p, product2 = p })
    .Where(o => o.product2.ProductName == "Chai")
    .Select(o => new { o.cat.CategoryName, Product1Name = o.product1.ProductName, Product2Name = o.product2.ProductName })
    .ToList();

Note that where you had p => in your original query these are now product1 => and product2 => respectively in the updated query above. This allows us to determine which instance of the joined entity type you are referring to in the LINQ translation.

Jeremy