Case and Exists in TSQL to LINQ To SQL

I had a pretty interesting scenario for which I needed a solution. I wanted to translate the following query to LINQ to SQL:

 CASE WHEN Exists(Select 1 from Waiver.CaseHistory WHERE CaseNumber = t1.CaseNumber) THEN 'Yes'
   ELSE 'No' END
 Waiver.Cases t1

After a bit of searching I finally found a way to exactly do it. I have included that below:

var objQuery = from objWaiver in objDataContext.Cases
      select new
       CN = objWaiver.CaseNumber,
       FN = objWaiver.FirstName,
       LN = objWaiver.LastName,
       HS = ((from objHistory in objDataContext.CaseHistories select objHistory.CaseNumber).Contains(objWaiver.CaseNumber) ? "Yes" : "No")

Hope this helps.

Happy Programming !!!



1 Response to “Case and Exists in TSQL to LINQ To SQL”

  1. 1 Trent Gardner May 6, 2011 at 1:53 am

    Good example, I would have thought rewriting the SQL to use a simple left join would have been easier. Something like:

    CASE WHEN ch.CaseNumber IS NOT NULL THEN ‘Yes’ ELSE ‘No’ END
    Waiver.Cases t1
    LEFT JOIN Waiver.CaseHistory ch ON ch.CaseNumber = t1.CaseNumber

    Would give you a much more readable outcome that using nested queries.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: