We have a scenario where an entity has a number associations from other entities that have been marked as IsNullable = true. When retrieving the entities using a named aggregated and sorting on a child entity’s property it results in a KeyNotFoundException because an inner join is being done rather than an outer join.
See the attached picture, it shows the entities that are being loaded using the named aggregate. The aggregate root is this example is EducationReport. The associations from EducationTerm, GradeLevel and EducationFacility to EducationReport have all been set to Is Nullable.
The query’s join property is being set to the following to ensure that outer joins are being done. This fixes the main query joins but it doesn’t resolve the sub queries for the other entities.
query.Join = Join.Outer<EducationReport, EducationFacility>("EducationFacilityId", "Id").And(Join.Outer<EducationReport, GradeLevel>("GradeLevelId", "Id")).And(Join.Outer<EducationReport, ReportType>("ReportTypeId", "Id")).And(Join.Outer<EducationReport, EducationTerm>("TermId", "Id"));
Below is a sample query where we are sorting on EducationTerm.Description. Note the inner join between EducationReport and EducationTerm. The same will occur when sorting on any of the entities where the association has been marked as IsNullable = true.
exec sp_executesql N'SELECT
[t0].*
FROM
(
SELECT
[t0].[Id] AS [t0.Id],
[t0].[ClientId] AS [t0.ClientId],
[t0].[Comments] AS [t0.Comments],
[t0].[CreatedOn] AS [t0.CreatedOn],
[t0].[DateIssued] AS [t0.DateIssued],
[t0].[DeletedOn] AS [t0.DeletedOn],
[t0].[EducationFacilityId] AS [t0.EducationFacilityId],
[t0].[FacilityUnknown] AS [t0.FacilityUnknown],
[t0].[GradeLevelId] AS [t0.GradeLevelId],
[t0].[IsHomeSchooled] AS [t0.IsHomeSchooled],
[t0].[LockVersion] AS [t0.LockVersion],
[t0].[ReportTypeId] AS [t0.ReportTypeId],
[t0].[TermId] AS [t0.TermId],
[t0].[UpdatedOn] AS [t0.UpdatedOn],
ROW_NUMBER() OVER(ORDER BY [t4].[Description]) as RowNumber
FROM
[dbo].[EducationReport] [t0]
LEFT OUTER JOIN
[dbo].[EducationFacility] [t1]
ON
[t0].[EducationFacilityId] = [t1].[Id]
LEFT OUTER JOIN
[dbo].[GradeLevel] [t2]
ON
[t0].[GradeLevelId] = [t2].[Id]
LEFT OUTER JOIN
[dbo].[ReportType] [t3]
ON
[t0].[ReportTypeId] = [t3].[Id]
LEFT OUTER JOIN
[dbo].[EducationTerm] [t4]
ON
[t0].[TermId] = [t4].[Id]
WHERE
([t0].[ClientId] = @p2 AND [t0].[DeletedOn] IS NULL)
)
[t0]
WHERE
RowNumber > @p0 AND
RowNumber <= @p1;
SELECT
[t1].[Id],
[t1].[ClassificationId],
[t1].[CreatedOn],
[t1].[DeletedOn],
[t1].[EmailAddress],
[t1].[FacilityTypeId],
[t1].[LockVersion],
[t1].[Name],
[t1].[UpdatedOn]
FROM
[dbo].[EducationFacility] [t1]
WHERE
(EXISTS (
SELECT
[t0].*
FROM
(
SELECT
[t0].*,
ROW_NUMBER() OVER(ORDER BY [t4].[Description]) as RowNumber
FROM
[dbo].[EducationReport] [t0]
INNER JOIN
[dbo].[EducationTerm] [t4]
ON
[t0].[TermId] = [t4].[Id]
WHERE
([t0].[ClientId] = @p2 AND [t0].[DeletedOn] IS NULL)
)
[t0]
WHERE
[t0].[EducationFacilityId] = [t1].[Id] AND
RowNumber > @p0 AND
RowNumber <= @p1
) AND [t1].[DeletedOn] IS NULL);
SELECT
[EducationReportResult].[Id],
[EducationReportResult].[Band],
[EducationReportResult].[CreatedOn],
[EducationReportResult].[EducationReportId],
[EducationReportResult].[Score],
[EducationReportResult].[Subject],
[EducationReportResult].[UpdatedOn]
FROM
[dbo].[EducationReportResult]
WHERE
EXISTS (
SELECT
[t0].*
FROM
(
SELECT
[t0].*,
ROW_NUMBER() OVER(ORDER BY [t4].[Description]) as RowNumber
FROM
[dbo].[EducationReport] [t0]
INNER JOIN
[dbo].[EducationTerm] [t4]
ON
[t0].[TermId] = [t4].[Id]
WHERE
([t0].[ClientId] = @p2 AND [t0].[DeletedOn] IS NULL)
)
[t0]
WHERE
[t0].[Id] = [EducationReportResult].[EducationReportId] AND
RowNumber > @p0 AND
RowNumber <= @p1
);
SELECT
[t2].[Id],
[t2].[CreatedOn],
[t2].[Description],
[t2].[IsProtected],
[t2].[LockVersion],
[t2].[Sequence],
[t2].[UpdatedOn]
FROM
[dbo].[GradeLevel] [t2]
WHERE
EXISTS (
SELECT
[t0].*
FROM
(
SELECT
[t0].*,
ROW_NUMBER() OVER(ORDER BY [t4].[Description]) as RowNumber
FROM
[dbo].[EducationReport] [t0]
INNER JOIN
[dbo].[EducationTerm] [t4]
ON
[t0].[TermId] = [t4].[Id]
WHERE
([t0].[ClientId] = @p2 AND [t0].[DeletedOn] IS NULL)
)
[t0]
WHERE
[t0].[GradeLevelId] = [t2].[Id] AND
RowNumber > @p0 AND
RowNumber <= @p1
);
SELECT
[t3].[Id],
[t3].[CreatedOn],
[t3].[Description],
[t3].[IsProtected],
[t3].[LockVersion],
[t3].[Sequence],
[t3].[UpdatedOn]
FROM
[dbo].[ReportType] [t3]
WHERE
EXISTS (
SELECT
[t0].*
FROM
(
SELECT
[t0].*,
ROW_NUMBER() OVER(ORDER BY [t4].[Description]) as RowNumber
FROM
[dbo].[EducationReport] [t0]
INNER JOIN
[dbo].[EducationTerm] [t4]
ON
[t0].[TermId] = [t4].[Id]
WHERE
([t0].[ClientId] = @p2 AND [t0].[DeletedOn] IS NULL)
)
[t0]
WHERE
[t0].[ReportTypeId] = [t3].[Id] AND
RowNumber > @p0 AND
RowNumber <= @p1
);
SELECT
[t4].[Id],
[t4].[CreatedOn],
[t4].[Description],
[t4].[IsProtected],
[t4].[LockVersion],
[t4].[Sequence],
[t4].[UpdatedOn]
FROM
[dbo].[EducationTerm] [t4]
WHERE
EXISTS (
SELECT
[t0].*
FROM
(
SELECT
[t0].*,
ROW_NUMBER() OVER(ORDER BY [t4].[Description]) as RowNumber
FROM
[dbo].[EducationReport] [t0]
INNER JOIN
[dbo].[EducationTerm] [t4]
ON
[t0].[TermId] = [t4].[Id]
WHERE
([t0].[ClientId] = @p2 AND [t0].[DeletedOn] IS NULL)
)
[t0]
WHERE
[t0].[TermId] = [t4].[Id] AND
RowNumber > @p0 AND
RowNumber <= @p1
)',N'@p0 int,@p1 int,@p2 uniqueidentifier',@p0=0,@p1=20,@p2='00000000-0000-0000-0000-000000000001'