Geek asks and answers

Convert T-SQL to LINQ

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 someone help me convert this expression to LINQ?

Thank.

+3
source share
2 answers

So, suppose you have a list of your objects CorpSystemin a variable named Corpsystemsand a list of your objects AffectedSystemin a variable named AffectedSystems. Try the following:

Edit: to connect to all affected systems, try the following:

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
                     };
+2
source

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

+1
source

More articles:


All Articles