Nested LINQ joins generates wrong SQL in LS 4.0.973.18978

Hi, I have the following in LinqPad (I get the same error in the actual code)...

DateTime lastUpdate = DateTime.Parse("2010-01-01 1:34:42Z");
List<string> hashkeys = new List<string>{"owhash","adamhash","felixhash"};

            var relevantParticipants =
                from bp in BoardParticipants
                where hashkeys.Contains(bp.Hashkey)
                select bp;

            var updatedMessagesInRelevantBoards =
                from pm in PrivateMessages 
                join rp in relevantParticipants
                    on pm.BulletinBoardId equals rp.BulletinBoardId
                where   pm.UpdatedOn > lastUpdate
                select pm;


            var authoredMessages =
                from um in updatedMessagesInRelevantBoards
                join rp in relevantParticipants
                    on um.AuthorId equals rp.ParticipantId
                select um;
authoredMessages.Dump("authoredMessages");

I get an SQL exception saying

"SqlException: The multi-part identifier "t3.Id" could not be bound. The multi-part identifier "t3.BulletinBoardId" could not be bound. The multi-part identifier "t3.CreatedOn" could not be bound. The multi-part identifier "t3.DeletedOn" could not be bound. etc...."

When I look at the generated SQL in LinqPad...

    SELECT
  t0.Id AS [t0.Id],
  t0.AuthorId AS [t0.AuthorId],
  t0.Body AS [t0.Body],
  t0.BulletinBoardId AS [t0.BulletinBoardId],
  t0.CreatedOn AS [t0.CreatedOn],
  t0.DeletedOn AS [t0.DeletedOn],
  t0.IncludeBoardStartDate AS [t0.IncludeBoardStartDate],
  t0.TypeId AS [t0.TypeId],
  t0.UpdatedOn AS [t0.UpdatedOn],
  t0.BannerImageId AS [t0.BannerImageId],
  t0.IncludeLinkToBoard AS [t0.IncludeLinkToBoard],
  t0.NameFormat AS [t0.NameFormat],
  t0.Salutation AS [t0.Salutation],
  t0.Subject AS [t0.Subject],
  t0.Body2 AS [t0.Body2],
  t0.LinkParagraph AS [t0.LinkParagraph],
  t0.LinkText AS [t0.LinkText],
  t0.RecipientRole AS [t0.RecipientRole],
  t0.SessionDateParagraph AS [t0.SessionDateParagraph],
  t0.WasSent AS [t0.WasSent],
  t3.Id AS [t3.Id],
  t3.BulletinBoardId AS [t3.BulletinBoardId],
  t3.CreatedOn AS [t3.CreatedOn],
  t3.DeletedOn AS [t3.DeletedOn],
  t3.Hashkey AS [t3.Hashkey],
  t3.LastLogin AS [t3.LastLogin],
  t3.MessageCount AS [t3.MessageCount],
  t3.ParticipantId AS [t3.ParticipantId],
  t3.RoleId AS [t3.RoleId],
  t3.UpdatedOn AS [t3.UpdatedOn]
FROM
  Communications t0
INNER JOIN
  (SELECT
    BoardParticipants.Id,
    BoardParticipants.BulletinBoardId,
    BoardParticipants.CreatedOn,
    BoardParticipants.DeletedOn,
    BoardParticipants.Hashkey,
    BoardParticipants.LastLogin,
    BoardParticipants.MessageCount,
    BoardParticipants.ParticipantId,
    BoardParticipants.RoleId,
    BoardParticipants.UpdatedOn
  FROM
    BoardParticipants
  WHERE
    (BoardParticipants.Hashkey IN ('owhash', 'adamhash', 'felixhash') AND BoardParticipants.DeletedOn IS NULL)
  ) t1
ON
  t0.BulletinBoardId = t1.BulletinBoardId AND t1.DeletedOn IS NULL
WHERE
  ((t0.UpdatedOn > '1/01/2010 2:34:42 p.m.' AND t0.TypeId = 7) AND t0.DeletedOn IS NULL)

Clearly there is no t3 defined. If I try to run this SQL in Microsoft SQL Server Management Express I get the same error. If I replace t1 with t3 (or t3 with t1) then the query runs OK. It looks to me like the SQL generation is getting confused at some point. This LINQ query was running fine under LS 3.

Any suggestions on a work around or bugfix?

Regards, Felix

Looking at the query I wonder if it is because the relevantParticipants query is nested twice. Perhaps when it is optimised it is losing track of the t3/t1 alias.

I got it working by refactoring the query into a single multi-column join.

var authoredMessages = 
            from bp in BoardParticipants
            join pm in PrivateMessages
            on bp.ParticipantId equals pm.AuthorId 
            where pm.BulletinBoardId == bp.BulletinBoardId && pm.UpdatedOn > lastUpdate && hashkeys.Contains(bp.Hashkey)
            select pm;

More readable/maintainable? maybe...

It does still seem to me that there is a bug in the query optimiser.

Thanks for reporting this - it will indeed be because the query with BoardParticipants has been included multiple times with LINQ this means that we have two distinct expression trees which will be equivalent in different parts of the tree, so as we part this we will alias this twice (t1 and t3 in this case). I am not too sure why the t3 instance has been stripped out of the query however, I would have expected that it would have included BoardParticipants twice.

If its possible to send through a repro for this that would be much appreciated. The workaround as you have found is simply to simplify how your query is expressed so that duplication doesn't occur, particularly in this case where you actually want to refer to the same table twice in multiple parts of the query.

Thanks!
Jeremy