SQL for fetching associations is using "JOIN" instead of "LEFT JOIN"

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.

    The associations are defined with a nullable foreign key columns in my database, and mapped in the context as nullable columns.

    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 ON (a.[SubjectGroupID] = b.[SubjectGroupID])JOIN [SubjectCohorts] AS ON (a.[SubjectCohortID] = c.[SubjectCohortID]) WHERE (a.[NameCode] LIKE '%555%' OR b.[NameLIKE '%555%' OR c.[NameLIKE '%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 JOIN [SubjectGoups] AS ON (a.[SubjectGroupID] = b.[SubjectGroupID]) LEFT JOIN [SubjectCohorts] AS ON (a.[SubjectCohortID] = c.[SubjectCohortID]) WHERE (a.[NameCode] LIKE '%555%' OR b.[NameLIKE '%555%' OR c.[NameLIKE '%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