This question is locked. New answers and comments are not allowed.
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
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
JOIN
[SubjectGoups]
AS
b
ON
(a.[SubjectGroupID] = b.[SubjectGroupID])
LEFT
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