Creating outer joins

[Jupiler]

Hi,
I would like to create a query with two outer joins, my query looks like this:

Code: Select all

var queryO = from bsm in this.EFContext.BsSeedMasters
                         where bsm.Year == year && bsm.A31 == a31 && bsm.FieldName == fieldName
                         from sfd in this.EFContext.SeedFloweringDetails.Where(sfd => sfd.SeedName == bsm.SeedName).DefaultIfEmpty()
                         from sfdc in this.EFContext.SeedFloweringDetailCountings.Where(sfdc => sfd.SeedName == sfdc.SeedName).DefaultIfEmpty()
                         where sfdc.MeasureDate == measureDate
                         select bsm;

I would expect to have this underlying query:

Code: Select all

SELECT ...
  FROM MCBIS.BS_SEED_MASTER Extent1
       LEFT OUTER JOIN MCBIS.SEED_FLOWERING_DETAIL Extent2
          ON Extent2.SEED_NAME = Extent1.SEED_NAME
       LEFT OUTER JOIN MCBIS.SEED_FLOWERING_DETAIL_COUNTING Extent3
          ON Extent2.SEED_NAME = Extent3.SEED_NAME
 WHERE (((Extent1.YEAR = :p__linq__0) AND (Extent1.A31 = :p__linq__1))
        AND (Extent1.FIELD_NAME = :p__linq__2))
       AND (Extent3.MEASURE_DATE = :p__linq__3);

But instead the query following is created:

Code: Select all

SELECT ...
  FROM MCBIS.BS_SEED_MASTER Extent1
       LEFT OUTER JOIN MCBIS.SEED_FLOWERING_DETAIL Extent2
          ON Extent2.SEED_NAME = Extent1.SEED_NAME
       INNER JOIN MCBIS.SEED_FLOWERING_DETAIL_COUNTING Extent3
          ON Extent2.SEED_NAME = Extent3.SEED_NAME
 WHERE (((Extent1.YEAR = :p__linq__0) AND (Extent1.A31 = :p__linq__1))
        AND (Extent1.FIELD_NAME = :p__linq__2))
       AND (Extent3.MEASURE_DATE = :p__linq__3);

How do I get two outer joins?
Thanks,
Femke

[AndreyR]

Have a look at this question on the StackOverflow website, for example.
The solution is either to have the correct relationship setup, or to use something like GroupJoin.

[Jupiler]

Hi Andrey,
Thanks for you reply, I got around it and am using:

Code: Select all

            var qry2 = (from pr in EFContext.PrimingRuns
                        join pd in EFContext.PrimingDevices on new { pr.OrderNo, pr.RunNumber } equals new { pd.OrderNo, pd.RunNumber } into tempDevices
                        from devices in tempDevices.DefaultIfEmpty()
                        join prr in EFContext.PrimingRegistrations on new { pr.OrderNo, pr.RunNumber } equals new { prr.OrderNo, prr.RunNumber } into tempRegistrations
                        from registrations in tempRegistrations.DefaultIfEmpty()
                        select new { pr, devices, registrations});

This will create:

Code: Select all

SELECT 
...
FROM   JIT.PRIMING_RUN "Extent1"
LEFT OUTER JOIN JIT.PRIMING_DEVICE "Extent2" ON ("Extent1".ORDER_NO = "Extent2".ORDER_NO) AND ("Extent1".RUN_NUMBER = "Extent2".RUN_NUMBER)
LEFT OUTER JOIN JIT.PRIMING_REGISTRATION "Extent3" ON ("Extent1".ORDER_NO = "Extent3".ORDER_NO) AND ("Extent1".RUN_NUMBER = "Extent3".RUN_NUMBER);

Containing the two outer joins.