LINQ join with a literal values

Question

This is kind of hypothetical as I'm thinking about how to design this. Consider this query with some literal integer values in the join criteria:

Select * 
From LeftPeople l
Inner Join RightPeople r On r.RecordId = l.RecordId and r.ResultId = 3 and l.ResultId = 7

Would this be the correct equivilant in LINQ? It seems kind of a cludge if this would even work and am wondering if there's a better way. I suppose I could put it in the where criteria instead. What do you think?

 var query = from leftPerson in LeftPeople
   join rightPerson in RightPeople on 
   new { RecordId = leftPerson.RecordId, RightResultId = 3,  LeftResultId = leftPerson.ResultId }
   equals new { RecordId = rightPerson.recordid, RightResultId = rightPerson.ResultId ,  LeftResultId = 7 }
   select new { LeftPerson = leftPerson, RightPerson = rightPerson };

Answer 1

If you only want items with a RightResultId of 3, then use a Where clause to filter out others. I don't think this really belongs in a join clause. It may work, but it feels messy to me.

In other words, I would do:

var query = from left in LeftPeople
            where left.RightResultId = 3
            join right in RightPeople.Where(r => r.LeftResultId = 7)
            on left.RecordId equals right.RecordId
            select new { LeftPerson = left, RightPerson = right };

That's assuming that RecordId is really the primary key here.

Answer 2

The filter for LeftPeople.ResultId and RightPeople.ResultId belong in the Where clause in your LINQ statement:

var query = from l in LefPeople
            join r in RightPeople on l.RecordId equals r.ResultId
            where r.ResultId == 3 && l.ResultId == 7
            select new { LeftPerson = l, RightPerson = r };

Trying to shoehorn them into the Join clause will only make your query harder to read/understand.

That said, I would probably argue that the SQL Query should have those filters in the WHERE clause as well.

Answer 3

if you made an association, you could write:

from left in leftPeople
where left.ResultId == 7
from right in left.RightPeople
where right.ResultId == 3
select new {left, right};