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:


SELECT
 CaseNumber,
 FirstName,
 LastName,
 CASE WHEN Exists(Select 1 from Waiver.CaseHistory WHERE CaseNumber = t1.CaseNumber) THEN 'Yes'
   ELSE 'No' END
FROM
 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 !!!

Cheers,

Raja

Advertisements

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:

    SELECT
    t1.CaseNumber,
    t1.FirstName,
    t1.LastName,
    CASE WHEN ch.CaseNumber IS NOT NULL THEN ‘Yes’ ELSE ‘No’ END
    FROM
    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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s





%d bloggers like this: