How to force the generated sql to use "LEFT OUTER JOIN" instead of "JOIN" for nullable Associations?

Thread is closed for posting
1 posts, 0 answers
  1. Yijun
    Yijun avatar
    5 posts
    Member since:
    Feb 2013

    Posted 30 Mar 2017 Link to this post

    Hello I am using a DataModelFluentMetadataSource where all the metadata is defined.

    This is my LINQ:

    Context.Subjects.Where(x => x.NameCode.Contains(search) || (x.SubjectGroup == null ? false : x.SubjectGroup.Name.Contains(search)) || (x.SubjectCohort == null ? false : x.SubjectCohort.Name.Contains(search)));

     

    This is my generated SQL:

    SELECT  a.[SubjectID] AS COL1, a.[IsActive] AS COL2, a.[NameCode] AS COL3, a.[SubjectCohortID] AS COL4, a.[SubjectGroupID] AS COL5 FROM [Subjects] a JOIN [SubjectGoups] AS b ON (a.[SubjectGroupID] = b.[SubjectGroupID]) JOIN [SubjectCohorts] AS c ON (a.[SubjectCohortID] = c.[SubjectCohortID]) WHERE (a.[NameCode] LIKE '%555%' OR b.[Name] LIKE '%555%' OR c.[Name] LIKE '%555%') ORDER BY COL1

     

    This SQL does not return all the records that match the search.

    This is how it should be:

    SELECT  a.[SubjectID] AS COL1, a.[IsActive] AS COL2, a.[NameCode] AS COL3, a.[SubjectCohortID] AS COL4, a.[SubjectGroupID] AS COL5 FROM [Subjects] a LEFT OUTER JOIN [SubjectGoups] AS b ON (a.[SubjectGroupID] = b.[SubjectGroupID]) LEFT OUTER JOIN [SubjectCohorts] AS c ON (a.[SubjectCohortID] = c.[SubjectCohortID]) WHERE (a.[NameCode] LIKE '%555%' OR b.[Name] LIKE '%555%' OR c.[Name] LIKE '%555%') ORDER BY COL1

     

    This SQL returns all records correctly.

    How can I force it to use "LEFT OUTER JOIN"?

    Any help would be appreciated.

     

    Regards,

    Yijun

     

Back to Top