Hi,
Please refer to my previous post titled 'Query Criteria and Eager Loading'.
I am having a similiar problem when conducting a seeming simple query like:
do_HAI.Status != 100 and do_HAI.Status != 4000
Please pay special attention to the WHERE clauses in the Eager Load queries.
I get this SQL generated:
SELECT
[do_HAI].[Id],
[do_HAI].[ED],
[do_HAI].[CreatedOn],
[do_HAI].[DeletedOn],
[do_HAI].[LockVersion],
[do_HAI].[UpdatedOn],
[do_HAI].[AssignedToId],
[do_HAI].[Class],
[do_HAI].[Comment],
[do_HAI].[Completed],
[do_HAI].[CompletionComment],
[do_HAI].[PreformAtDate],
[do_HAI].[PreformAtPeriod],
[do_HAI].[Status],
[do_HAI].[PatientId],
[do_HAI].[ProspectId],
[do_HAI].[CallbackReason],
[do_HAI].[InterestedIn],
[do_HAI].[InterestedInOther],
[do_HAI].[PreferredPhone],
[do_HAI].[DeliveryBy]
FROM
[do_HAI]
WHERE
(([do_HAI].[Status] <> 100 AND [do_HAI].[Status] <> 4000) AND [do_HAI].[DeletedOn] IS NULL);
SELECT
[do_Employee].[Id],
[do_Employee].[ED],
[do_Employee].[CreatedOn],
[do_Employee].[DeletedOn],
[do_Employee].[LockVersion],
[do_Employee].[UpdatedOn],
[do_Employee].[DOB],
[do_Employee].[Gender],
[do_Employee].[PreferredContact],
[do_Employee].[Address_EmergencyId],
[do_Employee].[Address_HomeId],
[do_Employee].[Address_WorkId],
[do_Employee].[Email_PrimaryId],
[do_Employee].[EmergContact],
[do_Employee].[EmergType],
[do_Employee].[MedEntity_PrimaryId],
[do_Employee].[Name_PrimaryId],
[do_Employee].[Phone_EmergencyId],
[do_Employee].[Phone_HomeId],
[do_Employee].[Phone_MobileId],
[do_Employee].[Phone_WorkId],
[do_Employee].[Race],
[do_Employee].[Site_PrimaryId],
[do_Employee].[Status]
FROM
[do_Employee]
WHERE
(((EXISTS (
SELECT
[do_HAI].*
FROM
[do_HAI]
WHERE
[do_HAI].[AssignedToId] = [do_Employee].[Id] AND
[do_HAI].[Status] <> 100
) AND EXISTS (
SELECT
[do_HAI].*
FROM
[do_HAI]
WHERE
[do_HAI].[AssignedToId] = [do_Employee].[Id] AND
[do_HAI].[Status] <> 4000
)) AND EXISTS (
SELECT
[do_HAI].*
FROM
[do_HAI]
WHERE
[do_HAI].[AssignedToId] = [do_Employee].[Id] AND
[do_HAI].[DeletedOn] IS NULL
)) AND [do_Employee].[DeletedOn] IS NULL);
SELECT
[do_Employee_Name].[Id],
[do_Employee_Name].[ED],
[do_Employee_Name].[CreatedOn],
[do_Employee_Name].[DeletedOn],
[do_Employee_Name].[LockVersion],
[do_Employee_Name].[UpdatedOn],
[do_Employee_Name].[Invalid],
[do_Employee_Name].[Status],
[do_Employee_Name].[First],
[do_Employee_Name].[Full],
[do_Employee_Name].[FullAuto],
[do_Employee_Name].[FullAuto_NonPrefixed],
[do_Employee_Name].[Full_Exact],
[do_Employee_Name].[Last],
[do_Employee_Name].[Middle],
[do_Employee_Name].[Prefix],
[do_Employee_Name].[Suffix],
[do_Employee_Name].[Type],
[do_Employee_Name].[EntityId]
FROM
[do_Employee_Name]
WHERE
(((EXISTS (
SELECT
[do_Employee].*
FROM
[do_Employee]
WHERE
[do_Employee].[Id] = [do_Employee_Name].[EntityId] AND
EXISTS (
SELECT
[do_HAI].*
FROM
[do_HAI]
WHERE
[do_HAI].[AssignedToId] = [do_Employee].[Id] AND
[do_HAI].[Status] <> 100
)
) AND EXISTS (
SELECT
[do_Employee].*
FROM
[do_Employee]
WHERE
[do_Employee].[Id] = [do_Employee_Name].[EntityId] AND
EXISTS (
SELECT
[do_HAI].*
FROM
[do_HAI]
WHERE
[do_HAI].[AssignedToId] = [do_Employee].[Id] AND
[do_HAI].[Status] <> 4000
)
)) AND EXISTS (
SELECT
[do_Employee].*
FROM
[do_Employee]
WHERE
[do_Employee].[Id] = [do_Employee_Name].[EntityId] AND
EXISTS (
SELECT
[do_HAI].*
FROM
[do_HAI]
WHERE
[do_HAI].[AssignedToId] = [do_Employee].[Id] AND
[do_HAI].[DeletedOn] IS NULL
)
)) AND [do_Employee_Name].[DeletedOn] IS NULL)
(2146 ms)