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

[Yijun]

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