Left join with And condition Linq to SQL

Question

How to convert following sql query to linq to sql?

select c.ClientID , c.ClientName , case when cca.clientID >0  then 1 else 0 end 'ClientAccess'  from Clients c
left join clientcontactaccess cca ON cca.clientid = c.ClientID and cca.ClientContactID = 2141
where c.GroupID = 1

I have done this so far but don't know how to handle "and cca.ClientContactID = 2141" condition;

dynamic query = (from c in db.Clientdb.ClientContactAccesscca.ClientIDc.ClientIDGroupfrom cca in GroupDetails.DefaultIfEmpty()where c.GroupID == 1c.ClientIDc.ClientNamecca.ClientID.ToString == null ? 0 : 1);

See webpage left outer join : https://code.msdn.microsoft.com/LINQ-Join-Operators-dabef4e9 — jdweng, Apr 22 '16 at 12:07
thanks for the link but can't see any left join example with 'And' condition. — user1263981, Apr 22 '16 at 13:20
There is a nice tool named [Linqer](http://www.sqltolinq.com/). Linqer is a SQL to LINQ conversion tool. It helps learning LINQ and convert existing SQL statements. — Siyavash Hamdi, Apr 22 '16 at 13:57

score 0 · Answer 1 · answered Apr 22 '16 at 13:37

I ended up using where clause with join (second line)

Dim query = (From c In db.Client _
                 Group Join cca In db.ClientContactAccess.Where(Function(cca) cca.ClientContactID = _contactID) On cca.ClientID Equals c.ClientID _
                 Into GroupDetails = Group _
                 From cca In GroupDetails.DefaultIfEmpty() Where c.GroupID = 1
                 Select c.ClientID, c.ClientName, ClientAccess = If(cca.ClientID.ToString Is Nothing, 0, 1))

If someone has better solution then please do post as it would be increase the knowledge plus i am new to linq.

Based on all of the other questions that are exactly like this, I'd say you're on the right track. — pseudocoder, Apr 22 '16 at 13:47

Left join with And condition Linq to SQL

1 Answers1