How do I reference joined table columns in where clause with linq?

Question

I have the following query that I'm trying to translate into a linq statement:

SELECT DISTINCT
    rc.RoleCatID, rc.RoleCategoryDescription
FROM 
    dbo.[Role] r
INNER JOIN 
    dbo.Employee e ON r.RoleID = e.RoleID
INNER JOIN 
    dbo.RoleCategory rc ON r.RoleCatID = rc.RoleCatID
INNER JOIN 
    dbo.Product p ON rc.ProductID = p.ProductID
WHERE 
    p.ChannelID = '123456'
    AND (p.[ProductID] = 'abc' OR p.[ProductID] = 'def' OR p.[ProductID] = 'ghi')

I have the below so far, but I can't reference anything beyond the original table's fields in the where clause. Intermediate selects haven't helped either.

db.Roles.Join(db.Employees, r => r.RoleID, e => e.RoleID, (r, e) => r)
    .Join(db.RoleCategories, r => r.RoleCatID, rc => rc.RoleCatID, (r, rc) => r)
    .Join(db.Products, rc => rc.ProductID, p => p.ProductID, (rc, p) => rc)
    .Where(p => p.ChannelID == "123456" && (p.ProductID == "abc" || p.ProductID == "def" || p.ProductID == "ghi")); // <-- this line fails

It can't see any of the Product table's fields and treats p as Role object from the Role table. The last join is also compromised as it should be:

.Join(db.Products, rc => rc.ProductID, p => p.ProductID, (rc, p) => rc)

However rc is treated as a Role object instead of a RoleCategory object.

I prefer translating SQL to fluent syntax, perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) would help you. BTW, you are removing access to previous table rows in each `Join` by doing e.g. `(r,rc)=>r` instead of `(r,rc)=>new { r,rc }`. — NetMage, Oct 02 '18 at 18:45
@NetMage thanks for the tip re: `(r, rc) => new {r, rc}`. I'm always torn on fluent syntax. I don't like it because it doesn't really look like c# but also isn't exactly SQL. However, lambda's rapidly turn into a mess beyond the most basic queries. — Legion, Oct 02 '18 at 18:53
I switch between them fine, but I find `join` and `group` often clearer in fluent syntax, and `let` more convenient (though perhaps hiding the cost) then adding a `Select` to create a new anonymous object. — NetMage, Oct 02 '18 at 19:17

score 1 · Accepted Answer · answered Oct 02 '18 at 19:13

The resultset of the Join method is selected in that last lambda expression.

In this case, you are accepting r and e, which are objects from the two tables, and returning just the r:

.Join(db.Employees, r => r.RoleID, e => e.RoleID, (r, e) => r)

If you want e you have to return it from the expression as well:

.Join(db.Employees, r => r.RoleID, e => e.RoleID, (r, e) => new { Role = r, Employee = e})

Now Join is returning an anonymous type with two member: Role and Employee. And you can access either of them in any subsequent Where or Select clause.

How do I reference joined table columns in where clause with linq?

1 Answers1