CODEWITHSUNDEEP
sqlentity-frameworklinq
Vishwash Roy
Vishwash Roy

Reputation: 281

How to convert left join sql query to linq to sql

I have following query in SQL. I want the same operation from linq to sql.

select count(bs.sampleId),s.sampleCode from prod.Samples s
LEFT join prod.BlockedSamples bs on bs.sampleId = s.sampleId
group by s.sampleCode
having count(bs.sampleId)>0

Relation between these two tables is 1 to many. 1 sample can have multiple entries in Blocked Sample.

Upvotes: 0

Views: 67

Answers (2)

CodeSmith
CodeSmith

Reputation: 3197

For this and future efforts of converting SQL to LINQ you can use:

sqltolinq

Upvotes: 0

jitender
jitender

Reputation: 10429

What about

from s in context.Samples 
    join bs in context.BlockedSamples  on s.sampleId 
    equals bs.sampleId into  ps
    from p in ps.DefaultIfEmpty()
 group p by s.sampleCode into grouped where grouped.Count(t=>t.sampleId  != null)>0
 select new {key=grouped.Key,Count = 
 grouped.Count(t=>t.sampleId  != null)}

Upvotes: 2

Related Questions