LINQ to Entities Join on Nullable Field where Null Implies "Match All"

Question

I am attempting to run the following LINQ query using Entity Framework 5:

int taskId = 2;

query = from a in Table_A
        where a.StatusCode != "DONE"
           && a.Inbound
        join b in Table_B
            on a.Id equals b.Id_Table_A
        join c in Table_C
            on a.State equals (c.State ?? a.State)
        where 2 == c.Id_Task
           && b.DataType == c.DataType
        select a.Id;

The line that is causing me problems is:

on a.State equals (c.State ?? a.State)

The "State" field in Table_C is nullable... and when it is null, it is used to imply "all states". As such, when "c.State" is null I want the record to be matched. If I were to write this in SQL, I would use the following:

JOIN Table_C ON Table_A.State = ISNULL(Table_C.State, Table_A.State)

Unfortunately, I am being given the following error:

The name 'a' is not in scope on the right side of 'equals'. Consider swapping the expressions on either side of 'equals'.

I will be grateful to anybody who can let me in on the secret to getting this working.

Thanks.

Answer 1

You can modify your code like:

int taskId = 2;

query = from a in Table_A
        where a.StatusCode != "DONE"
           && a.Inbound
        join b in Table_B
            on a.Id equals b.Id_Table_A
        from c in Table_C
        where 2 == c.Id_Task
           && b.DataType == c.DataType
           && (c.State == null || a.State.Equals(c.State))
        select a.Id;

A join is essentially a where clause, so here we can use the where clause due to the restrictions with join.

Answer 2

I managed to get this to work by moving the "DataType" check from the WHERE to the JOIN, and moving the "State" check from the JOIN to the WHERE. The resulting code that worked as I expected is as follows:

query = from a in Table_A
        where a.StatusCode != "DONE"
           && a.Inbound
        join b in Table_B
            on a.Id equals b.Id_Table_A
        join c in Table_C
            on b.DataType equals c.DataType
        where 2 == c.Id_Task
            && (c.State ?? a.State) == a.State
        select a.Id;

Many thanks to everybody who has taken a look at this for me. :)

Answer 3

You can use "from" syntax instead of "join"

from a in TableA
from b in TableB
.Where(x => (x.Buy ?? a.Buy) == a.Buy
        && (x.Parity ?? a.Parity) == a.Parity)