Left Join NOT Working, Help!!

11 posts, 0 answers
  1. Eduardo
    Eduardo avatar
    9 posts
    Member since:
    Feb 2009

    Posted 12 Feb 2013 Link to this post

    Hi, when i execute my linq query in LinqPad, everything is fine and works as expected. This is my query:
    var 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, i_NodeId = 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, i_NodeId = J2.i_NodeId } into J2_join
        from J2 in J2_join.DefaultIfEmpty()
     
        where A.i_OrganizationId == 0
        select new
        {
            A.i_WarehouseId,
            A.v_Name,
            J1.v_UserName,
            A.d_InsertDate,
            Column1 = J2.v_UserName,
            A.d_UpdateDate
        };

    And also, the SQL generated by the LinqPad is correct:
    -- Region Parameters
    DECLARE @p0 Int = 1
    DECLARE @p1 Int = 1
    DECLARE @p2 Int = 0
    -- EndRegion
    SELECT [t0].[i_WarehouseId] AS [I_WarehouseId], [t0].[v_Name] AS [V_Name], [t1].[v_UserName] AS [v_UserName], [t0].[d_InsertDate] AS [D_InsertDate], [t2].[v_UserName] AS [Column1], [t0].[d_UpdateDate] AS [D_UpdateDate]
    FROM [Warehouse] AS [t0]
    LEFT OUTER JOIN [SystemUser] AS [t1] ON (([t0].[i_InsertUserId]) = [t1].[i_PersonId]) AND (@p0 = [t1].[i_NodeId])
    LEFT OUTER JOIN [SystemUser] AS [t2] ON (([t0].[i_UpdateUserId]) = [t2].[i_PersonId]) AND (@p1 = [t2].[i_NodeId])
    WHERE [t0].[i_OrganizationId] = @p2

    But, when i try to execute with OpenAccess, i got this exception:

    Exception: Joins can only involve base type or reference fields, move other conditions into the filter. (LiteralNode@2393581 LONG 1)

    I am stuck with this, and i really need some help about it. By the way i am using the latest OpenAccess version (Q3_2012_SP1).
    Thanks in advance.
  2. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 13 Feb 2013 Link to this post

    Please use the upcoming version Q1 2013 (due within 10 days). This version will have support for non-column join conditions.

    All the best,
    Thomas
    the Telerik team
    Q3'12 SP1 of OpenAccess ORM packs Multi-Table Entities mapping support. Check it out.
  3. DevCraft banner
  4. Eduardo
    Eduardo avatar
    9 posts
    Member since:
    Feb 2009

    Posted 13 Feb 2013 Link to this post

    Hi, thanks for your reply, but i need a workaround at this moment. How can i modify my linq query in order to work with this OpenAccess version?

    Thanks in advance.
  5. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 13 Feb 2013 Link to this post

    I think you will need to wait for the fix. I do not see a practical workaround this time.

    Sorry for the inconvenience,
    Thomas
    the Telerik team
    Q3'12 SP1 of OpenAccess ORM packs Multi-Table Entities mapping support. Check it out.
  6. Eduardo
    Eduardo avatar
    9 posts
    Member since:
    Feb 2009

    Posted 13 Feb 2013 Link to this post

    It is a really bad thing that OpenAccess can not handle this kind of basic queries. I think you should make a better design / programming of your product before releasing to the public.

    So, i have to wait 10 additional days for a really basic functionality (support Left Joins with Linq) that many others ORM already support. Also, how do you make sure that my linq query will work? do you guarantee that with the new version?

    When is the exact date of the upcoming version will be available?
  7. Eduardo
    Eduardo avatar
    9 posts
    Member since:
    Feb 2009

    Posted 15 Feb 2013 Link to this post

    Anybody? Please this is really important for our project.
  8. Ivailo
    Admin
    Ivailo avatar
    318 posts

    Posted 15 Feb 2013 Link to this post

    Hi Eduardo,

    Please accept our apologies for the misleading answer. This feature implementation is planned to be released for the Service Pack of Q1 release, not the Q1 release itself. The date is not fixed yet.

    For the time being the only way to perform those joins is by using custom queries through our ADO API - the other option being filtering the data in-memory.

    Once the new feature gets into the product, we will test it with your query as well to make sure that it is running fine, and I will make sure we notify you in this thread about the release date.


    All the best,
    Ivailo
    the Telerik team
    Q3'12 SP1 of OpenAccess ORM packs Multi-Table Entities mapping support. Check it out.
  9. Eduardo
    Eduardo avatar
    9 posts
    Member since:
    Feb 2009

    Posted 01 Mar 2013 Link to this post

    I have downloaded the Q1 2013 release and this is still NOT working. When is the SP1 of the Q1 available?

    I have many queries which depend on this in my project!!!. Please help me with this.
  10. Ivailo
    Admin
    Ivailo avatar
    318 posts

    Posted 07 Mar 2013 Link to this post

    Hello Eduardo,

    As per the current plans, the Q1 2013 SP1 release of OpenAccess ORM is expected in mid-April. I will make sure we inform you if there are any changes of plans and we deliver it earlier or later than this time-frame. Note that the SP1 release will contain the joins functionality we are discussing.


    All the best,
    Ivailo
    the Telerik team
    OpenAccess ORM Q1 2013 is out featuring Multi-Diagrams, Persistent Data Stream Support and much more. Check out all of the latest highlights.
  11. Alexander
    Admin
    Alexander avatar
    727 posts

    Posted 09 Apr 2013 Link to this post

    Hello Eduardo,

    In the latest internal build of OpenAccess (version 2013.1.404.2) we have added support for joins on non-column conditions like constants or expressions. This means that you will be able to execute queries like this:

    EntitiesModel1 context = new EntitiesModel1();
      
    var query = from od in context.OrderDetails
                join o in context.Orders
                    on new
                    {
                        od.OrderID,
                        A = true,
                        B = true
                    }
                    equals new
                    {
                        o.OrderID,
                        A = o.OrderDate >= new DateTime(1996, 1, 1),
                        B = o.OrderDate <= new DateTime(1998, 1, 1)
                    } into g
                from x in g.DefaultIfEmpty()
                select new { od, x };
      
    var result = query.ToList();

    What is still under development is moving any Where conditions of the joined extent (Orders in this example) to the ON clause in case of outer joins, as it requires a more complex implementation. For the moment however, you should be able to use similar syntax to the one in the example, by specifying the filters within the join.
    Hope that helps.

    Regards,
    Alexander
    the Telerik team
    Using Encrypted Connection Strings with Telerik OpenAccess ORM. Read our latest blog article >>
  12. Patrick
    Patrick avatar
    1 posts
    Member since:
    Sep 2013

    Posted 07 Oct 2014 in reply to Alexander Link to this post

    Just thought I would add this for those developers running into LINQ issues. You always have an alternative of writing a SQL View to your SQL database (assuming it is a SQL database). If needed you can also create a stored procedure if you need to pass special parameters other than a basic where clause. SQL handles left outer joins easily.
Back to Top
DevCraft banner