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
JOIN
keyword 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 forOR
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 }
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
}
.....
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;
LINQ for an SQL with multiple LEFT OUTER JOINS on different tables
You can simply convert your query to linq query like this:
var results = (from a in A_TABLE
join b in B_TABLE
on a.A_TABLE_Id equals b.A_TABLE_Id into ab
from b in ab.DefaultIfEmpty()
join c in C_TABLE_List on b.B_TABLE_Id equals c.B_TABLE_Id
select new
{
ClassAProperty1 = a.Property1,
ClassBProperty1 = b.Property1,
ClassCProperty1 = c.Property1
}).ToList();
you can continue on joining tables whatever times you need.
Don't forget to change Property# to required property names.
for more info take a look at LINQ Left Outer Join, and this stack thread.
Update:
this is the Lambda expressions version of the query:
var result = A_TABLE
.GroupJoin(B_TABLE,
a => a.A_TABLE_Id,
b => b.A_TABLE_Id,
(a, b) =>
new {
tableAProperty1 = a.Property1,
tableAProperty2 = a.Property2, /* You cannot access this property in next join if you don't add it here */
B_TABLE = b.FirstOrDefault() /* This is the way to access B_TABLE in next join */
}).GroupJoin(C_TABLE,
ab => ab.B_TABLE.B_TABLE_Id,
c => c.B_TABLE_Id,
(ab, c) =>
new {
ab.tableAProperty1,
B_TABLEProperty2 = ab.B_TABLE.Property2,
C_TABLE = c.FirstOrDefault()
}).ToList();
LINQ Left Outer Join Multiple Tables with Group Count and Row Concatenation
Assuming you are using EF, and you have navigation properties set up, then your query would look like this:
var result = context.Parents
.Select(p => new {
p.ParentId,
p.ParentName,
p.ParentOccupation,
NumberOfGrandChildren = p.Children
.SelectMany(c => c.GrandChildren)
.Count(),
NamesOfGrandChildren = string.Join(", ", p.Children
.SelectMany(c => c.GrandChildren)
.Select(g => g.GrandChildName))
}).ToList();
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
};
Complex joins in Linq with multiple tables and LEFT OUTER JOIN
You should apply Left Join
this way:
join readers in db.Readers on readerdata.mac_address equals readers.mac_address into readersJ
from readers in readersJ.DefaultIfEmpty()
The full code:
var query = (
from tags in db.Tags
join tagreads in db.tag_reads on tags.epc equals tagreads.epc
join readerdata in db.ReaderData on tagreads.ReaderDataId equals readerdata.Id
join readers in db.Readers on readerdata.mac_address equals readers.mac_address into readersJ
from readers in readersJ.DefaultIfEmpty()
join locations in db.Locations
on new { ap = tagreads.antennaPort, rd = readerdata.Id }
equals new { ap = locations.AntennaId, rd = locations.ReaderId }
group tags by new { tags.TagFriendlyName, timestamp = tagreads.timeStamp, readerdata.mac_address } into grp
select new CurrentStatus()
{
TagName = grp.Key.TagFriendlyName,
LastSeen = grp.Key.timestamp,
LocationName = grp.Key.mac_address
}
)
.OrderByDescending(o => o.LastSeen)
Related Topics
Calculating Distance Between Two Latitude and Longitude Geocoordinates
Xmlserializer Giving Filenotfoundexception at Constructor
Dot Character '.' in MVC Web API 2 for Request Such as API/People/Staff.45287
The Entity Type <Type> Is Not Part of the Model for the Current Context
Binding List<T> to Datagridview in Winform
Validate Image from File in C#
Shouldserialize*() VS *Specified Conditional Serialization Pattern
How to Set Session Timeout in Web.Config
Prevent .Net Garbage Collection for Short Period of Time
Do You Have to Put Task.Run in a Method to Make It Async
Best Way in ASP.NET to Force Https for an Entire Site
Asynchronous Locking Based on a Key