How do I convert this SQL query to LINQ to SQL

Question

I have following SQL query and I want to convert it to LINQ. I want to use a subquery or the LINQ include syntax and don't want to use join:

SELECT Count(*) AS CountOfRecs 
FROM tblAcc a
INNER JOIN tblAccOwner o ON
  a.[Creditor Registry ID] = o.[Registry ID] AND
  a.[Account No] = a.[Account No] 
WHERE 
  (a.[Account Owner ID] = 731752693037116688) AND 
   a.[Account Type] NOT IN ('CA00', 'CA01', 'CA03', 'CA04', 'CA02',
                            'PA00', 'PA01', 'PA02', 'PA03', 'PA04')) AND
  (DATEDIFF(mm, a.[State Change Date], GETDATE()) <= 4 OR
    a.[State Change Date] IS NULL AND 
   (a.[Account Type] IN ('OD','CL00','PL00') OR a.[Account Type] LIKE '%hala%'))

Answer 1

You can use LinqPad or Linqer. I would try to convert it in LinqPad, but I would have to have the database.

Answer 2

Converting this to Linq should be pretty easy - although I think you should use a join if you want the same query!

First create your Linq2Sql model and import your 2 tables, then your query will have the general structure

var query = from acc in db.Accs
    join accOwner in db.AccOwners on {acc.regId, acc.AccountNo} equals {accOwner.regId, accOwner.AccountNo}
    where // your where conditions
          // - these should all convert quite easily
          // - just translate them one by one
    select acc;

var count = query.Count();

---

If you really need to use subquery then since you are an expert in SQL, then write the query out in SQL first, then test it, then translate the query across to Linq, then test it - taking the translation line by line you'll be fine - and you'll also find Intellisense is wonderful!

---

Please also realise that if you use .Include() then you are probably using join within the generated SQL anyway.