This is a migrated thread and some comments may be shown as answers.

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

0 Answers 37 Views
Development (API, general questions)
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Yijun
Top achievements
Rank 1
Yijun asked on 30 Mar 2017, 09:51 PM

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
Tags
Development (API, general questions)
Asked by
Yijun
Top achievements
Rank 1
Share this question
or