Join and Left Join Equivalent in Linq

JOIN and LEFT JOIN equivalent in LINQ

You may have to tweak this slightly as I was going off the cuff, but there are a couple of major things to keep in mind. If you have your relationships set up properly in your dbml, you should be able to do inner joins implicitly and just access the data through your initial table. Also, left joins in LINQ are not as straight forward as we may hope and you have to go through the DefaultIfEmpty syntax in order to make it happen. I created an anonymous type here, but you may want to put into a DTO class or something to that effect. I also didn't know what you wanted to do in the case of nulls, but you can use the ?? syntax to define a value to give the variable if the value is null. Let me know if you have additional questions...

var query = (from a in context.Appointment
join b in context.AppointmentFormula on a.AppointmentId equals b.AppointmentId into temp
from c in temp.DefaultIfEmpty()
join d in context.AppointmentForm on a.AppointmentID equals e.AppointmentID into temp2
from e in temp2.DefaultIfEmpty()
where a.RowStatus == 1 && c.RowStatus == 1 && a.Type == 1
select new {a.AppointmentId, a.Status, a.Type, a.Title, c.Days ?? 0, a.Type.Description, e.FormID ?? 0}).OrderBy(a.Type);

JOIN and LEFT JOIN equivalent in LINQ with Method Syntax

The join ... into becomes a GroupJoin and the second from becomes a SelectMany:

var linq_query = Item
    .GroupJoin(
        item_status.Where(x => x.selected_item_status.FLAGGED), // EDIT: Where clause moved here.
        selected_item => selected_item.ID,
        selected_item_status => selected_item_status.itemID,
        (selected_item, joined) => new
        {
            selected_item,
            statuses = joined.DefaultWithEmpty(),
        })
    .SelectMany(x => x.statuses.Select(selected_item_status => new
    {
        x.selected_item,
        selected_item_status,
    }))
    // EDIT: Removed where clause.
    .ToList();

It looks like the Where makes the left outer join unnecessary, as null statuses will be filtered out anyway.

EDIT: No, upon reviewing the SQL it looks like your LINQ query is slightly incorrect. It should be:

var linq_query = (
    from selected_item in item
    join selected_item_status
        in (
            from status in item_status
            where status.FLAGGED
            select status)
        on selected_item.ID equals item_status.itemID into joined
    from item_status in joined.DefaultIfEmpty()
    select new {selected_item, selected_item_status}).ToList();

Linq - Equivalent to BETWEEN inside a Left Join

Edit - Added DefaultOrEmpty() in order for it to be a Left Join

Modify your query like so, this will force the where clause into the join on clause. It wont give you the Between clause in the Join, but at least there wont be a where clause

var theModel = from model in models
               from Tcar in cars.Where(x => model.id == x.model)
                                .Where(x => model.Start <= x.year && model.End >= x.year)
                                .DefaultOrEmpty()
               select new
                   {
                       CAR = Tcar.name
                   };

linq: left join in linq with different type of conditions

Try like this;

(from s in db.PromotionalOffers
 join e in db.PromotionalOffersUsed on s.Id equals e.OfferId into joinT
 from e in joinT.DefaultIfEmpty()
 where s.ISVCSPId == 10 && (e == null || e.OfferId == null)
 select new { PromotionalOffers = s, PromotionalOffersUsed = joinT } ).ToListAsync();

LEFT JOIN in LINQ to entities?

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.

How to left join multiple tables with LINQ

From Linq - left join on multiple (OR) conditions :

IQueryable<Job> jobs = (from j in _db.Jobs

                join jt in _db.JobTranslators on j.Id equals jt.JobId into jts
                from jtResult in jts.DefaultIfEmpty()

                join jr in _db.JobRevisors on jtResult.Id equals jr.JobId into jrs
                from jrResult in jrs.DefaultIfEmpty()

                join u in _db.Users on jtResult.UserId equals u.Id into jtU
                from jtUResult in jtU.DefaultIfEmpty()

                where jtUResult.Id == userId

                orderby j.Id

                select j).Concat(
                from j in _db.Jobs

                join jt in _db.JobTranslators on j.Id equals jt.JobId into jts
                from jtResult in jts.DefaultIfEmpty()

                join jr in _db.JobRevisors on jtResult.Id equals jr.JobId into jrs
                from jrResult in jrs.DefaultIfEmpty()

                join u in _db.Users on jrResult.UserId equals u.Id into jrU
                from jrUResult in jrU.DefaultIfEmpty()

                where jtUResult.Id == userId

                orderby j.Id

                select j
                ).Distinct()

Include method in LINQ is used for Left Join?

In the first example the join should not have .AsEnumerable() on the end of it. By doing that you are causing EF to go and get all the records from Product_Categories and then doing the join in memory which can be very inefficient as it doesn't use any kind of index.

The second option you have isn't pure LINQ. Include is an EF-specific extension method that is not available in other providers.

So if you want common LINQ you could use with other DB providers go with option 1. If you want simpler syntax and okay with being EF specific option 2 might be better.

SQL to LINQ with multiple join, count and left join

For translating SQL to LINQ query comprehension:

