LINQ EF Core left join

Question

There is a question here: LEFT OUTER JOIN in LINQ, but that was specifically asked about Linq-to-Objects. This question is about Linq-to-Entities.

I have an inner join that I'd like to make into an outer join:

        alumni = alumni.Join(_context.AlumniSurvey, a => a.Uid, s => s.Uid, (a, s) => new { a, s })
                    .Where(x => x.s.UniversityNumber != x.s.Uid)
                    .Select(x => x.a);

Not that I am only returning alumni; I include AlumniSurvey because I will perform a variety of where clauses based upon what the user selects in the index view

Answer 1

This should translate to a LEFT JOIN in SQL - note that this will cause duplicate alumni rows if there are more than one matching AlumniSurvey rows.

alumni = alumni.GroupJoin(_context.AlumniSurvey, a => a.Uid, s => s.Uid, (a, sj) => new { a, sj })
               .SelectMany(asj => asj.sj.DefaultIfEmpty(), (asj, s) => new { asj.a, s })
               .Where(x => x.s.UniversityNumber != x.s.Uid)
               .Select(x => x.a);

You can create an extension method to simplify this:

private static Expression<Func<TOuter, TInner, TResult>> CastSMLambda<TOuter, TInner, TResult>(LambdaExpression ex, TOuter _1, TInner _2, TResult _3) => (Expression<Func<TOuter, TInner, TResult>>)ex;

public static IQueryable<TResult> LeftOuterJoin<TOuter, TInner, TKey, TResult>(
    this IQueryable<TOuter> outer,
    IQueryable<TInner> inner,
    Expression<Func<TOuter, TKey>> outerKeyExpr,
    Expression<Func<TInner, TKey>> innerKeyExpr,
    Expression<Func<TOuter, TInner, TResult>> resExpr) {

    var gjResTemplate = new { outer = default(TOuter), innerj = default(IEnumerable<TInner>) };
    // typeof(new { outer, innerj }) oij
    var oijParm = Expression.Parameter(gjResTemplate.GetType(), "oij");
    // TInner inner
    var iParm = Expression.Parameter(typeof(TInner), "inner");
    // oij.outer
    var oijOuter = Expression.PropertyOrField(oijParm, "outer");
    // (oij,inner) => resExpr(oij.outer, inner)
    var selectResExpr = CastSMLambda(Expression.Lambda(resExpr.Apply(oijOuter, iParm), oijParm, iParm), gjResTemplate, default(TInner), default(TResult));

    return outer.GroupJoin(inner, outerKeyExpr, innerKeyExpr, (outer, innerj) => new { outer, innerj })
                .SelectMany(r => r.innerj.DefaultIfEmpty(), selectResExpr);
}

// Apply: (x => f).Apply(args)
/// <summary>
/// Substitutes an array of Expression args for the parameters of a lambda, returning a new Expression
/// </summary>
/// <param name="e">The original LambdaExpression to "call".</param>
/// <param name="args">The Expression[] of values to substitute for the parameters of e.</param>
/// <returns>Expression representing e.Body with args substituted in</returns>
public static Expression Apply(this LambdaExpression e, params Expression[] args) {
    var b = e.Body;

    foreach (var pa in e.Parameters.Zip(args, (p, a) => (p, a)))
        b = b.Replace(pa.p, pa.a);

    return b.PropagateNull();
}

/// <summary>
/// Replaces an Expression (reference Equals) with another Expression
/// </summary>
/// <param name="orig">The original Expression.</param>
/// <param name="from">The from Expression.</param>
/// <param name="to">The to Expression.</param>
/// <returns>Expression with all occurrences of from replaced with to</returns>
public static T Replace<T>(this T orig, Expression from, Expression to) where T : Expression => (T)new ReplaceVisitor(from, to).Visit(orig);

/// <summary>
/// ExpressionVisitor to replace an Expression (that is Equals) with another Expression.
/// </summary>
public class ReplaceVisitor : ExpressionVisitor {
    readonly Expression from;
    readonly Expression to;

    public ReplaceVisitor(Expression from, Expression to) {
        this.from = from;
        this.to = to;
    }

    public override Expression Visit(Expression node) => node == from ? to : base.Visit(node);
}

public static T PropagateNull<T>(this T orig) where T : Expression => (T)new NullVisitor().Visit(orig);

/// <summary>
/// ExpressionVisitor to replace a null.member Expression with a null
/// </summary>
public class NullVisitor : System.Linq.Expressions.ExpressionVisitor {
    public override Expression Visit(Expression node) {
        if (node is MemberExpression nme && nme.Expression is ConstantExpression nce && nce.Value == null)
            return Expression.Constant(null, nce.Type.GetMember(nme.Member.Name).Single().GetMemberType());
        else
            return base.Visit(node);
    }
}