Linq To Sql Join with multiple conditions and table

Question

how could i convert this sql to Linq to sql

SELECT   v.Id, c.Id, bt.*
FROM BankTransaction bt
LEFT OUTER JOIN Customer c ON (bt.ObjectId = c.Id And bt.TransactionForObjectId = 1)
LEFT OUTER JOIN Vendor v ON (bt.ObjectId = v.Id And bt.TransactionForObjectId = 2)
LEFT OUTER JOIN AccountTitle ac ON (bt.ObjectId = ac.Id And bt.TransactionForObjectId = 3)

i am trying different linq to sql queries but didn't get the results — Ubaid Ur Rehman, May 23 '21 at 15:14
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. More specifically, `join c in Customer on new { bt.ObjectId, bt.TransactionForObjectId } equals new { ObjectId = c.Id, TransactionForObjectId = 1 } into cj from c in cj.DefaultIfEmpty()` — NetMage, May 27 '21 at 00:57

score 0 · Answer 1 · answered May 23 '21 at 15:17

this is what i am trying so far

var tran = await (
                        from bankTrans in InventoryContext.BankTransaction.Where(x => x.BankId == bank.Id && x.Date <= BankTransaction.Date)
                        from ven in InventoryContext.Vendor.Where(x => bankTrans.ObjectId == x.Id && bankTrans.TransactionForObjectId == (int)TransactionFor.Vendor)
                        .DefaultIfEmpty()
                        from cus in InventoryContext.Customer.Where(x => bankTrans.ObjectId == x.Id && bankTrans.TransactionForObjectId == (int)TransactionFor.Customer)
                        .DefaultIfEmpty()
                        from other in InventoryContext.AccountTitle.Where(x => bankTrans.ObjectId == x.Id && bankTrans.TransactionForObjectId == (int)TransactionFor.Other)
                        .DefaultIfEmpty()
                        select new
                        { bankTrans, ven, cus, other, cash }).ToListAsync();

And what result do you get? You have extra filters here that are not in the SQL version `Where(x => x.BankId == bank.Id && x.Date <= BankTransaction.Date)` — Charlieface, May 23 '21 at 15:29
@Charlieface let's i have tried this without the where clause but didn't get the desired result. — Ubaid Ur Rehman, May 23 '21 at 15:38

Linq To Sql Join with multiple conditions and table

1 Answers1