ToListAsync returns wrong amount of rows when using TemporalAll and list projection

[Ben555555]

The variable result only contains 1 items when the following code is executed.

string languageCode = Thread.CurrentThread.CurrentCulture.Name;

var query = dbContext.Users
    .TemporalAll()
    .Where(user => user.Id == id)
    .Select(user => new UserHistoryListModel()
    {
        Id = user.Id,
        RoleNames = dbContext.UserRoles.TemporalAll()
        .Where(userRole => EF.Property<DateTime>(user, "PeriodStart") >= EF.Property<DateTime>(userRole, "PeriodStart"))
        .OrderByDescending(userRole => EF.Property<DateTime>(userRole, "PeriodStart"))
        .Where(userRole => userRole.UserId == user.Id)
        .SelectMany(userRole => TemporalProjections.JoinEntityQuery<UserRoleEntity, RoleTranslationEntity>(dbContext.RoleTranslations).Invoke(userRole)
            .Where(roleTranslation => roleTranslation.RoleId == userRole.RoleId && roleTranslation.LanguageCode == languageCode))
        .Select(joined => joined.Name)
        .ToList()
    });

//query = query.AsSplitQuery();

var result = await query.ToListAsync(cancellationToken);

But when I analyze and execute the SQL query from the debugview there are actually 3 rows returned:

DECLARE @__languageCode_1 nvarchar(5) = N'de-CH';
DECLARE @__id_0 uniqueIdentifier = '996eb5cc-8171-47de-9a61-08dcf4b9e5b8';

SELECT [a].[Id], [a].[UpdateDate], [t0].[Name], [t0].[RoleId], [t0].[UserId], [t0].[RoleId0], [t0].[LanguageCode]
FROM [AspNetUsers] FOR SYSTEM_TIME ALL AS [a]
LEFT JOIN (
    SELECT [t].[Name], [u].[RoleId], [u].[UserId], [t].[RoleId] AS [RoleId0], [t].[LanguageCode], [u].[PeriodStart]
    FROM [UserRoles] FOR SYSTEM_TIME ALL AS [u]
    INNER JOIN (
        SELECT [r].[RoleId], [r].[LanguageCode], [r].[Name], [r].[PeriodStart]
        FROM [RoleTranslations] FOR SYSTEM_TIME ALL AS [r]
        WHERE [r].[LanguageCode] = @__languageCode_1
    ) AS [t] ON [u].[PeriodStart] >= [t].[PeriodStart] AND [u].[RoleId] = [t].[RoleId]
) AS [t0] ON [a].[PeriodStart] >= [t0].[PeriodStart] AND [a].[Id] = [t0].[UserId]
WHERE [a].[Id] = @__id_0
ORDER BY [a].[Id], [t0].[PeriodStart] DESC, [t0].[RoleId], [t0].[UserId], [t0].[RoleId0]

When I remove the projection part of "RoleNames" the correct amount of rows is returned. The only workaround I've found is to use AsSplitQuery(). Thefor is guess EF somehow thinks that the results are just duplicates and contains different entries for "RolesNames" (because this is a list in the projection). Also there are columns like RoleId and RoleId0 which I'm not sure why they are generated in the query.

Include provider and version information

EF Core version: 8.0.10
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 8.0
Operating system:
IDE: Visual Studio 2022 17.10.

[Ben555555]

It looks like a nested list projection with TemporalAll() doesn't work correctly. Because even with SplitQuery, the content of "RoleNames" is only correct for the first result. For the other items it's always empty. Therefor currently I have to fetch these RoleNames in a separate query:

var list = dbContext.Users
   .TemporalAll()
   .Where(user => user.Id == id)
   .Select(user => new UserHistoryListModel()
    {
        Id = user.Id,
                   PeriodStart = TemporalProjections.GetPeriodStartProjection<UserEntity>().Invoke(user)
    }).ToListAsync(cancellationToken);

if (list.Any())
{
    var maxPeriodStart = list.Max(item => item.PeriodStart);
    var minPeriodEnd = list.Min(item => item.PeriodEnd);

    // Many relations seem to be bugged and need to be fetched manually
    var userRoles = await dbContext.UserRoles
        .TemporalAll()
        .Where(userRole =>
        // Exclude user role histories that have ended before the user history was created
        EF.Property<DateTime>(userRole, "PeriodEnd") >= minPeriodEnd &&
        // Exclude user role histories that have started after the user history was created
        EF.Property<DateTime>(userRole, "PeriodStart") <= maxPeriodStart &&
        userRole.UserId == id)
        .SelectMany(userRole => dbContext.RoleTranslations
            .Where(roleTranslation => roleTranslation.RoleId == userRole.RoleId && roleTranslation.LanguageCode == languageCode)
            .Select(roleTranslation => new
            {
                RoleName = roleTranslation.Name,
                PeriodStart = EF.Property<DateTime>(userRole, "PeriodStart"),
                PeriodEnd = EF.Property<DateTime>(userRole, "PeriodStart")
            }))
        .ToListAsync(cancellationToken);

    foreach (var item in list)
    {
        // Only add the roles that were active during the period
        item.RoleNames = userRoles.Where(userRole => item.PeriodStart >= userRole.PeriodStart && item.PeriodEnd <= userRole.PeriodEnd)
            .Select(userRole => userRole.RoleName)
            .ToList();
    }
}

[maumar]

Queries with joins (or includes or navigations) only really reliably work with AsOf operation. EF can guarantee a consistent graph and properly bucket the results. For all other operations, adding joins may cause all kinds of trouble. In your example, say, UserRoles has been modified 10 times (so we get 10 rows for UserRole table), but RoleTranslation for that role was only modified twice (so we get 2 rows). There is INNER JOIN between them, so 8 UserRole rows will get gobbled up because they don't have the matching RoleTranslation. We already explicitly block using navigations with non-AsOf operations, but when users hand-craft JOINs we let those through.

[maumar]

We should consider adding some guidance/warning in the documentation so that customers are aware of pitfalls of using those problematic operators in complex queries

[Ben555555]

@maumar Will it ever be possible to make performant queries like this? I'm only creating those queries because it's a requirement for our projects to list the history of tables and relations while being able to sort and filter these rows. Also I can say it works the way I do it, at least for joining one relation, even though it's rather complicated / ugly.

[maumar]

@Ben555555 honestly, I doubt we will be able to make it work. temporal all + navigations is fundamentally incompatible with how the feature has been implemented (relatively simple rewrite) I'm not sure if this can be done without some sort of N+1 strategy, like the one you employed in your previous comment.

If that's the case, we would be very reluctant to make those transformations for you - we want to avoid cases where user write a seemingly simple query, and EF rewrites it into N+1 and drastically blows up the execution time

[roji]

Note that you can always query without Includes; you can use Join to define the exact query you want, or even do multiple queries and process/combine results client-side in whatever manner you want.

[maumar]

I filed a documentation issue to provide some guidance on how to manually craft queries with navigations in non-AsOf scenarios as they are quite tricky to get right