How to combine inner join and left join in Entity Framework

Question

I am using DBContext API from EF 4.1. Consider following entity model (A, B, E, D are entities)

A: aId

B: aId, cId

E: eId, aId

D: eId, cId, Data

What I want is equivalent of below sql query

SELECT
   B.aId,
   B.cId,
   COALESCE(M.Data, [default value])
FROM
   B LEFT OUTER JOIN
   (
      SELECT
         E.aId,
         D.cId,
         D.Data  
      FROM
         E INNER JOIN D ON E.eId = D.eId
   ) M
   ON B.aId = M.aId AND B.cId = M.cId

Its simple to have left join on B, E & D but I found that I cannot solve above query. I have tried linq form of what I think would be an equivalent query

// inner join equivalent
var ee = db.E.Join(db.D, e => e.eId, d => d.eId,
    (e, d) => new { e.aId, e.eId, d.cId, d.Data });

// left outer join
var o = from c in db.B
        join e in ee on new { c.aId, c.cId }
            equals new { e.aId, e.cId } into temp
        from m in temp.DefaultIfEmpty()
        select new
        {
            c.aId,
            c.cId,
            Data = null != m ? m.Data : [default value]
        };

However, this fails when I call o.ToString() with following exception details:

System.ArgumentException: The argument to DbIsNullExpression must refer to a primitive or reference type. at System.Data.Common.CommandTrees.ExpressionBuilder.Internal.ArgumentValidation.ValidateIsNull(DbExpression argument, Boolean allowRowType) at System.Data.Objects.ELinq.ExpressionConverter.EqualsTranslator.CreateIsNullExpression(ExpressionConverter parent, Expression input) at System.Data.Objects.ELinq.ExpressionConverter.EqualsTranslator.TypedTranslate(ExpressionConverter parent, BinaryExpression linq) at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)

... [more stack trace out here]

at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq) at System.Data.Objects.ELinq.ExpressionConverter.Convert()
at System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable1 forMergeOption) at System.Data.Objects.ObjectQuery.ToTraceString() at System.Data.Entity.Internal.Linq.InternalQuery1.ToString() at System.Data.Entity.Infrastructure.DbQuery`1.ToString()

I have tried to form similar query using extension methods but had the same exception. What am I missing here?

---------------------------------------------------------------------------------

EDIT:

It appears that issue was due to line

Data = null != m ? m.Data : [default value]

I have modified it to

   Data = m

And it started working. I have to move null checking logic at the place where I am using the result. Now, I am wondering what can be the cause of exception? From exception details, it appears that it cannot figure out m (which is an anonymous type) as reference type. Is this behavior documented somewhere?

Answer 1

Well you were null checking a joined entity, which doesn't make sense in terms of sql. The following should be interpreted properly as a coalesce:

Data = m.Data ?? [default value]