Entity Framework forcing Inner Join when Left Join DefaultIfEmpty() Syntax used

c# entity-framework-6 join linq-to-entities oracle

Question

I'm trying to create a fairly complex query with the Oracle EF framework (Oracle.ManagedDataAccess.EntityFramework nuget package, version 12.1.2400) with numerous left joins, utilizing the .DefaultIfEmpty() syntax:

from i in dbHR.Identities

join p in dbHR.Personals
    on new {
        key1 = i.ID,
        key2 = true,
        key3 = true
    } equals new {
        key1 = p.EID_ID,
        key2 = (DbFunctions.TruncateTime(p.EFFECTIVE).Value.CompareTo(DbFunctions.TruncateTime(DateTime.Now).Value) <= 0),
        key3 = (DbFunctions.TruncateTime(p.EXPIRY).Value.CompareTo(DbFunctions.TruncateTime(DateTime.Now).Value) >= 0)
    }
    into pj    from p in pj.DefaultIfEmpty()

join s in dbHR.States on p.DSP_ID_ADDRESS equals s.ID into sj from s in sj.DefaultIfEmpty()

join e in dbHR.Employments
    on new {
            key1 = i.ID,
            key2 = true
        }
        equals new {
            key1 = e.EID_ID,
            key2 = (!e.TERMINATION_DATE.HasValue || DbFunctions.TruncateTime(e.TERMINATION_DATE.Value).Value.CompareTo(DbFunctions.TruncateTime(DateTime.Now).Value) >= 0)
        }
    into ej from e in ej.DefaultIfEmpty()

join a in dbHR.Assignments
    on new {
        key1 = e.ID,
        key2 = true
    } equals new {
        key1 = a.EEM_ID,
        key2 = ((!a.ASSIGNMENT_END_DATE.HasValue || DbFunctions.TruncateTime(a.ASSIGNMENT_END_DATE.Value).Value.CompareTo(DbFunctions.TruncateTime(DateTime.Now).Value) >= 0)
            && (DbFunctions.TruncateTime(a.ASSIGNMENT_START_DATE).Value.CompareTo(DbFunctions.TruncateTime(DateTime.Now).Value) <= 0)
            && (a.PRIME_ASSIGNMENT != null))
    }
    into aj from a in aj.DefaultIfEmpty()

join ad in dbHR.AssignmentDetails
    on
        new { key1 = a.ID, key2 = true }
    equals
        new
        {
            key1 = ad.EAS_ID,
            key2 = (
                DbFunctions.TruncateTime(ad.EXPIRY).Value.CompareTo(DbFunctions.TruncateTime(DateTime.Now).Value) >= 0
                && DbFunctions.TruncateTime(ad.EFFECTIVE).Value.CompareTo(DbFunctions.TruncateTime(DateTime.Now).Value) <= 0
                )
        }
    into adj from ad in adj.DefaultIfEmpty()

join j in dbHR.Jobs 
    on ad.DJB_ID equals j.ID 
    into jj from j in jj.DefaultIfEmpty()

join jd in dbHR.JobDetails 
    on new { 
        key1 = j.ID, key2 = true 
    } equals new {
        key1 = jd.DJB_ID,
        key2 =
            (
            DbFunctions.TruncateTime(jd.EFFECTIVE).Value.CompareTo(DbFunctions.TruncateTime(DateTime.Now).Value) <= 0
            && DbFunctions.TruncateTime(jd.EXPIRY).Value.CompareTo(DbFunctions.TruncateTime(DateTime.Now).Value) >= 0
            )
    }
    into jdj from jd in jdj.DefaultIfEmpty()

join d in dbHR.Departments 
    on ad.DDP_ID equals d.ID 
    into dj from d in dj.DefaultIfEmpty()

join dd in dbHR.DepartmentDetails 
    on new { 
        key1 = d.ID, key2 = true 
    } equals new {
        key1 = dd.DDP_ID,
        key2 =
            (
            DbFunctions.TruncateTime(dd.EFFECTIVE).Value.CompareTo(DbFunctions.TruncateTime(DateTime.Now).Value) <= 0
            && DbFunctions.TruncateTime(dd.EXPIRY).Value.CompareTo(DbFunctions.TruncateTime(DateTime.Now).Value) >= 0
            )
    } into ddj from dd in ddj.DefaultIfEmpty()

