StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to convert the sql statement to linq (VB)
text
Body
copied!<p>the following sql statement works fine, but how can it convert into linq (VB) ?</p> <p>i had looked for some online tools, but doesn't work...</p> <p>thanks</p> <pre><code>SELECT distinct a.sid, a.DisplayName, CASE WHEN b.sID IS NOT NULL THEN 1 ELSE 0 END AS Type1, CASE WHEN c.sID IS NOT NULL THEN 1 ELSE 0 END AS Type2, CASE WHEN d.sID IS NOT NULL THEN 1 ELSE 0 END AS Type3 FROM dbo.vHRIS_EmailAlert a LEFT JOIN dbo.vHRIS_EmailAlert b ON a.sid = b.sid AND b.Type = 4 LEFT JOIN dbo.vHRIS_EmailAlert c ON a.sid = c.sid AND c.Type = 5 LEFT JOIN dbo.vHRIS_EmailAlert d ON a.sid = d.sid AND d.Type = 6 where a.CID=37 and (b.Type=4 or c.Type=5 or d.Type=6) </code></pre> <p>The following is my linq. i run my linq by LINQPad, it returns "syntax error"</p> <pre><code>(From a In db.vHRIS_EmailAlerts _ Group Join b In db.vHRIS_EmailAlerts _ On New With { a.SID, .Type = 4 } _ Equals New With { b.SID, .Type = CType(CLng(b.Type),Int64?) } Into b_join = Group _ From b In b_join.DefaultIfEmpty() _ Group Join c In db.vHRIS_EmailAlerts _ On New With { a.SID, .Type = 5 } _ Equals New With { c.SID, .Type = CType(CLng(c.Type),Int64?) } Into c_join = Group _ From c In c_join.DefaultIfEmpty() _ Group Join d In db.vHRIS_EmailAlerts _ On New With { a.SID, .Type = 6 } _ Equals New With { d.SID, .Type = CType(CLng(d.Type),Int64?) } Into d_join = Group _ From d In d_join.DefaultIfEmpty() _ Where _ CLng(a.CID) = 37 And _ (CLng(b.Type) = 4 Or _ CLng(c.Type) = 5 Or _ CLng(d.Type) = 6) _ Select _ SID = CType(a.SID,Int32?), _ a.DisplayName, _ Type1 = If( _ Not b.SID Is Nothing, 1, 0), _ Type2 = If( _ Not c.SID Is Nothing, 1, 0), _ Type3 = If( _ Not d.SID Is Nothing, 1, 0)) _ ).Distinct.ToList </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload