Hi,

I have another problem yet. I would like to use left join in my query as below

var q = (from ua in metaData.Useraccount
                    join inner in
                        (from ugr in metaData.Usergrouprel
                         join ug in metaData.Usergroup on ugr.Idusergroup equals ug.Idusergroup into joinQi                     
                         from QiJoined in joinQi.DefaultIfEmpty()
                         select ugr) on ua.Iduseraccount equals inner.Iduseraccount into joinQ
                    from QJoined in joinQ.DefaultIfEmpty()
                    select ua);

                IQueryable<UseraccountEntity> query = q
                    .WithPath<UseraccountEntity>(uaPath => uaPath.Prefetch<UsergrouprelEntity>(ua => ua.Usergrouprel)
                        .SubPath<UsergrouprelEntity>(ugrPath => ugrPath.Prefetch<UsergroupEntity>(ugr => ugr.Usergroup)));
                List<UseraccountEntity> ualist = query.ToList<UseraccountEntity>();

I used in query joins because I need to add conditions. And left joins are important for me too. Linq query produce sql queries as below

SELECT "TEST_1"."USERGROUPREL"."IDUSERGROUPREL" AS "Idusergrouprel",
       "TEST_1"."USERGROUPREL"."IDUSERACCOUNT"  AS "Iduseraccount",
       "TEST_1"."USERGROUPREL"."IDUSERGROUP"    AS "Idusergroup"
  FROM ("TEST_1"."USERGROUPREL" "LPA_L3" LEFT JOIN "TEST_1"."USERGROUP"
        "LPA_L4" ON "LPA_L3"."IDUSERGROUP" = "LPA_L4"."IDUSERGROUP")
        
SELECT DISTINCT "LPA_L2"."IDUSERACCOUNT"       AS "Iduseraccount",
                "LPA_L2"."MAIL"             AS "Mail",
                "LPA_L2"."CACHEDPASS"         AS "Cachedpass",
                "LPA_L2"."LOGINDATE"           AS "Logindate",
                "LPA_L2"."CREATEDBY"           AS "Createdby",
                "LPA_L2"."CREATEDDATE"       AS "Createddate",
                "LPA_L2"."MODIFIEDBY"         AS "Modifiedby",
                "LPA_L2"."MODIFIEDDATE"     AS "Modifieddate",
                "LPA_L2"."DELETEDBY"           AS "Deletedby",
                "LPA_L2"."DELETEDDATE"       AS "Deleteddate",
                "LPA_L2"."FULLNAME"         AS "Fullname",
                "LPA_L2"."PHONE"               AS "Phone",
                "LPA_L2"."POSTALCODE"         AS "Postalcode",
                "LPA_L2"."PAGER"               AS "Pager",
                "LPA_L2"."CITY"             AS "City",
                "LPA_L2"."FIRSTNAME"           AS "Firstname",
                "LPA_L2"."COUNTRY"           AS "Country",
                "LPA_L2"."SURNAME"           AS "Surname",
                "LPA_L2"."FAX"               AS "Fax",
                "LPA_L2"."MOBILE"             AS "Mobile",
                "LPA_L2"."STREET"             AS "Street"
  FROM ((SELECT "TEST_1"."USERGROUPREL"."IDUSERGROUPREL" AS "Idusergrouprel",
                "TEST_1"."USERGROUPREL"."IDUSERACCOUNT"  AS "Iduseraccount",
                "TEST_1"."USERGROUPREL"."IDUSERGROUP"   AS "Idusergroup"
           FROM ("TEST_1"."USERGROUPREL" "LPA_L3" LEFT JOIN
                 "TEST_1"."USERGROUP" "LPA_L4" ON
                 "LPA_L3"."IDUSERGROUP" = "LPA_L4"."IDUSERGROUP")) "LPA_L1"
        RIGHT JOIN "TEST_1"."USERACCOUNT" "LPA_L2" ON
        "LPA_L2"."IDUSERACCOUNT" = "LPA_L1"."Iduseraccount")

There is problem with aliases. They should be used instad of full table name. One left outer join in query works ok, two doesn't work. Inner joins work fine.

lib's version 2.6.0.0.12112008

Thanks in advance

Just to be on the safe side, would you please upgrade to the latest runitme build and see if it works.

lib was upgraded (2.6.0.0.01162009). The problem still exists.

As we need to reproduce the problem here, I presume it's Oracle you're using? If you don't use the WithPath, so just the main query, do you see the same problem?

Yes, I'm using Oracle db. When I don't use WithPath error is the same.

Thanks. I think I understand the entities, but to be sure I don't waste time on testing a situation that's not yours: UserAcount 1:n UserGroupRel m:1 UserGroup ? If so, why do you need left joins towards UserGroupRel, as it seems there shouldn't be a UserGroupRel with any of its FK fields being NULL (or I might not understand the model).

Please post as much info you can give on the entities/tables involved so we can be sure we're testing the same scenario.

I can reproduce it with this query:

[Test]
public void NestedDefaultIfEmptyInNestedQueryInJoin()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        var q = from o in metaData.Order
                join inner in
                    (from od in metaData.OrderDetail
                     join p in metaData.Product on od.ProductId equals p.ProductId into joinOdp
                     from odpJoined in joinOdp.DefaultIfEmpty()
                     select od) on o.OrderId equals inner.OrderId into joinOInner
                from oInnerJoined in joinOInner.DefaultIfEmpty()
                select o;

        foreach(var v in q)
        {
        }
    }
}

(OrderDetail is intermediate entity in m:n relationship between Order and Product).

Looking into the reason why the aliases are missing.

I fixed it (see attached dll).

there's still a known issue with these kind of queries: if you add a where clause to the nested query it might go wrong. We are aware of this, though we actually don't know how to fix this properly without re-writing parts of the linq provider. Luckily there are workarounds by using references to related entities instead (which result in left/right joins if hte FK field is nullable). We've planned to partly rewrite the Join + groupjoin + defaultifempty parts for v3 as many issues we've ran into are actually centered around these three elements.

The main problem is that with nested groupjoins (the join + into clause) combined with joins AND defaultifempty AND nested where clauses requires that all joins will eventually become a single set of joins and therefore all where clauses etc. have to be moved outside that area. As you can see you currently get a nested select statement. This is not really what should be done, it should be 1 FROM with 2 joins. It's cumbersome to get this right as the issue only pops up in small number of situations and they're all very complex (huge expression tree) so analysis what should be done is hard. But I hope we'll find a way to fix this properly in v3 with a partial rewrite.