select new HREmployeeRecord
{
    i=i,
    p= p,
    s=s,
    e=e,
    a=a,
    ad=ad,
    j=j,
    jd=jd,
    d=d,
    dd=dd
}

The resulting SQL generated by EF has left joins on most of the tables, but 3 of them keep reverting to inner joins:

FROM          "DB"."DB_HR_IDENTITIES" "Extent1"
LEFT OUTER JOIN "DB"."DB_HR_PERSONALS" "Extent2" ON ("Extent1"."ID" = "Extent2"."EID_ID") AND [...datetime comparison removed for brevity...]
LEFT OUTER JOIN "DB"."DB_CM_STATE_PROVINCES" "Extent3" ON "Extent2"."DSP_ID_ADDRESS" = "Extent3"."ID"
LEFT OUTER JOIN "DB"."DB_HR_EMPLOYMENTS" "Extent4" ON ("Extent1"."ID" = "Extent4"."EID_ID") AND [...datetime comparison removed for brevity...]
LEFT OUTER JOIN "DB"."DB_HR_ASSIGNMENTS" "Extent5" ON ("Extent4"."ID" = "Extent5"."EEM_ID") AND [...datetime comparison removed for brevity...]
INNER JOIN "DB"."DB_HR_ASSIGNMENT_DETAILS" "Extent6" ON ("Extent5"."ID" = "Extent6"."EAS_ID") AND ((TRUNC("Extent6"."EXPIRY")) >= (TRUNC(LOCALTIMESTAMP))) AND ((TRUNC("Extent6"."EFFECTIVE")) <= (TRUNC(LOCALTIMESTAMP)))
INNER JOIN "DB"."DB_CM_JOBS" "Extent7" ON "Extent6"."DJB_ID" = "Extent7"."ID"
LEFT OUTER JOIN "DB"."DB_CM_JOB_DETAILS" "Extent8" ON ("Extent7"."ID" = "Extent8"."DJB_ID") AND [...datetime comparison removed for brevity...]
INNER JOIN "DB"."DB_CM_DEPARTMENTS" "Extent9" ON "Extent6"."DDP_ID" = "Extent9"."ID"
LEFT OUTER JOIN "DB"."DB_CM_DEPARTMENT_DETAILS" "Extent10" ON ("Extent9"."ID" = "Extent10"."DDP_ID") AND [...datetime comparison removed for brevity...]

I've tried replacing some of the joins with the following, but unfortunately it results in identical output:

from ad in dbHR.AssignmentDetails.Where(x=>
    a.ID == x.EAS_ID &&
    (
        DbFunctions.TruncateTime(x.EXPIRY).Value.CompareTo(DbFunctions.TruncateTime(DateTime.Now).Value) >= 0
        && DbFunctions.TruncateTime(x.EFFECTIVE).Value.CompareTo(DbFunctions.TruncateTime(DateTime.Now).Value) <= 0
    )

).DefaultIfEmpty()

I'm beginning to pull my hair out.

I read in this answer:

EF seems to use INNER JOIN for including a required and LEFT OUTER JOIN for including an optional navigation property.

While that makes sense, I don't think it applies to my AssignmentDetails table, as it's the Many (FK) side of a One (Assignments) to Many (AssignmentDetails) relationship, where there could possibly not be an AssignmentDetails record.

It may be true, however, for the Departments and Jobs tables, since they're the primary key table referenced by a foreign key - there should always be a record. However, this still breaks the results because if in the join the FK is on a table that was NULL due to a left join, then the entire row is then removed due to the INNER join.

Any assistance would be greatly appreciated!

1
2
5/23/2017 11:44:37 AM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Popular Answer

My mistake. I was wrapping this query in a function called GetEmployees() which returned an iQueryable list, and was filtering it:

results = HRFunctions.GetEmployees().Where(x => x.ad != null);

Because there was a WHERE clause added that assumed AD should not be null, EntityFramework intelligently altered the LEFT JOIN to an INNER JOIN.

After removing the .Where(), AssignmentDetails is now a Left Join.

2
12/15/2015 11:20:58 PM

Related Questions

Entityframework Join using lambdas and the join method

