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.
10 Answers, 1 is accepted
All the best,
Thomas
the Telerik team
Thanks in advance.
Sorry for the inconvenience,
Thomas
the Telerik team
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?
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.
Ivailo
the Telerik team
I have many queries which depend on this in my project!!!. Please help me with this.
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.
Ivailo
the Telerik team
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