Linq left outer join not working using DefaultIfEmpty

Question

Using the technique found on the MSDN article "How to: Perform Left Outer Joins (C# Programming Guide)", I attempted to create a left outer join in my Linq code. The article mentions using the DefaultIfEmpty method in order 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 executes, however, it does so as if the outer join has not been specified.

In our database, AgentProductTraining is a collection of courses our agents have taken. Normally you cannot enter a Course onto it's appropriate table without entering a corresponding value into the CourseMaterials table. However, occasionally this may happen, so we want to make sure we return results even when a Course is listed in AgentProductTraining without any corresponding 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
            };

Answer 1

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 must use your own "subpet", I rewrote your code using the submat variable:

        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
                                  };