Translate subselects as separately declared variables unless they reference columns outside the subselect, in which case use parentheses to create a sub-query.
Translate each clause in LINQ clause order, translating monadic and aggregate operators (DISTINCT, TOP, MIN, MAX etc) into functions applied to the whole LINQ query.
Use table aliases as range variables. Use column aliases as anonymous type field names.
Use anonymous types (new { ... }) for multiple columns (e.g. in groupby).
Use First().field to get non-key values from the groupby aggregate range variable.
When using EF or EF Core, translate JOIN clauses into navigation properties possibly using .Include().
Otherwise JOIN clauses that are multiple ANDed equality tests between the two tables should be translated into anonymous objects on each side of equals.
JOIN conditions that aren't all equality tests with AND must be handled using where clauses outside the join, or with cross product (from ... from ...) and then where. If you are doing LEFT JOIN, add a lambda Where clause between the join range variable and the DefaultIfEmpty() call.
LEFT JOIN is simulated by using into joinvariable and doing another from the joinvariable followed by .DefaultIfEmpty().
Translate multiple tables in the FROM clause into multiple from clauses.
Translate FROM T1 CROSS APPLY T2 into two from clauses, one for T1 and one for T2.
Translate FROM T1 OUTER APPLY T2 into two from clauses, one for T1 and one for T2, but add .DefaultIfEmpty() to T2.
Replace COALESCE with the conditional operator (?:)and a null test.
Translate IN to .Contains() and NOT IN to !...Contains(), using literal arrays or array variables for constant lists.
Translate x BETWEEN low AND high to low <= x && x <= high.
Translate CASE, ISNULL and IIF to the ternary conditional operator ?:.
SELECT * must be replaced with select range_variable or for joins, an anonymous object containing all the range variables.
SELECT columns must be replaced with select new { ... } creating an anonymous object with all the desired fields or expressions.
References to computed SELECT columns can be translated by repeating the expression or by using let to name the expression before its first use.
Proper FULL OUTER JOIN must be handled with an extension method.
Translate UNION to Concat unless both sub-queries are DISTINCT, in which case you can translate to Union and leave off the DISTINCT.
Translate aggregate queries that have no GROUP BY using a singleton GroupBy: add .GroupBy(r => 1) and then translate the aggregate functions in the Select.
Date Math and some other canonical functions can be accessed using EF.Functions to get an instance of the DbFunctions class (EF Core), EntityFunctions class (EF < 6) or DbFunctions to access the static methods (EntityFramework 6.x).
Translate SQL LIKE expressions using (EF Core >= 2) EF.Functions.Like(column, pattern) or (EF 6.x) DbFunctions.Like(column, pattern).

Applying these rules to your SQL query, you get:

var subrq = from r in Table_R
            group r by r.Id into rg
            select new { Id = rg.Key, cnt = rg.Count() };

var ansq = (from c in Table_C
            join v in Table_V on c.Id equals v.Id
            join r in subrq on c.Id equals r.Id into rj
            from r in rj.DefaultIfEmpty()
            where c.IdUser == "1234"
            group new { c, v, r } by new { c.Id, c.Title, r.cnt } into cvrg
            select new {
                cvrg.Key.Title,
                Nb_V2 = cvrg.Count(),
                Nb_V1 = cvrg.Select(cvr => cvr.v.IdUser).Distinct().Count(),
                Nb_R = (int?)cvrg.Key.cnt
            }).Distinct();

The lambda translation is tricky, but the conversion of LEFT JOIN to GroupJoin...SelectMany is what is needed:

var subr2 = Table_R.GroupBy(r => r.Id).Select(rg => new { Id = rg.Key, cnt = rg.Count() });
var ans2 = Table_C.Where(c => c.IdUser == "1234")
                  .Join(Table_V, c => c.Id, v => v.Id, (c, v) => new { c, v })
                  .GroupJoin(subr, cv => cv.c.Id, r => r.Id, (cv, rj) => new { cv.c, cv.v, rj })
                  .SelectMany(cvrj => cvrj.rj.DefaultIfEmpty(), (cvrj, r) => new { cvrj.c, cvrj.v, r })
                  .GroupBy(cvr => new { cvr.c.Id, cvr.c.Title, cvr.r.cnt })
                  .Select(cvrg => new { cvrg.Key.Title, Nb_V2 = cvrg.Count(), Nb_V1 = cvrg.Select(cvr => cvr.v.IdUser).Distinct().Count(), Nb_R = (int?)cvrg.Key.cnt });

SQL to LINQ - Left Join Before Inner Join

Use:

var query = from m in tableA
join s in tableB on m.ShipId equals s.ShipId
join p in tableC on s.PostageId equals p.PostageId
where m.MailId == "Specification" && p.PostageCode == "Package"
select m.MailId;

Your query uses a LEFT OUTER JOIN but it doesn't need it.

It will, in practice, function as an INNER JOIN due to your tc.PostageCode = 'Package' clause. If you compare to a column value in a table in a WHERE clause (and there are no OR clauses and you aren't comparing to NULL) then effectively all joins to get to that table will be treated as INNER).

That clause will never be true if TableB is null (which is why you use LEFT OUTER JOIN vs INNER JOIN) - so you should just use an INNER JOIN to make the problem simpler.