Can't make GroupJoin work. NavigationExpandingExpressionVisitor exception

Question

This is my first time using GroupJoin. From the examples I have seen it seems pretty straightforward to use in its basic form but I always get a NavigationExpandingExpressionVisitor exception when I use it. Here, an example:

[Table("Users")]
public class WAUser
{
    public int Id { get; set; }
    
    //TODO: Unique key
    [NotNull]
    public string UserUuid { get; set; } 
    
    [DefaultValue(true)]
    public bool NotifyOnlineState { get; set; }
    [DefaultValue(true)]
    public bool NotifyOfflineState { get; set; }
}


public class WASubscription
{
    public int Id { get; set; }
    
    public string PackageIdentifier { get; set; } //Product package indentifier
    
    public DateTime? ExpiresAt { get; set; } //When the subscription or trial expires
    
    public bool Expired { get; set; }
    
    public bool IsTrial { get; set; }
    
    public int PhoneCount { get; set; } //Number of phones this subscriptions supplies
    
    public int UserId { get; set; }
    public WAUser User { get; set; }
}

var userSubscriptions = await dbContext.Users
                .GroupJoin(dbContext.Subscriptions,
                    u => u.Id,
                    s => s.UserId,
                    (u, subscriptions) => new
                    {
                        User = u,
                        Subscriptions = subscriptions
                    })
                .ToListAsync();

The Exception thrown:

Unhandled exception. System.InvalidOperationException: Processing of the LINQ expression 'DbSet .GroupJoin( outer: DbSet, inner: u => u.Id, outerKeySelector: s => s.UserId, innerKeySelector: (u, subscriptions) => new { User = u, Subscriptions = subscriptions })' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.

user_5202563 · Answer

Glad you asked.

The problem is that EF Core team doesn't (and does not want to) provide GroupJoin translation. See my comments on this EF Core GitHub thread Query: Support GroupJoin when it is final query operator #19930 and linked discussions where I was trying to convince them to include such support (which should have been extremely easy for what they do to support LINQ left outer join pattern for instance). So please go there and vote, otherwise the argument is that it "has no value".

With that being said, with current EF Core either add and use collection navigation property (preferable), or use correlated subquery instead of GroupJoin, e.g. replace

.GroupJoin(dbContext.Subscriptions,
    u => u.Id,
    s => s.UserId,
    (u, subscriptions) => new
    {
        User = u,
        Subscriptions = subscriptions
    })

with

.Select(u => new
{
    User = u,
    Subscriptions = dbContext.Subscriptions.Where(s => u.Id == s.UserId) // <--
})

Can't make GroupJoin work. NavigationExpandingExpressionVisitor exception

1 Answers