Creating outer joins

Creating outer joins

Postby Jupiler » Thu 28 Apr 2011 07:46

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
Jupiler
 
Posts: 16
Joined: Tue 12 Apr 2011 11:43

Postby AndreyR » Fri 29 Apr 2011 10:19

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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby Jupiler » Fri 06 May 2011 09:43

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.
Jupiler
 
Posts: 16
Joined: Tue 12 Apr 2011 11:43


Return to dotConnect for Oracle