T-SQL to LINQ conversion

Question

I have the following SQL statement

SELECT 
    c.CorpSystemID, c.SystemName  , 
    case when a.TaskItemID is NULL then 'false' else 'true' end as Assigned
FROM CorpSystems c 
LEFT OUTER JOIN
     (SELECT CorpSystemID, TASKItemID 
      FROM AffectedSystems 
      where TASKItemID = 1) a ON c.CorpSystemID = a.CorpSystemID

Can anyone please help me to convert this statement to LINQ?

Thank you.

Have you tried something by yourself? (https://www.google.ca/search?ix=hca&sourceid=chrome&ie=UTF-8&q=linq+to+sql+examples) — Francis P, Apr 11 '12 at 13:13
Showing you the LINQ query is only somewhat useful. You need to gather the context either in Memory or via a remote LINQ provider like LINQ to SQL or entity framework to make the LINQ function in place of the T-SQL. This is why its useful to give it a shot yourself first. — P.Brian.Mackey, Apr 11 '12 at 13:20

Mathew Thompson · Accepted Answer · 2012-04-11T14:16:58.920

3

Ok so assume you've got a list of your CorpSystem objects in a variable called Corpsystems and a list of your AffectedSystem objects in a variable called AffectedSystems. Try the following:

Edit: For a join on all Affected Systems, try this:

var matches = from c in CorpSystems
              join a in AffectedSystems on c.CorpSystemId equals a.CorpSystemId into ac
              from subSystem in ac.DefaultIfEmpty()
              select new
                     {
                         c.CorpSystemId,
                         c.SystemName,
                         Assigned = subSystem != null && subSystem.TaskItemId != null
                     };

Or for just AffectedSystems that have a TaskItemId of 1:

var matches = from c in CorpSystems
              join a in AffectedSystems.Where(as => as.TaskItemId == 1)
                  on c.CorpSystemId equals a.CorpSystemId into ac
              from subSystem in ac.DefaultIfEmpty()
              select new
                     {
                         c.CorpSystemId,
                         c.SystemName,
                         Assigned = subSystem != null && subSystem.TaskItemId != null
                     };

edited Apr 11 '12 at 14:16

answered Apr 11 '12 at 13:20

Mathew Thompson

55,877
15
127
148

1

he has a left outer join not an inner join – Darren Apr 11 '12 at 13:22
this creates inner join not outer – Yuri Apr 11 '12 at 13:30
@user1313789 Nah it's a left outer join. See here: http://msdn.microsoft.com/en-us/library/bb397895.aspx although I did edit unless you are using the code before I edited? – Mathew Thompson Apr 11 '12 at 13:30
on the line where a.TaskItemID == 1 it creates error 'a does not exists' – Yuri Apr 11 '12 at 13:33
this is not valid 'ac.TaskItemID == 1' – Yuri Apr 11 '12 at 13:36
@user1313789 DOH, check my new edit, sorry I should have used the `subSystem` variable instead of ac, as I've projected into it. – Mathew Thompson Apr 11 '12 at 13:44
CorpSystems has 7 records and I'm expecting all 7 back, instead I got 4 records? But thank you for trying:-) – Yuri Apr 11 '12 at 13:54
That's probably because of the where clause, if you take that out you should get them all. Do you need only AffectedSystems with a TaskItemId of 1? – Mathew Thompson Apr 11 '12 at 14:12
Check my edit, I've done both with a check where the AffectedSystem TaskItemId is 1 and one without the check on the TaskItemId. – Mathew Thompson Apr 11 '12 at 14:17
No problem! Glad it helped, we got there in the end :) hehe – Mathew Thompson Apr 11 '12 at 15:56

score 1 · Answer 2 · edited May 23 '17 at 11:49

1

See the answers to the following SO question SQL to LINQ Tool, assuming that you do not want to go through the process by hand.

edited May 23 '17 at 11:49

Community

1
1

answered Apr 11 '12 at 13:19

Joshua Drake

2,704
3
35
54

T-SQL to LINQ conversion

2 Answers2