How to left join tables in LINQ TO SQL to get associated records

Question

I have a linq to sql statement where I am doing join with a bunch of tables , but I need to left join a table as we are not sure if there be any associated records.

We are left joing as we are not sure if every purchase will have a file associated.

Purchase Join - Invc Purchase Join - FileStore FileStore##Entity

select * from Purchase p
join Invc i on i.Invc_ID = p.Invc_ID
left join  FileStore##Entity fe on p.Purchase_ID = fe.Entity_ID
left join FileStore fs on fs.FileStore_ID = fe.FileStore_ID
where p.Purchase_ID = 53

My current linq to sql works for the regular join , how can I left join the file store and store entity table

PurchaseEntity purchase = (from p in TransactionalDbContext.Purchase
                join i in TransactionalDbContext.Invc
                    on p.InvcId equals i.InvcId
                where d.Purchase_Id == id
                select new DisputeEntity
                {
                    Purchase_Id = p.Purchase_Id,
                    InvcId = i.InvcId,
                    SubmittedDate = d.SubmitDt,
                    InvcNum = i.InvcNum
                    // get the file elements by left join file 
                }).FirstOrDefault();

Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Nov 13 '20 at 22:02

score 1 · Accepted Answer · answered Nov 13 '20 at 19:05

You can easily perform left or right joins by playing with the DefaultIfEmpy method.

Following your example:

from p in dbContext.Purchases
        join i in dbContext.Invc on p.InvcId equals i.InvcId
        join fe in dbContext.FileStoreEntities.DefaultIfEmpty() on p.Purchase_ID equals fe.Entity_ID
        join fs in dbContext.FileStores.DefaultIfEmpty() on fe.FileStore_ID equals fs.FileStore_ID

How to left join tables in LINQ TO SQL to get associated records

1 Answers1