Linq - Left Join on Multiple (Or) Conditions

LINQ to SQL - Left Outer Join with multiple join conditions

You need to introduce your join condition before calling DefaultIfEmpty(). I would just use extension method syntax:

from p in context.Periods
join f in context.Facts on p.id equals f.periodid into fg
from fgi in fg.Where(f => f.otherid == 17).DefaultIfEmpty()
where p.companyid == 100
select f.value

Or you could use a subquery:

from p in context.Periods
join f in context.Facts on p.id equals f.periodid into fg
from fgi in (from f in fg
             where f.otherid == 17
             select f).DefaultIfEmpty()
where p.companyid == 100
select f.value

LInq left join with multiple condition in on clause

• If you really want to do it with a JOIN, you could do it like this:

  from cust in customer
  join _p1 in products on cust.identifier equals _p1.orgID into _p1
  from p1 in _p1.DefaultIfEmpty()
  join _p2 in products on cust.identifier equals _p2.personalno into _p2
  from p2 in _p2.DefaultIfEmpty()
  where cust.identifier != null
  && (p1 != null || p2 != null)
  select new { Customer = cust, Product = p1 == null ? p2 : p1 }
  

• An easier solution without the JOINkeyword would be:

  from p in products
  from cust.Where(q => q.identifier != null && (p.orgID == q.identifier || p.personalno == q.identifier)).DefaultIfEmpty()
  where cust != null
  select new { Product = p, Customer = cust }
  

• To answer the question, that the headline suggests, do the following to do a join on multiple conditions, but be aware, that this only works for AND conditions and not for OR conditions:

  from t1 in Table1
  join t2 in Table2 on new { Criteria1 = t1.criteria1, Criteria2 = t1.criteria2 } equals new { Criteria1 = t2.criteria1, Criteria2 = t2.criteria2 }
  select new { T1 = t1, T2 = t2 }
  

Linq - left join on multiple (OR) conditions

LINQ only directly supports equijoins. If you want to do any other kind of join, you basically need a cross-join and where:

from a in tablea
from b in tableb
where a.col1 == b.col1 || a.col2 == b.col2
select ...

It's probably worth checking what the generated SQL looks like and what the query plan is like. There may be more efficient ways of doing it, but this is probably the simplest approach.

Multiple conditions on a left outer join clause in a LINQ query

With the help of the comments on the question, I've figured out writing the query using LINQ syntax.

from q in context.Questions
join qpph in context.PoolPickHandles
  on new { questionId = q.Id, pickerId = user.Id } 
    equals new { questionId = qpph.QuestionId, pickerId = qpph.PickerId } 
      into Handles
from m in Handles.DefaultIfEmpty()
where m == null
select q;

SQL to LINQ Left Outer Join with Multiple Conditions

This one should give you desired SQL:

.....
  join bn in applicationDbContext.Bins 
     on new { pr.ProdId, sz.size_id } equals new { bn.ProdId, bn.size_id } into ps                
  from bn in ps.DefaultIfEmpty()
  select new CoreBin
  {
    ... fields here
  }
.....

Using LINQ left join with multiple conditions and subquery

You need to to the join with anonymous type, I don't think you can choose a class like you did:

new { ID_REQUEST = a.ID_REQUEST , ID_KATALOG = a.ID_KATALOG}
equals
new { ID_REQUEST = b.ID_REQUEST, ID_KATALOG = b.ID_KATALOG }

If for example b.ID_KATALOG is nullable in the database, you can solve it like this:

new { ID_REQUEST = a.ID_REQUEST , ID_KATALOG = a.ID_KATALOG}
equals
new { ID_REQUEST = b.ID_REQUEST, ID_KATALOG = (int)b.ID_KATALOG }

That is assuming ID_KATALOG is an int of course.
Or you can do it the other way around too normally:

new { ID_REQUEST = a.ID_REQUEST , ID_KATALOG = (int?)a.ID_KATALOG}
equals
new { ID_REQUEST = b.ID_REQUEST, ID_KATALOG = b.ID_KATALOG }

LINQ: Left Outer Join with multiple conditions

var items = inputReportDefinitions.GroupJoin(
              baseReportDefinitions,
              firstSelector => new {
                         firstSelector.ParentName, firstSelector.ReportName
                                   },
              secondSelector => new {
                         secondSelector.ParentName, secondSelector.ReportName
                                   },
              (inputReport, baseCollection) => new {inputReport, baseCollection})
              .SelectMany(grp => grp.baseCollection.DefaultIfEmpty(),
                         (col, baseReport) => new
                                                 {
                                                    Base = baseReport,
                                                    Input = col.inputReport
                                                 });

I believe this ends up being a left outer join. I don't know how to convert this monstrosity to a query statement. I think if you add AsQueryable() to the end it could be used in Linq-to-SQL, but honestly, I have little experience with that.

EDIT: I figured it out. Much easier to read:

var otherItems = from i in inputReportDefinitions
                         join b in baseReportDefinitions
                         on new {i.ParentName, i.ReportName} 
                         equals new {b.ParentName, b.ReportName} into other
                         from baseReport in other.DefaultIfEmpty()
                         select new
                                    {
                                        Input = i,
                                        Base = baseReport
                                    };

C# Lambda Join with Multiple Conditions in a SQL Left Outer Join

In response to all @Cetin Basoz workings I was able to get a solution using LinqPad in Lambda - instead of Join I needed to use SelecctMany() in this example:

var myQuery = _db.CustomFields.SelectMany(
                      cf => cf.DataItems.Where(d => d.OutsideId == x.Id).DefaultIfEmpty(),
                      (cf, cd) => new {cf, cd })
                   .Where(s => s.cf.AnotherId == 1)
                   .Select(
                      m =>
                         new MyModel
                         {
                             CustomFieldId = m.cf.Id,
                             Name = m.cf.Name,
                             DataType = m.cf.DataType,
                             StringValue = m.cd.StringValue,
                             IntValue = m.cd.IntValue,
                             BoolValue = m.cd.BoolValue,
                             DateTimeValue = m.cd.DateTimeValue,
                             DecimalValue = m.cd.DecimalValue
                         }
                   ).ToList()

---

Related Topics