score:128

Accepted answer

Ah, got it myselfs.
The quirks and quarks of LINQ-2-entities.
This looks most understandable:

var query2 = (
    from users in Repo.T_Benutzer
    from mappings in Repo.T_Benutzer_Benutzergruppen
        .Where(mapping => mapping.BEBG_BE == users.BE_ID).DefaultIfEmpty()
    from groups in Repo.T_Benutzergruppen
        .Where(gruppe => gruppe.ID == mappings.BEBG_BG).DefaultIfEmpty()
    //where users.BE_Name.Contains(keyword)
    // //|| mappings.BEBG_BE.Equals(666)  
    //|| mappings.BEBG_BE == 666 
    //|| groups.Name.Contains(keyword)

    select new
    {
         UserId = users.BE_ID
        ,UserName = users.BE_User
        ,UserGroupId = mappings.BEBG_BG
        ,GroupName = groups.Name
    }

);


var xy = (query2).ToList();

Remove the .DefaultIfEmpty(), and you get an inner join.
That was what I was looking for.

score:-1

Left join using linq //System.Linq

        Test t = new Test();

        //t.Employees is employee List
        //t.EmployeeDetails is EmployeeDetail List

        var result = from emp in t.Employees
                     join ed in t.EmployeeDetails on emp.Id equals ed.EDId into tmp
                     from final in tmp.DefaultIfEmpty()
                     select new { emp.Id, emp.Name, final?.Address };

        foreach (var r in result)
        {
            Console.WriteLine($"Employee Id: {r.Id}, and Name: {r.Name}, and address is: {r.Address}");
        }

score:1

You can use this not only in entities but also store procedure or other data source:

var customer = (from cus in _billingCommonservice.BillingUnit.CustomerRepository.GetAll()  
                          join man in _billingCommonservice.BillingUnit.FunctionRepository.ManagersCustomerValue()  
                          on cus.CustomerID equals man.CustomerID  
                          // start left join  
                          into a  
                          from b in a.DefaultIfEmpty(new DJBL_uspGetAllManagerCustomer_Result() )  
                          select new { cus.MobileNo1,b.ActiveStatus });  

score:1

Lambda Syntax Mapping

The query syntax solutions are good, but there are cases when a lambda syntax solution would be preferable (dealing with Expression Trees, for example). LinqPad conveniently converts query syntax to lambda syntax for a mapped query. With a little adjustment, we end up with:

// Left-join in query syntax (as seen in several other answers)
var querySyntax = 
  from o in dbcontext.Outer
  from i in dbcontext.Inner.Where(i => i.ID == o.ID).DefaultIfEmpty()
  select new { o.ID, i.InnerField };

// Maps roughly to:
var lambdaSyntax = dbcontext.Outer
    .SelectMany(
        o => dbcontext.Inner.Where(i => i.ID == o.ID).DefaultIfEmpty(),
        (o, i) => new { o.ID, i.InnerField }
    );

So a GroupJoin is actually superfluous in lambda syntax. The SelectMany + DefaultIfEmpty mapping is also covered in one of the test cases for the official dotnet/ef6 repo. See SelectMany_with_DefaultIfEmpty_translates_into_left_outer_join.

SelectMany and Other JOINs

The most important thing to take away here is that SelectMany is more versatile than Join when it comes to translating SQL JOINs.

Custom Extension Method

Using the above Lambda Statement, we can create an analog to the Join extension method in lambda syntax:

