LEFT JOIN in LINQ to entities?

I'm trying out LINQ to entities.

I have a problem with the following: I want it to do this:

SELECT 
	 T_Benutzer.BE_User
	,T_Benutzer_Benutzergruppen.BEBG_BE
FROM T_Benutzer

LEFT JOIN T_Benutzer_Benutzergruppen
	ON T_Benutzer_Benutzergruppen.BEBG_BE = T_Benutzer.BE_ID 

the closest thing I've come to is this:

        var lol = (
            from u in Repo.T_Benutzer

            //where u.BE_ID == 1
            from o in Repo.T_Benutzer_Benutzergruppen.DefaultIfEmpty()
                // on u.BE_ID equals o.BEBG_BE
            
            where (u.BE_ID == o.BEBG_BE || o.BEBG_BE == null)

            //join bg in Repo.T_Benutzergruppen.DefaultIfEmpty()
            //    on o.BEBG_BG equals bg.ID

            //where bg.ID == 899 

            orderby
                u.BE_Name ascending
                //, bg.Name descending

            //select u 
            select new
            {
                 u.BE_User
                ,o.BEBG_BG
                //, bg.Name 
            }
         ).ToList();

But this generates the same results as an inner join, and not a left join.
Moreover, it creates this completely crazy SQL:

SELECT 
	 [Extent1].[BE_ID] AS [BE_ID]
	,[Extent1].[BE_User] AS [BE_User]
	,[Join1].[BEBG_BG] AS [BEBG_BG]
FROM  [dbo].[T_Benutzer] AS [Extent1]

CROSS JOIN  
(
	SELECT 
		 [Extent2].[BEBG_BE] AS [BEBG_BE]
		,[Extent2].[BEBG_BG] AS [BEBG_BG]
	FROM ( SELECT 1 AS X ) AS [SingleRowTable1]
	LEFT OUTER JOIN [dbo].[T_Benutzer_Benutzergruppen] AS [Extent2] 
		ON 1 = 1 
) AS [Join1]

WHERE [Extent1].[BE_ID] = [Join1].[BEBG_BE] 
OR [Join1].[BEBG_BE] IS NULL

ORDER BY [Extent1].[BE_Name] ASC

How can I do a left join in LINQ-2-entities in a way where another person can still understand what's being done in that code ?

and most-preferably where the generated SQL looks like:

SELECT 
     T_Benutzer.BE_User
    ,T_Benutzer_Benutzergruppen.BEBG_BE
FROM T_Benutzer

LEFT JOIN T_Benutzer_Benutzergruppen
    ON T_Benutzer_Benutzergruppen.BEBG_BE = T_Benutzer.BE_ID 

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.

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
	}
);

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.

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

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 });  

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.

• For inner joins, you can even use SelectMany instead of Join. Just drop the .DefaultIfEmpty().

• You can do a CROSS JOIN if you also drop the Where in the SelectMany.

  See https://stackoverflow.com/questions/56547/how-do-you-perform-a-cross-join-with-linq-to-sql.

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.

> 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}");
        }