Joins can only involve base type or reference fields, move other conditions into the filter.

[AJ]

I'm not sure if this is a "bug" or not, but I came across this issue. Before I go on, I must add that while I've been using LINQ for some time... I've just now "dove" into creating more complex queries. For assistance/debugging, I've been using LINQPad which has been incredibly helpful. Anyway, here we go:

Here was the query I built in LINQPad:

var qry = (from c in _ctx.Cases
                       join imp in _ctx.Implanteds on c.CaseID equals imp.CaseID
                       join prod in _ctx.Products on imp.ProductID equals prod.ProductID
                       join inv in _ctx.Invoices on c.InvoiceID equals inv.InvoiceID
                       join com in _ctx.Commissions on
                        new
                            {
                                LocID = inv.LocationID,
                                CatID = prod.CategoryID,
                                SurID = c.SurgeonID,
                                RepID = salesRepID
                            }
                        equals new
                                {
                                    LocID = com.LocationID,
                                    CatID = com.CategoryID,
                                    SurID = com.SurgeonID,
                                    RepID = com.SalesRepID
                                }
                       where c.CaseID == caseID
                       select new SalesRepCommissionDue
                                {
                                    LocationID = inv.LocationID,
                                    CategoryID = prod.CategoryID,
                                    SurgeonID = c.SurgeonID,
                                    SellingPrice = imp.SellingPrice * imp.Quantity,
                                    CommissionPct = com.CommissionPercent,
                                }
                      );

This ran perfectly fine and the generated SQL looked good. However, when I moved it into Visual Studio and ran the code there it threw this error: Failure: Joins can only involve base type or reference fields, move other conditions into the filter.

I'm not sure why the error was thrown. 

However, modifying the code to the below ran fine:

var qry = (from c in _ctx.Cases
                       join imp in _ctx.Implanteds on c.CaseID equals imp.CaseID
                       join prod in _ctx.Products on imp.ProductID equals prod.ProductID
                       join inv in _ctx.Invoices on c.InvoiceID equals inv.InvoiceID
                       join com in _ctx.Commissions on
                        new
                            {
                                LocID = inv.LocationID,
                                CatID = prod.CategoryID,
                                SurID = c.SurgeonID
                            }
                        equals new
                                {
                                    LocID = com.LocationID,
                                    CatID = com.CategoryID,
                                    SurID = com.SurgeonID
                                }
                       where c.CaseID == caseID && com.SalesRepID==salesRepID
                       select new SalesRepCommissionDue
                                {
                                    LocationID = inv.LocationID,
                                    CategoryID = prod.CategoryID,
                                    SurgeonID = c.SurgeonID,
                                    SellingPrice = imp.SellingPrice * imp.Quantity,
                                    CommissionPct = com.CommissionPercent,
                                }
                      );

Again, I don't know if this is a bug or by design. But just wanted to point it out...

 - Andrew

[Thomas]

Hi Andrew,

this is currently a shortcoming which should be fixed, but it is not exactly trivial to do so.
Fortunately you found the workaround that will work only for inner joins, not outer joins.

Greetings,
Thomas
the Telerik team

[AJ]

My workaround is no longer a viable solution, as I need it to return a value of 0 if there isn't any Commission record. With the restriction above in mind, I came up with the following:

var qry = (from c in DataContext.Cases
            join imp in DataContext.Implanteds on c.CaseID equals imp.CaseID
            join prod in DataContext.Products on imp.ProductID equals prod.ProductID
            join inv in DataContext.Invoices on c.InvoiceID equals inv.InvoiceID
            join com in DataContext.Commissions on
                new
                    {
                        LocID = inv.LocationID,
                        CatID = prod.CategoryID,
                        SurID = c.SurgeonID,
                        RepID = salesRepID
                    }
                equals new
                            {
                                LocID = com.LocationID,
                                CatID = com.CategoryID,
                                SurID = com.SurgeonID,
                                RepID = com.SalesRepID
                            } into j4
            from loj in j4.Where(x => x.SalesRepID == salesRepID).DefaultIfEmpty()
            where c.CaseID == caseID
            select new SalesRepCommissionDue
                        {
                            LocationID = inv.LocationID,
                            CategoryID = prod.CategoryID,
                            SurgeonID = c.SurgeonID,
                            SellingPrice = imp.SellingPrice*imp.Quantity,
                            CommissionPct = loj == null ? 0M : loj.CommissionPercent,
                        }
            ).ToList();

return qry;

This query works as expected when I tried it out in LINQPad, but when I put it into my application running OpenAccess, I get the error "Object reference not set to an instance of an object."

Since I'm just going to move this into a stored procedure, I just wanted to make you aware of this issue. However, I would be more than happy to help you debug this issue if need be.

Thanks,
Andrew

[Viktor Zhivkov]

Hi Andrew,

Thank you for the detailed query and sharing your considerations and ideas.
We will add an equivalent query to our LINQ test suite and we will do out best to improve our support for such complex ones.
When we have any positive outcome I will let you know.

Kind regards,
Viktor Zhivkov
the Telerik team

[Eduardo]

I am interested in this same issue, because i have this query:

                IQueryable<WarehouseDtoForList> query =
                        from A in dbContext.Warehouses

                        join J1 in dbContext.SystemUsers on new {  i_InsertUserId = A.i_InsertUserId.Value, i_NodeId = 1 }
                          equals new { i_InsertUserId = J1.i_PersonId, J1.i_NodeId } into J1_join
                        from J1 in J1_join.DefaultIfEmpty()  
                      
                        join J2 in dbContext.SystemUsers on new { i_UpdateUserId = A.i_UpdateUserId.Value, i_NodeId = 1 }
                          equals new { i_UpdateUserId = J2.i_PersonId, J2.i_NodeId } into J2_join
                        from J2 in J2_join.DefaultIfEmpty()                        
                        where
                          A.i_OrganizationId.Value == pintOrganizationId
                        select new WarehouseDtoForList
                        {
                            WarehouseId =  A.i_WarehouseId,
                            Warehouse = A.v_Name,
                            CreationUser = J1.v_UserName,
                            CreationDate = A.d_InsertDate,
                            UpdateUser = J2.v_UserName,
                        };

And i get the same error, but when i try with linqpad it works!!!. With OpenAccess it does not work.
Please some help!!

[Viktor Zhivkov]

Hi Eduardo,

Any further communication about this issue will appear in the other thread that you have created specifically for your question.
Please refer to http://www.telerik.com/community/forums/orm/linq-questions/left-join-not-working-help.aspx for further discussions.

Greetings,
Viktor Zhivkov
the Telerik team

[Eduardo]

Hi, thanks for your response, but i still do not see any useful answer.

[Ivailo]

Hi,

To get the information to this thread as well - this LINQ functionality discussed is missing in OpenAccess but is already planned for implementation. 

Further information will be posted in the thread mentioned above once there is an OpenAccess release containing the new join scenario.

Kind regards,
Ivailo
the Telerik team