Converting "is null" into a linq to sql statement

Question

I am having trouble replicating the following sql as a LINQ statement

select TableA.* from TableA left outer join TableAinTableB on TableA.Id = TableAId where TableBId is null

The following returns no lines

from TableA in db.TableA join AinB in db.TableAinTableB on TableA.Id equals TableAId where AinB.TableBId == null select TableA

Also tried and a few other things that didn't work.

from TableA in db.TableA join AinB in db.TableAinTableB on TableA.Id equals TableAId where AinB == null select TableA

TableAinTableB is a many to many table. The query I want will pull all the records from TableA that have no records in the middle table. My sql does what I want but I have no idea how to convert it to LINQ to SQL.

I ended up working around it by just doing a db.ExecuteQuery("working sql"); But I would like to know if the query is possible in LINQ and how to write it, or a pointer to a document that covers this scenario. My searching did not uncover anything I found useful.

Answer 1

You can use the DefaultIfEmpty to simulate an outer join. Check out this sample.

In your example it's something like:

var q = from a in TableA
            join b in TableB on a.Id equals b.Id into g
            from b in g.DefaultIfEmpty()
            select a;

Answer 2

For "IS NULL" try something like this

from TableA in db.TableA 
join AinB in db.TableAinTableB 
on TableA.Id equals TableAId 
where object.Equals(AinB.TableBId, null)
select TableA;

Answer 3

but how will you handle null string? there is no getdefaultvalue.

if your table contains field with null value and the type is nvarchar