Type inference error with inner join on multiple columns in linq query

Question

I am trying to join a single column from one table to 2 columns from another table using inner join but getting error as shown below :

My classes are defined as below :

public class Employee
{
    public int Id { get; set; }

    public int DepartmentId { get; set; }
}

public class LeaveRequest
{
    public int Id { get; set; }

    public int SentFromEmployeeId { get; set; }
    public int SentToEmployeeId { get; set; }

    public virtual ICollection<LeaveUpdateLogs> LeaveUpdateLogs { get; set; }
}

public class LeaveUpdateLogs 
{
    public int Id { get; set; }

    public DateTimeOffset RequestedDate { get; set; }

    public int LeaveRequestId { get; set; }

    public string Status { get; set; }

    public virtual LeaveRequest LeaveRequest { get; set; }
}

Sql Query :

    select SentFromEmployeeId,SentToEmployeeId,* from LeaveUpdateLogs l
    inner join LeaveRequest lr on l.LeaveRequestId = lr.Id
    inner join Employee e1 on e1.Id = lr.SentFromEmployeeId or e1.Id = lr.SentToEmployeeId
    where (l.RequestedDate >= '2021-11-01' and l.RequestedDate < '2021-11-16')
        and (e1.DepartmentId =100 or e2.DepartmentId = 100)

Linq:

var query = from l in context.LeaveUpdateLogs
            join e in context.Employee
            on new { l.LeaveRequest.SentFromEmployeeId, l.LeaveRequest.SentToEmployeeId }
            equals new { Id1 = e.Id, Id2 = e.Id } into j
            where  j.DepartmentId== 100

Error : Type of expression in one of the join clause is incorrect. Type inference failed in the call to 'groupjoin'

Can someone please help me with this?

Why not just wire up navigation properties for the `SentFrom/ToEmployeeId` and then you can get EF to do the joins — Caius Jard, Nov 15 '21 at 21:05
@CaiusJard Currently the model is designed like this and I cant update it, so that's the issue — I Love Stackoverflow, Nov 15 '21 at 21:06
`new { l.LeaveRequest.SentFromEmployeeId, l.LeaveRequest.SentToEmployeeId }` if one or both of them are nullable or different type then the `Id` property on the other table you need to cast properties into correct type in the second anonymous object :`{ Id1 = (int?)e.Id, Id2 = (int?)e.Id }` — Eldar, Nov 15 '21 at 21:07
@Eldar Both SentFromEmployeeId and SentToEmployeeId are not nullable — I Love Stackoverflow, Nov 15 '21 at 21:09
This does seem a bit odd; you want to know the list of all the leaves that a manager has approved for someone else or taken themselves? (It feels like two queries, not one.. Or if I was doing it in e.g. straight SQL I'd join Employee in twice, not OR it) — Caius Jard, Nov 15 '21 at 21:09
@CaiusJard I want to get data for both "Sent" and "Received" request and only that either of those employee are from a given department. So I want to get the data for particular department and select only those SentFromEmployeeId and SentToEmployeeId which belongs to department 100 — I Love Stackoverflow, Nov 15 '21 at 21:11
@Eldar My Sql query is working fine so want to translate that query in to equivalent linq query. I am having hard time converting that SQL query to equivalent linq — I Love Stackoverflow, Nov 15 '21 at 21:12
Can you post the whole query pls. I can't see even some brakets — Serge, Nov 15 '21 at 21:13
if lr.SentFromEmployeeId || lr.SentToEmployeeId are not nullable then why do you have an Or condition? It seems like it should be an and. I am researching if it is possible to have an or linq condition for multiple columns. so far no luck — Golden Lion, Nov 15 '21 at 23:17

Type inference error with inner join on multiple columns in linq query

0 Answers0