The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join'.- Linq JOIN with multiple condition

Question

This might get closed for a possible duplicate of this: The type of one of the expressions in the join clause is incorrect in Entity Framework But I would request to not close this without reading this whole problem.

Although it might seem to be a duplicate question, I would request to give this a read and help if possible. I have seen the solutions to the same question I linked, it is described that the property type and the name must match. I carefully read the solutions over there and made sure mine was following each of them.

However, I could not find any property type and name mismatch error in my code. So I am guessing there is something else that I am missing. Hence looking for help creating a separate question.

So I am trying to write LINQ Join with Multiple Conditions in On Clause. I have written them making sure the object's properties are of same type and name. Nevertheless I am getting the infamous misleading error - The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join'.

Following is my code:

              results = from i in context.Invoices.AsNoTracking()
                        join iwr in context.InvoiceWorkOrderRelations on i.ID equals iwr.InvoiceId
                        join w in context.WorkOrders on iwr.WorkOrderId equals w.ID
                        join scr in context.StationCompanyRelations 
                            on new { Company = w.CompanyID ?? -1, Station = w.RepairLocation ?? -1 } equals new { Comapny = (int)?scr.CompanyID, Station= (int)?scr.StationID }
    
                        where i.IsDeleted == false
                    && (companyID == Company.SYSTEMCOMPANYID || i.CompanyId == companyID)
                        orderby i.ID descending
                        select new DTO.InvoiceQuickView
                        {
                            ID = i.ID,
                            ....
                            ShopCode = scr.ShopCode
                        };

CompanyID and RepairLocation from WorkOrders context both are of type int?, hence, I type casted the relation table and stringly provided names for each property just to make sure.

Also to handle null values, I added w.CompanyID ?? -1. The error remains with or without this null checking.

Answer 1

Well after a lot of trying and searching I found the solution. Thanks to @Meysam Asadi who brought a point about typecasting the anonymous types inside linq, but to add to that statement type names must also match. I found out the issue with his solution was the second property of both object (RepairLocation and StationID) are not same and that's why compiler was still throwing the same error.

I don't know why the other solutions were sugesting typecasting though. In my case it is the typecasting that was mainly causing this error.

                    join w in context.WorkOrders on iwr.WorkOrderId equals w.ID
                    join scr in context.StationCompanyRelations on 
                       new { Company = w.CompanyID ?? -1, Station = w.RepairLocation ?? -1 } equals 
                       new { Comapny = scr.CompanyID, Station= scr.StationID }

So, while writing linq to join tables with multiple condition on ON clause:-

You must match the property names and you must handle for nullable values if one propertiy from one table is nullable whilst the other one is not null.

Answer 2

You can not use conversion or direct conversion functions inside the linq expression, which must be anonymous.

To join two tables with two parameters, both fields must have the same type I below your code:

 results = from i in context.Invoices.AsNoTracking()
                      join iwr in context.InvoiceWorkOrderRelations on i.ID equals iwr.InvoiceId
                      join w in context.WorkOrders on iwr.WorkOrderId equals w.ID
                      join scr in context.StationCompanyRelations
                          on new { w.CompanyID, w.RepairLocation } equals new { scr.CompanyID, scr.StationID }

                      where i.IsDeleted == false
                  && (companyID == Company.SYSTEMCOMPANYID || i.CompanyId == companyID)
                      orderby i.ID descending
                      select new DTO.InvoiceQuickView
                      {
                          ID = i.ID,
                          ....ShopCode = scr.ShopCode
                      };

in line code

join scr in context.StationCompanyRelations
                          on new { w.CompanyID, w.RepairLocation } equals new { scr.CompanyID, scr.StationID }

The compiler automatically compares the first field of the first anonymous linq expression with the same field in the second expression, and also compares the second field of the first anonymous link expression with the second anonymous linq expression with the second field.

Use DefaultIfEmpty if you want the values to match if they are empty. The following code shows how to do it.

 results = from i in context.Invoices.AsNoTracking()
                      join iwr in context.InvoiceWorkOrderRelations on i.ID equals iwr.InvoiceId
                      join w in context.WorkOrders on iwr.WorkOrderId equals w.ID
                      join scr in context.StationCompanyRelations
                          on new { w.CompanyID, w.RepairLocation } equals new { scr.CompanyID, scr.StationID }
                          into lj
                      where i.IsDeleted == false
                  && (companyID == Company.SYSTEMCOMPANYID || i.CompanyId == companyID)
                      orderby i.ID descending
                     from x in lj.DefaultIfEmpty()
                      select new DTO.InvoiceQuickView
                      {
                          ID = i.ID,
                          ....ShopCode = scr.ShopCode
                      };