Left Join NOT Working, Help!!

[Eduardo]

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.

[Thomas]

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

[Eduardo]

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.

[Thomas]

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

[Eduardo]

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?

[Eduardo]

Anybody? Please this is really important for our project.

[Ivailo]

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

[Eduardo]

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.

[Ivailo]

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

[Alexander]

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

[Patrick]

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.