Entity framework left join

Hola usuario de nuestro sitio web, tenemos la solución a lo que buscas, has scroll y la hallarás aquí.

Solución:

adaptado de MSDN, cómo unirse a la izquierda usando EF 4

var query = from u in usergroups
            join p in UsergroupPrices on u.UsergroupID equals p.UsergroupID into gj
            from x in gj.DefaultIfEmpty()
            select new  
                UsergroupID = u.UsergroupID,
                UsergroupName = u.UsergroupName,
                Price = (x == null ? String.Empty : x.Price) 
            ;

Puede ser un poco exagerado, pero escribí un método de extensión, así que puedes hacer un LeftJoin utilizando el Join sintaxis (al menos en la notación de llamada al método):

persons.LeftJoin(
    phoneNumbers,
    person => person.Id,
    phoneNumber => phoneNumber.PersonId,
    (person, phoneNumber) => new
        
            Person = person,
            PhoneNumber = phoneNumber?.Number
        
);

Mi código no hace más que agregar un GroupJoin y un SelectMany llamar al árbol de expresión actual. Sin embargo, parece bastante complicado porque tengo que construir las expresiones yo mismo y modificar el árbol de expresiones especificado por el usuario en el resultSelector parámetro para mantener todo el árbol traducible por LINQ-to-Entities.

public static class LeftJoinExtension

    public static IQueryable LeftJoin(
        this IQueryable outer,
        IQueryable inner,
        Expression> outerKeySelector,
        Expression> innerKeySelector,
        Expression> resultSelector)
    
        MethodInfo groupJoin = typeof (Queryable).GetMethods()
                                                 .Single(m => m.ToString() == "System.Linq.IQueryable`1[TResult] GroupJoin[TOuter,TInner,TKey,TResult](System.Linq.IQueryable`1[TOuter], System.Collections.Generic.IEnumerable`1[TInner], System.Linq.Expressions.Expression`1[System.Func`2[TOuter,TKey]], System.Linq.Expressions.Expression`1[System.Func`2[TInner,TKey]], System.Linq.Expressions.Expression`1[System.Func`3[TOuter,System.Collections.Generic.IEnumerable`1[TInner],TResult]])")
                                                 .MakeGenericMethod(typeof (TOuter), typeof (TInner), typeof (TKey), typeof (LeftJoinIntermediate));
        MethodInfo selectMany = typeof (Queryable).GetMethods()
                                                  .Single(m => m.ToString() == "System.Linq.IQueryable`1[TResult] SelectMany[TSource,TCollection,TResult](System.Linq.IQueryable`1[TSource], System.Linq.Expressions.Expression`1[System.Func`2[TSource,System.Collections.Generic.IEnumerable`1[TCollection]]], System.Linq.Expressions.Expression`1[System.Func`3[TSource,TCollection,TResult]])")
                                                  .MakeGenericMethod(typeof (LeftJoinIntermediate), typeof (TInner), typeof (TResult));

        var groupJoinResultSelector = (Expression, LeftJoinIntermediate>>)
                                      ((oneOuter, manyInners) => new LeftJoinIntermediate OneOuter = oneOuter, ManyInners = manyInners);

        MethodCallExpression exprGroupJoin = Expression.Call(groupJoin, outer.Expression, inner.Expression, outerKeySelector, innerKeySelector, groupJoinResultSelector);

        var selectManyCollectionSelector = (Expression, IEnumerable>>)
                                           (t => t.ManyInners.DefaultIfEmpty());

        ParameterExpression paramUser = resultSelector.Parameters.First();

        ParameterExpression paramNew = Expression.Parameter(typeof (LeftJoinIntermediate), "t");
        MemberExpression propExpr = Expression.Property(paramNew, "OneOuter");

        LambdaExpression selectManyResultSelector = Expression.Lambda(new Replacer(paramUser, propExpr).Visit(resultSelector.Body), paramNew, resultSelector.Parameters.Skip(1).First());

        MethodCallExpression exprSelectMany = Expression.Call(selectMany, exprGroupJoin, selectManyCollectionSelector, selectManyResultSelector);

        return outer.Provider.CreateQuery(exprSelectMany);
    

    private class LeftJoinIntermediate
    
        public TOuter OneOuter  get; set; 
        public IEnumerable ManyInners  get; set; 
    

    private class Replacer : ExpressionVisitor
    
        private readonly ParameterExpression _oldParam;
        private readonly Expression _replacement;

        public Replacer(ParameterExpression oldParam, Expression replacement)
        
            _oldParam = oldParam;
            _replacement = replacement;
        

        public override Expression Visit(Expression exp)
        
            if (exp == _oldParam)
            
                return _replacement;
            

            return base.Visit(exp);
        
    

Haga su vida más fácil (no use unirse al grupo):

var query = from ug in UserGroups
            from ugp in UserGroupPrices.Where(x => x.UserGroupId == ug.Id).DefaultIfEmpty()
            select new 
             
                UserGroupID = ug.UserGroupID,
                UserGroupName = ug.UserGroupName,
                Price = ugp != null ? ugp.Price : 0 //this is to handle nulls as even when Price is non-nullable prop it may come as null from SQL (result of Left Outer Join)
            ;

Te mostramos las reseñas y valoraciones de los lectores

Recuerda que tienes concesión de agregar una reseña si diste con la contestación.

---

---

Utiliza Nuestro Buscador