EF Core will not allow you to do a GroupJoin without following up with a SelectMany in order to flatten the list. The GroupJoin has no equivalent implementation in SQL, however the GroupJoin/SelectMany is equivalent to an inner-join or left-join (depending on if you use DefaultIfEmpty) so it works fine:
context.Users.GroupJoin(
context.UserRoles,
u => u.UserId,
r => r.UserId,
(user, roles) => new { user, roles })
//Will not work without this line
.SelectMany(x => x.roles.DefaultIfEmpty(), (x, r) => new { x.user, role = r })
.ToList();
If you actually want your results to be grouped (as opposed to trying to do a left-join) you have a few options:
You can materialize the results of a left join, then group the results in-memory (the code below uses my LeftJoin function shown in LEFT OUTER JOIN in LINQ):
context.Users.LeftJoin(
context.UserRoles,
u => u.UserId,
r => r.UserId,
(user, roles) => new { user, roles })
.ToList()
.GroupBy(x => x.user, (u, x) => new
{
User = u,
Roles = x.Select(z => z.role).Where(r => r != null).ToList()
})
.ToList();
You can use a sub-query. Note that EF is smart enough to use a left-join when it generates the SQL:
context.Users.Select(u => new
{
User = u,
Roles = context.UserRoles.Where(r => r.UserId == u.UserId).ToList()
})
.ToList();
If you prefer the GroupJoin syntax, but don't want to have to keep calling all the other functions to flatten, materialize, then re-group the results, you can use my JoinMany() extension method. This method uses the sub-query approach but wraps it in a generic method that looks very similar to the GroupJoin function:
context.Users.JoinMany(
context.UserRoles,
(u, r) => u.UserId == r.UserId,
(user, roles) => new { user, roles })
.ToList();
Supporting code:
public static class QueryableExtensions
{
public static IQueryable<TResult> LeftJoin<TOuter, TInner, TKey, TResult>(
this IQueryable<TOuter> outer,
IEnumerable<TInner> inner, Expression<Func<TOuter, TKey>> outerKeySelector,
Expression<Func<TInner, TKey>> innerKeySelector,
Expression<Func<TOuter, TInner, TResult>> resultSelector)
{
return outer
.GroupJoin(inner, outerKeySelector, innerKeySelector, (o, i) => new { o, i })
.SelectMany(o => o.i.DefaultIfEmpty(), (x, i) => new { x.o, i })
.ApplySelector(x => x.o, x => x.i, resultSelector);
}
public static IQueryable<TResult> JoinMany<TOuter, TInner, TResult>(
this IQueryable<TOuter> outers, IQueryable<TInner> inners,
Expression<Func<TOuter, TInner, bool>> condition,
Expression<Func<TOuter, IEnumerable<TInner>, TResult>> resultSelector)
{
//Use a placeholder "p => true" expression for the sub-query
Expression<Func<TOuter, JoinResult<TOuter, IEnumerable<TInner>>>> joinSelector = o =>
new JoinResult<TOuter, IEnumerable<TInner>> { Outer = o, Inner = inners.Where(p => true) };
//Create the where-clause that will be used for the sub-query
var whereClause = Expression.Lambda<Func<TInner, bool>>(
condition.Body.ReplaceParameter(condition.Parameters[0], joinSelector.Parameters[0]),
condition.Parameters[1]);
//Replace the placeholder expression with our new where clause
joinSelector = Expression.Lambda<Func<TOuter, JoinResult<TOuter, IEnumerable<TInner>>>>(
joinSelector.Body.VisitExpression(node =>
(node is LambdaExpression le && le.Parameters.Count == 1 && le.Parameters[0].Type == typeof(TInner)
&& le.Body is ConstantExpression ce && ce.Value is bool b && b)
? whereClause : null),
joinSelector.Parameters[0]);
return outers.Select(joinSelector).ApplySelector(x => x.Outer, x => x.Inner, resultSelector);
}
private static IQueryable<TResult> ApplySelector<TSource, TOuter, TInner, TResult>(
this IQueryable<TSource> source,
Expression<Func<TSource, TOuter>> outerProperty,
Expression<Func<TSource, TInner>> innerProperty,
Expression<Func<TOuter, TInner, TResult>> resultSelector)
{
var p = Expression.Parameter(typeof(TSource), $"param_{Guid.NewGuid()}".Replace("-", string.Empty));
Expression body = resultSelector?.Body
.ReplaceParameter(resultSelector.Parameters[0], outerProperty.Body.ReplaceParameter(outerProperty.Parameters[0], p))
.ReplaceParameter(resultSelector.Parameters[1], innerProperty.Body.ReplaceParameter(innerProperty.Parameters[0], p));
var selector = Expression.Lambda<Func<TSource, TResult>>(body, p);
return source.Select(selector);
}
public class JoinResult<TOuter, TInner>
{
public TOuter Outer { get; set; }
public TInner Inner { get; set; }
}
}
public static class ExpressionExtensions
{
public static Expression ReplaceParameter(this Expression source, ParameterExpression toReplace, Expression newExpression)
=> new ReplaceParameterExpressionVisitor(toReplace, newExpression).Visit(source);
public static Expression VisitExpression(this Expression source, Func<Expression, Expression> onVisit)
=> new DelegateExpressionVisitor (onVisit).Visit(source);
}
public class DelegateExpressionVisitor : ExpressionVisitor
{
Func<Expression, Expression> OnVisit { get; }
public DelegateExpressionVisitor(Func<Expression, Expression> onVisit)
{
this.OnVisit = onVisit;
}
public override Expression Visit(Expression node)
{
return OnVisit(node) ?? base.Visit(node);
}
}
public class ReplaceParameterExpressionVisitor : ExpressionVisitor
{
public ParameterExpression ToReplace { get; }
public Expression ReplacementExpression { get; }
public ReplaceParameterExpressionVisitor(ParameterExpression toReplace, Expression replacement)
{
this.ToReplace = toReplace;
this.ReplacementExpression = replacement;
}
protected override Expression VisitParameter(ParameterExpression node)
=> (node == ToReplace) ? ReplacementExpression : base.VisitParameter(node);
}
