Linq left outer join not working
Using the technique found in the MSDN article "How to: Doing a Left Outer Join (C # Programming Guide)" , I tried to create a left outer join in my Linq code. The article mentions using a method DefaultIfEmpty
to create a left outer join from a group join. Basically, it instructs the program to include the results of the left (first) collection, even if there are no results in the right collection.
The way this program is executed, however, makes it as if no outer join were specified.
In our database AgentProductTraining
, this is a set of courses that our agents have taken. Usually you cannot enter Course
into the corresponding table without entering the corresponding value into the table CourseMaterials
. However, sometimes this can happen, so we want to make sure that we return results even if Course
specified in AgentProductTraining
without relevant information in CourseMaterials
.
var training = from a in db.AgentProductTraining
join m in db.CourseMaterials on a.CourseCode equals m.CourseCode into apm
where
a.SymNumber == id
from m in apm.DefaultIfEmpty()
where m.EffectiveDate <= a.DateTaken
&& ((m.TerminationDate > a.DateTaken) | (m.TerminationDate == null))
select new
{
a.AgentProdTrainId,
a.CourseCode,
a.Course.CourseDescription,
a.Course.Partner,
a.DateTaken,
a.DateExpired,
a.LastChangeOperator,
a.LastChangeDate,
a.ProductCode,
a.Product.ProductDescription,
m.MaterialId,
m.Description,
a.Method
};
source to share
The MSDN example uses a new variable subpet
:
var query = from person in people
join pet in pets on person equals pet.Owner into gj
from subpet in gj.DefaultIfEmpty()
select new { person.FirstName, PetName = (subpet == null ? String.Empty : subpet.Name) };
So you have to use your own "subtitle", I rewrote your code with a variable submat
:
var training = from a in db.AgentProductTraining
join m in db.CourseMaterials on a.CourseCode equals m.CourseCode into apm
where
a.SymNumber == id
from submat in apm.DefaultIfEmpty()
where
(submat.EffectiveDate <= a.DateTaken || submat.EffectiveDate == null) &&
(submat.TerminationDate > a.DateTaken || submat.TerminationDate == null)
select new
{
a.AgentProdTrainId,
a.CourseCode,
a.Course.CourseDescription,
a.Course.Partner,
a.DateTaken,
a.DateExpired,
a.LastChangeOperator,
a.LastChangeDate,
a.ProductCode,
a.Product.ProductDescription,
MaterialId = (submat==null?-1:submat.MaterialId),
Description = (submat==null?String.Empty:submat.Description),
a.Method
};
source to share