user1334007 user1334007 - 3 months ago 14
SQL Question

Entity framework join with null condition

I have a SQL query that I'm trying to convert to an Entity Framework expression. One of the join clauses has a test for null and I'm not sure how to express that in EF. Here is the SQL query, notice that the join actually has multiple conditions:

SELECT T1.* FROM
FROM product T1
INNER JOIN T2 ON T1.pk1 = T2.fk1
INNER JOIN T3 ON T2.pk2 = T3.fk2 AND T3.x IS NULL <-- this is the issue


Out of blind hope for something simple, I tried this EF expression:

from t1 in ctx.T1
join t2 in ctx.T2 on t1.pk1 equals t2.fk1
join t3 in ctx.T3 on t2.pk2 equals t3.fk2 && t3.x == null
select t1


The compiler doesn't like the
&& t3.x == null
part. It says


Error CS0019 Operator '&&' cannot be applied to operands of type 'int' and 'bool'


So I tried this (note that T3.x is a nullable date field):

from t1 in ctx.T1
join t2 in ctx.T2 on t1.pk1 equals t2.fk1
join t3 in ctx.T3 on new { A = t2.pk2, B = null as DateTime? } equals new { A = t3.fk2 && B = t3.x }
select t1


which compiles, but when I run it I get


Unable to create a null constant value of type 'System.Object'. Only entity types, enumeration types or primitive types are supported in this context.


So how might I go about this?

Update



As Ivan Stoev pointed out, the issue was that I was using
null as DateTime?
rather than
(DateTime?)null
. So the working query looks like this:

from t1 in ctx.T1
join t2 in ctx.T2 on t1.pk1 equals t2.fk1
join t3 in ctx.T3 on new { A = t2.pk2, B = (DateTime?)null } equals new { A = t3.fk2 && B = t3.x }
select t1

Answer

Either move the condition to where clause (for inner joins it really doesn't matter):

join t3 in ctx.T3 on t2.pk2 equals t3.fk2
where t3.x == null

or use the following

join t3 in ctx.T3
    on new { A = t2.pk2, B = (DateTime?)null }
    equals new { A = t3.fk2, B = t3.x }