It seems there are different ways to do joins using linq. One is more straightforward and involves just joining tables like this:...var found = from c in s.categories join cm in s.categorymaps on c.CategoryId equals cm.ChildCategoryId select c; ...There is another way using lambdas and I'm having a heck of a time trying to f...
entity-framework join lambda
asked by Mason

Outer left Entity Framework through Linq To Entities using Where Clause

I have read all the posts related to implementing the equivalent of a LEFT OUTER JOIN in Linq to Entities (.NET 3.5) when using the Entity Framework, but have yet to find a solution to the following problem....Given two tables, represented by the objects below:... public class Foo { public int FooId; // primary key public st...
entity-framework left-join linq-to-entities outer-join
asked by EulerOperator

LEFT JOIN or RIGHT JOIN using LINQ Entity-Framework

Trying joining using Linq. What should i use ? Left join or right join?... APPLICANT TABLE PROFILE TABLE APPLICANT_ID|profile_id|Applicant_Name| |profile_id|Applicant_Name 1 | NULL | RAY HEAVENS | | 1 | MARK LAPID 2 | NULL | BEN TULFO | | 2 | SUPER MAN 3 | 1...
asp.net asp.net-mvc c# entity-framework linq
asked by Enrique Gil

Left Outer Join using Entity Framework and Lambda Expressions

Employee id, name, datejoin, deptID ...and another table that is...Marketing id, name, deptID ...What I am trying to do is search of deptID from Employee table in Marketing to see if it exists, if it doesn't exists want to add name and deptID from Employee table into Marketing table. Basically I believe it is left outer join. ...I am trying to do...
c# entity-framework lambda linq-to-entities
asked by J. Davidson

Entity framework Include command - Left or inner join?

As I was investigating the difference between ...Include... and ...Join... I found that : ...If the DB ...does not... include a Foreign Keys -it has no navigation props so it's better to use ...Join...If It ...does... have a navigation props - then use ...Include.... ( it also save a db hit.)...But one ...answer here... caught my attention: ...Inc...
.net-4.0 c# entity-framework linq linqpad
asked by Royi Namir

Entity Framework + Linq LEFT JOIN using a where clause?

I am using Linq with Entity Framework 5 and using query syntax (I think, please correct my terminology). I have a table of participants, and want to associate their study ID number with a logged SMS message where either the "to" or "from" number in the message matches the participant's phone number. In addition I want messages sent from (or to) a...
c# entity-framework linq mysql
asked by William T. Mallard

Entity Framework left join on multiple tables creates inner join instead

I'm trying to create a query that joins four tables together, only one of which is guaranteed to exist. I've been successful creating left joins in EF before, but never with more than one join. Here is the query:... var details = (from planInfo in context.PlanInfo join templateRec in context.ProductTemplates o...
c# entity-framework entity-framework-6 linq
asked by kindwarrior

How to do a left outer join in Entity Framework without using the query syntax?

I'm trying to convert this query using linq's query syntax to a method based syntax....Here's the query:... var products = from p in context.Products join t in context.TopSellings on p.Id equals t.Id into g from tps in g.DefaultIfEmpty() orderby tps.Rating descending ...
c# entity-framework linq
asked by Randel Ramirez

linq left join with filtering in joined

I am getting duplication meals here...IEnumerable<DTOHotMealsPrice> lst = (from m in this.dbEntity.HOT_MEALS join ml in this.dbEntity.HOT_MEALS_PRICE on m.MEALSID equals ml.MEALSID into mls from mls1 in mls.DefaultIfEmpty() where mls1.HOTID==hotelId select new DTOHotMealsPrice { MEALSID = m.MEALSID, MEALSNAME =...
c# entity-framework-6 linq
asked by safeena rasak

EF Core 2.1, force SelectMany to produce LEFT JOIN

Let's assume we've simple ...one->one->many... relation with following tables structure:...public class City { public string Name { get; set; } [Column("DtaCentralSchoolId")] [ForeignKey("MyCentralSchool")] public int? CentralSchoolId { get; set; } public CentralSchool MyCentralSchool { get; set; } } public class CentralSchool { pub...
ef-fluent-api entity-framework entity-framework-6 entity-framework-core linq
asked by Grigoryants Artem

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow