LINQ translation issue

[pikausp]

I have the following model for tracking notifications

public class Notification {
    [Key]
    public int Id { get; set; }
    public NotificationType Type { get; set; }
    public string? Data { get; set; }
    public DateTimeOffset? UpdatedAt { get; set; }

    public IList<UserNotification> UserNotifications { get; set; }
}

public class UserNotification {
    public int UserId { get; set; }
    public User User { get; set; } = null!;

    public int NotificationId { get; set; }
    public Notification Notification { get; set; } = null!;

    public DateTimeOffset? UpdatedAt { get; set; }
    public DateTimeOffset? ViewedAt { get; set; }
}

Each notification can be assigned to multiple users. My goal is to obtain when the latest change has occurred either on the level of the Notification record (i.e there was a typo and admin fixed it) or the level of UserNotification (i.e user has viewed their notification) and the number of unread notifications, all for a specific user.

My query looks like this

await context.Set<UserNotification>()
            .Where(un => un.UserId == user.Id).Include(un => un.Notification)
            .Select(un => new {
                viewedAt = un.ViewedAt,
                unUpdatedAt = un.UpdatedAt,
                nUpdatedAt= un.Notification.UpdatedAt
            })
            .GroupBy(g => 1)
            .Select(g => new {
                unreadCount = g.Count(l => l.viewedAt == null),
                lastNCreatedAt = g.Max(l => l.nUpdatedAt),
                lastUnCreatedAt = g.Max(l => l.unUpdatedAt)
            })
            .FirstOrDefaultAsync();

This is the output SQL logged to the console

SELECT COUNT(*)::INT AS "unreadCount", MAX(n."UpdatedAt") AS "lastNCreatedAt", MAX(u."UpdatedAt") AS "lastUnCreatedAt"
FROM "UserNotification" AS u
INNER JOIN "Notifications" AS n ON u."NotificationId" = n."Id"
WHERE (u."UserId" = @__user_Id_0) AND (@__user_Id_0 IS NOT NULL)
LIMIT 1 

I don't think the value of unreadCount reflects the LINQ since Count(*) should return the number of notifications.

Am I missing something or this a bug?

[roji]

This currently isn't supported by EF Core itself - see #1090 which was about this exact thing. 3.1.0 will at least throw an exception instead of generating incorrect SQL, the actual feature is tracked by dotnet/efcore#11711.