Linq Outer Join with defaults on both sides

Question

I have a problem with an outer join. I found this very helpful stackoverflow article: linq-full-outer-join but I am having issues on my join when the data is retrieved with the error:

NotSupportedException: Unsupported overload used for query operator 'DefaultIfEmpty'.

Here is the code:

var query =
        from i2 in (from sel in (from i in (from a1 in Activities
                    where a1.ActivityDate >= DateTime.Parse("4/15/2011")
                    && a1.ActivityDate < DateTime.Parse("4/19/2011")
                    select new { 
                        a1.ActivityDate,
                        a1.RecID} )
        group i by new { i.RecID }
        into g
        select new {
            g.Key.RecID,
            MaxDate = g.Max(i => i.ActivityDate)})
        join a in Activities on 1 equals 1 
        where (sel.MaxDate == a.ActivityDate && sel.RecID == a.RecID) 
        select new { 
            a.RecID,  
            a.UserName, 
            ActivityDate = a.ActivityDate.Date, 
            Sum1 = (a.StatusID == 7) ? 1 : 0,
            Sum2 = (a.StatusID == 5) ? 1 : 0,
            Sum3 = (a.StatusID == 4) ? 1 : 0})
        group i2 by new {
                UserName = i2.UserName,
                ActivityDate = i2.ActivityDate
                }
        into g2 
        select new {
            JoinId = (string)(g2.Key.ActivityDate + "_" + g2.Key.UserName),
            ActivityDate = (DateTime)g2.Key.ActivityDate,
            UserName = (string)g2.Key.UserName,
            Sum1 = (int)g2.Sum(i2 => i2.Sum1),
            Sum2 = (int)g2.Sum(i2 => i2.Sum2),
            Sum3 = (int)g2.Sum(i2 => i2.Sum3),
        };
    var query2 = from s in ProdHistories
    where s.CompletedDate >= DateTime.Parse("4/15/2011") 
    && s.CompletedDate <= DateTime.Parse("4/19/2011")
    select new { 
        JoinId = (string)(s.CompletedDate + "_" + s.UserName),
        CompletedDate = (DateTime)s.CompletedDate,
        UserName = (string)s.UserName,
        Type1 = (int)s.Type1,
    Type2 = (int)s.Type2,
            Type3 = (int)s.Type3,
            Type4 = (int)s.Type4,
            Type5 = (int)s.Type5,
            Type6 = (int)s.Type6,
            Type7 = (int)s.Type7,
            Type8 = (int)s.Type8,
            };

    var joinLeft = from ph in query2
        join act in query 
        on ph.JoinId equals act.JoinId
        into temp 
        from act in temp.DefaultIfEmpty(new { 
            JoinId = (string)ph.JoinId,
            ActivityDate = (DateTime)ph.CompletedDate,
            UserName = (string)ph.UserName, 
            Sum1 = default(int),
            Sum2 = default(int),
            Sum3 = default(int),
             })
        select new { ph.UserName, 
            ph.CompletedDate,
            ph.Type1,
            ph.Type2,
            ph.Type3,
            ph.Type4,
            ph.Type5,
            ph.Type6,
            ph.Type7,
            ph.Type8,
            act.Sum1,
            act.Sum2,
            act.Sum3};

    query.Dump(); // successfully dumps (in LinqPad) data - no nulls
    query2.Dump(); // successfully dumps (in LinqPad) data - no nulls
    joinLeft.Dump(); // raises: NotSupportedException: Unsupported overload used for query operator 'DefaultIfEmpty'.

I tried the same outer join clause and use fixed data and it works:

 var query = new[]
 {
      new {     JoinId = "12345", ActivityDate = DateTime.Parse("1/1/2011"), UserName = "UID1", Sum1 = 10, Sum2 = 11, Sum3 = 12 },
      new {     JoinId = "23456", ActivityDate = DateTime.Parse("1/2/2011"), UserName = "UID2", Sum1 = 20, Sum2 = 21, Sum3 = 22 },
      new {     JoinId = "34567", ActivityDate = DateTime.Parse("1/3/2011"), UserName = "UID3", Sum1 = 30, Sum2 = 31, Sum3 = 32 },
 };

 var query2 = new[]
 {
      new {     JoinId = "12345", CompletedDate = DateTime.Parse("1/1/2011"), UserName = "UID1", Type1 = 110, Type2 = 111, Type3 = 112, Type4 = 113, Type5 = 114, Type6 = 115, Type7 = 116, Type8 = 117 },
      new {     JoinId = "23456", CompletedDate = DateTime.Parse("1/2/2011"), UserName = "UID2", Type1 = 210, Type2 = 211, Type3 = 212, Type4 = 213, Type5 = 214, Type6 = 215, Type7 = 216, Type8 = 217 },
      new {     JoinId = "45678", CompletedDate = DateTime.Parse("1/5/2011"), UserName = "UID4", Type1 = 310, Type2 = 311, Type3 = 312, Type4 = 313, Type5 = 314, Type6 = 315, Type7 = 316, Type8 = 317 },
 };

 var joinLeft = from ph in query2
           join act in query 
           on ph.JoinId equals act.JoinId
           into temp 
           from act in temp.DefaultIfEmpty(new { 
                JoinId = (string)ph.JoinId,
                ActivityDate = (DateTime)ph.CompletedDate,
                UserName = (string)ph.UserName, 
                Sum1 = default(int),
                Sum2 = default(int),
                Sum3 = default(int),
                 })
           select new { ph.UserName, 
                ph.CompletedDate,
                ph.Type1,
                ph.Type2,
                ph.Type3,
                ph.Type4,
                ph.Type5,
                ph.Type6,
                ph.Type7,
                ph.Type8,
                act.Sum1,
                act.Sum2,
                act.Sum3};

 joinLeft.Dump();

result:

UserName CompletedDate    Type1 Type2 Type3 Type4 Type5 Type6 Type7 Type8 Sum1 Sum2 Sum3 
UID1 1/1/2011 12:00:00 AM 110   111   112   113   114   115   116   117   10   11   12
UID2 1/2/2011 12:00:00 AM 210   211   212   213   214   215   216   217   20   21   22
UID4 1/5/2011 12:00:00 AM 310   311   312   313   314   315   316   317   0    0    0

I saw another stackoverflow article where Jon Skeet uses IEnumerable as part of a solution to this error in another context, but I'm not quite sure how to apply that.

Thanks for any clues!

Answer 1

I think the post you took the code from answers your question as well (bold is mine).

This works as written since it is in LINQ to Objects. If LINQ to SQL or other, the overload of DefaultIfEmpty() that takes in a default may not work. Then you'd have to use the conditional operator to conditionally get the values.

i.e.,

var leftOuterJoin = from first in firstNames
                    join last in lastNames
                    on first.ID equals last.ID
                    into temp
                    from last in temp.DefaultIfEmpty()
                    select new
                    {
                        first.ID,
                        FirstName = first.Name,
                        LastName = last != null ? last.Name : default(string),
                    };

Update from Robb

This is the answer, and I was able to get my code working by converting the IQueryable to a List using ToList().

My comment on update

Definitely this is another option if you don't want you joins to run on the database and you're comfortable with doing them on objects. However this may affect performance if there are really many objects (how many—it depends). Follow the usual optimization mantra: measure, measure and measure. If this is of no concern, ToList will work fine.