Posts Tagged 'LINQ To SQL'

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