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

8 posts, 0 answers
  1. AJ
    AJ avatar
    33 posts
    Member since:
    Jun 2011

    Posted 30 May 2012 Link to this post

    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
  2. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 04 Jun 2012 Link to this post

    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
    Follow @OpenAccessORM Twitter channel to be the first one to get the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
  3. DevCraft banner
  4. AJ
    AJ avatar
    33 posts
    Member since:
    Jun 2011

    Posted 17 Aug 2012 Link to this post

    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
  5. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 22 Aug 2012 Link to this post

    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
    Follow @OpenAccessORM Twitter channel to be the first one to get the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
  6. Eduardo
    Eduardo avatar
    9 posts
    Member since:
    Feb 2009

    Posted 12 Feb 2013 Link to this post

    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!!
  7. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 15 Feb 2013 Link to this post

    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
    Q3'12 SP1 of OpenAccess ORM packs Multi-Table Entities mapping support. Check it out.
  8. Eduardo
    Eduardo avatar
    9 posts
    Member since:
    Feb 2009

    Posted 15 Feb 2013 Link to this post

    Hi, thanks for your response, but i still do not see any useful answer.
  9. Ivailo
    Admin
    Ivailo avatar
    318 posts

    Posted 15 Feb 2013 Link to this post

    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
    Q3'12 SP1 of OpenAccess ORM packs Multi-Table Entities mapping support. Check it out.
Back to Top
DevCraft banner