public static class Ext
{
    // The extension method
    public static IQueryable<TResult> LeftOuterJoin<TOuter, TInner, TKey, TResult>(
        this IQueryable<TOuter> outer, IQueryable<TInner> inner,
        Expression<Func<TOuter, TKey>> outerKeySelector, 
        Expression<Func<TInner, TKey>> innerKeySelector,
        Expression<Func<TOuter, TInner, TResult>> resultSelector)
    {
        // Re-context parameter references in key selector lambdas.
        // Will give scoping issues otherwise
        var oParam = Expression.Parameter(
            typeof(TOuter), 
            outerKeySelector.Parameters[0].Name
        );
        var iParam = Expression.Parameter(
            typeof(TInner), 
            innerKeySelector.Parameters[0].Name
        );
        
        var innerLinqTypeArgs = new Type[]{ typeof(TInner) };
        
        // Maps `inner.Where(i => outerKeySelector body == innerKeySelector body)`
        var whereCall = Expression.Call(
            typeof(Queryable), nameof(Queryable.Where), innerLinqTypeArgs,
            // Capture `inner` arg
            Expression.Constant(inner),
            (Expression<Func<TInner, bool>>)Expression.Lambda(
                SwapParams(
                    Expression.Equal(innerKeySelector.Body, outerKeySelector.Body),
                    new[] { iParam, oParam }
                ),
                iParam
            )
        );
        
        // Maps `(IEnumerable<TRight>)<Where Call>.DefaultIfEmpty()`
        // Cast is required to get SelectMany to work
        var dieCall = Expression.Convert(
            Expression.Call(typeof(Queryable), nameof(Queryable.DefaultIfEmpty), innerLinqTypeArgs, whereCall),
            typeof(IEnumerable<TInner>)
        );
        
        // Maps `o => <DefaultIfEmpty Call>`
        var innerLambda = (Expression<Func<TOuter, IEnumerable<TInner>>>)Expression.Lambda(dieCall, oParam);
        
        return outer.SelectMany(innerLambda, resultSelector);
    }
    
    // Core class used by SwapParams
    private class ParamSwapper : ExpressionVisitor
    {
        public ParameterExpression Replacement;
        
        // Replace if names match, otherwise leave alone.
        protected override Expression VisitParameter(ParameterExpression node)
            => node.Name == Replacement.Name ? Replacement : node;
    }
    
    // Swap out a lambda's parameter references for other parameters
    private static Expression SwapParams(Expression tgt, ParameterExpression[] pExps)
    {
        foreach (var pExp in pExps)
            tgt = new ParamSwapper { Replacement = pExp }.Visit(tgt);
            
        return tgt;
    }
}

Example Usage:

dbcontext.Outer
    .LeftOuterJoin(
        dbcontext.Inner, o => o.ID, i => i.ID, 
        (o, i) => new { o.ID, i.InnerField }
    );

Granted, it doesn't save a whole lot of typing, but I think it does make the intention more clear if you're coming from a SQL background.

score:6

May be I come later to answer but right now I'm facing with this... if helps there are one more solution (the way i solved it).

    var query2 = (
    from users in Repo.T_Benutzer
    join mappings in Repo.T_Benutzer_Benutzergruppen on mappings.BEBG_BE equals users.BE_ID into tmpMapp
    join groups in Repo.T_Benutzergruppen on groups.ID equals mappings.BEBG_BG into tmpGroups
    from mappings in tmpMapp.DefaultIfEmpty()
    from groups in tmpGroups.DefaultIfEmpty()
    select new
    {
         UserId = users.BE_ID
        ,UserName = users.BE_User
        ,UserGroupId = mappings.BEBG_BG
        ,GroupName = groups.Name
    }

);

By the way, I tried using the Stefan Steiger code which also helps but it was slower as hell.

score:6

Easy way is to use let keyword. This works for me.

from AItem in Db.A
let BItem = Db.B.Where(x => x.id == AItem.id ).FirstOrDefault() 
where SomeCondition
select new YourViewModel
{
    X1 = AItem.a,
    X2 = AItem.b,
    X3 = BItem.c
}

This is a simulation of Left Join. If each item in B table not match to A item, BItem return null

score:46

You can read an article i have written for joins in LINQ here

var query = 
from  u in Repo.T_Benutzer
join bg in Repo.T_Benutzer_Benutzergruppen
    on u.BE_ID equals bg.BEBG_BE
into temp
from j in temp.DefaultIfEmpty()
select new
{
    BE_User = u.BE_User,
    BEBG_BG = (int?)j.BEBG_BG// == null ? -1 : j.BEBG_BG
            //, bg.Name 
}

The following is the equivalent using extension methods:

var query = 
Repo.T_Benutzer
.GroupJoin
(
    Repo.T_Benutzer_Benutzergruppen,
    x=>x.BE_ID,
    x=>x.BEBG_BE,
    (o,i)=>new {o,i}
)
.SelectMany
(
    x => x.i.DefaultIfEmpty(),
    (o,i) => new
    {
        BE_User = o.o.BE_User,
        BEBG_BG = (int?)i.BEBG_BG
    }
);

Related Query

More Query from same tag