Unknown column in' where clause'- Converting MySQL query to LINQ

Question

I am trying to convert MySQL query to LINQ using LINQPad

MySQL Query

Select m.Id
from monthly as m
left join party as p on p.Id = m.PartyId
left join partymap as sp on sp.PositionId = m.PositionId and p.Id = sp.PartyId

LINQ Query

(
     from m in monthly     
     join p in party on m.PartyId equals p.Id into part
        from p in part.DefaultIfEmpty()
     join sp in partymap on m.PositionId equals sp.PositionId into spPart
        from sp in spPart.Where(sp => sp.PartyId == p.Id).DefaultIfEmpty()
     select new 
     { 
        m.Id
     }
)

Executing this query in LINQPad is giving below error

Message: Unknown column 'part.Id' in 'where clause'

What I am doing wrong here? The second condition p.Id = sp.PartyId is added in where clause as it is. I tried switching the conditions. The error is at p.Id. If I give any hardcode value in place of p.Id, it works well.

Am I missing anything here?

score 0 · Answer 1 · answered Apr 16 '23 at 18:11

I got the answer after going through the same posts. Below is my LINQ query-

(
     from m in monthly     
     join p in party on m.PartyId equals p.Id into part
        from p in part.DefaultIfEmpty()
     join sp in partymap on new {m.PositionId, sp.PartyId } equals new{ sp.PositionId, PartyId = p.Id } into spPart
        from sp in spPart.DefaultIfEmpty()
     select new 
     { 
        m.Id
     }
)

Here names in the conditions should match

Unknown column in' where clause'- Converting MySQL query to LINQ

1 Answers1