I am having problems figuring out the Linq
syntax for a left outer join within multiple joins. I want to do a left join on RunLogEntry
Table so I get records which match from this table as well as all of the Service Entries.
Can some one please correct my snytax?
var list = (from se in db.ServiceEntry
join u in db.User on se.TechnicianID equals u.ID
join s in db.System1 on se.SystemID equals s.ID
join r in db.RunLogEntry on se.RunLogEntryID equals r.ID
where se.ClosedDate.HasValue == false
where se.ClosedDate.HasValue == false
&& se.Reconciled == false
orderby se.ID descending
select new ServiceSearchEntry()
{
ID = se.ID,
ServiceDateTime = se.ServiceDateTime,
Technician = u.FullName,
System = s.SystemFullName,
ReasonForFailure = se.ReasonForFailure,
RunDate = r.RunDate
})
.Skip((page - 1) * PageSize);
You can use LINQ to perform a left outer join by calling the DefaultIfEmpty method on the results of a group join.
A Left Outer join returns all records from the left table and the matching record from the right table. If there are no matching records in the right table then it returns null. If we want to do a Left Outer join in LINQ then we must use the keyword "into" and method "DefaultIfEmpty".
When you use the LINQ join clause in the query expression syntax to combine two sets of related information, you perform an inner join. This means that you provide an expression that determines for each item in the first sequence, the matching items in the second.
Use the .DefaultIfEmpty
method on r
to perform a left join:
var list = (from se in db.ServiceEntry
join r in db.RunLogEntry on se.RunLogEntryID equals r.ID into joinRunLogEntry
from r2 in joinRunLogEntry.DefaultIfEmpty()
join u in db.User on se.TechnicianID equals u.ID
join s in db.System1 on se.SystemID equals s.ID
where se.ClosedDate.HasValue == false
where se.ClosedDate.HasValue == false
&& se.Reconciled == false
orderby se.ID descending
select new ServiceSearchEntry()
{
ID = se.ID,
ServiceDateTime = se.ServiceDateTime,
Technician = u.FullName,
System = s.SystemFullName,
ReasonForFailure = se.ReasonForFailure,
RunDate = (r2 == null ? (DateTime?)null : r2.RunDate)
})
.Skip((page - 1) * PageSize);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With