Convert SQL to Linq for left join with NULL in right

Question

How can I convert the below SQL statement to Linq:

SELECT A.Application, B.Filter
FROM Applications A left join Filters B on A.Application = B.Filter OR 
B.Filter is null

I have written the following but am unable to figure out how to add "OR B.Filter is NULL" to it:

var filteredApproved = (from f in 
(from a in Applications
join b in Filters on a.Application equals b.Filter into rss
from ss in rss.DefaultIfEmpty()
select new {
a.Application,
Filter = (b == null ? 0: b.Filter)
})

For example, if the Applications and Filter table are as below:

Application
---------
A
B

Filter
--------
A
null

I am wanting the following result

Application   Filter
---------     ------
A             A
A             null
B             null

The Linq query returns:

Application   Filter
---------     ------
A             A
B             null

It does not return the second row that SQL is returning. I need to add " OR B.Filter is NULL" to linq.

How are your objects defined, and what relationships do you have between them? — krillgar, Jul 17 '17 at 17:17
So you want a left outer join in LINQ (and you could have used it in SQL!) That makes this an exact duplicate of [this question](https://stackoverflow.com/questions/3404975/left-outer-join-in-linq) — oerkelens, Jul 17 '17 at 17:19
For example, if the Applications and Filter table are as below: Application --------- A B Filter -------- A null I want the following result Application Filter --------- ------ A A A null B null — Sanjeev, Jul 17 '17 at 17:23

score 2 · Accepted Answer · answered Jul 17 '17 at 18:10

LINQ supports only equijoins. The only option for other type of joins is the correlated where (the regular where with condition accessing properties of both related sequences), or in case of the left outer join - a correlated subquery:

var result =
    from a in Applications
    from b in Filters
        .Where(b => a.Application == b.Filter || b.Filter == null)
        .DefaultIfEmpty()
    select new
    {
        a.Application,
        Filter = b == null ? null : b.Filter
    };

Convert SQL to Linq for left join with NULL in right

1 Answers1