Left Join If A Certain Field Is A Certain Value?

Question

I'm having a difficult time building a left join query that does something like this:

var Results =
    from a in Db.Table1
    join b in Db.Table2 on a.Id equals b.Id into c //IF b.Value2 == 1
    from d in c.DefaultIfEmpty()
    select new {Table1 = a};

The problem I'm having is that I simply can't add "where b.Value2==1" before the "select" because this will exclude all joined rows with b.Value2 != 1.

What I want to have happen is for the columns to join only if b.Value2==1, and if not, it will keep the row (instead of excluding them as the where described above would), but just not join the two columns.

I hope this makes sense and there is a way to do this, thanks!

Edit:

Table1
ValueA=1,ValueB=1
ValueA=2,ValueB=2

Table2
ValueB=1,ValueC=1
ValueB=2,ValueC=2

Desired Result (joining on Table1.ValueB==Table2.ValueB Where Table2.ValueC == 2, still include this row, but just don't join it to Table2):
ValueA=1, ValueB=1, ValueC=null //ValueC=null because where Table2.ValueB==1,ValueC != 2
ValueA=2, ValueB=2, ValueC=2

Answer 1

add the where, then add another query that's just for b.Value2 != 1, merge both queries?

Answer 2

This should work, basically create a dummy var and join on it. The problem is this doesn't make for a friendly push down operation to the DB.

var Results =
    from a in Db.Table1
      join b in Db.Table2 on 
               new { ID= a.Id, Bit = true } 
               equals 
               new { ID = b.Id, Bit = b.Value ==1 } 
      into c 
      from d in c.DefaultIfEmpty()
      select new {
           Table1 = a, 
           Table2= d  //will be null if b.Value is not 1
      };

Answer 3

Create two linq queries, which will be composed into one sql query:

The first query will do a regular outer join

var partResult= from a in Db.Table1
    join b in Db.Table2 on a.Id equals b.Id into c
    from d in c.DefaultIfEmpty()
    select new { Table1 = a, Table2=d};

The second query will select all entries from the first query where Table2 is null or Table2.Id==1

var result = from obj in partResult
    where (obj.Table2 == null || obj.Table2.Id == 2)
    select obj.Table1;

This will be composed into a single select statement when the queries execute:

SELECT [t0].[Id]
FROM [dbo].[Table1] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1].[Id]
FROM [dbo].[Table2] AS [t1]
) AS [t2] ON [t0].[Id] = [t2].[Id]
WHERE ([t2].[test] IS NULL) OR ([t2].[Id] = 2)

if Table 1 has Ids 1, 2, 3, 4, 5, 6 and Table2 has Ids 1, 2, 3, 4 the result will be 1,5,6