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:

 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 !!!