sql to LINQ

Question

how can i convert bellow sql query to LINQ ? SELECT dbo.AD_UserInReaders.ReaderID FROM dbo.AD_UserInReaders where sUserID=2 and ReaderID NOT IN (SELECT dbo.TB_USER_DEVICE.nDeviceID FROM dbo.TB_USER_DEVICE WHERE (dbo.TB_USER_DEVICE.nUserIdn = 2)) Thank you

Ramesh Maruthi · Answer

Hi Thilanka I didn't understand your sql query your selecting the nDeviceID from the second query and checking with readerid ? how this is possible ? give me one clarity does even dbo.TB_USER_DEVICE consists of readerid column? other wise just checking the numbers ? In case if your sql query is correct than you can write the above query in Linq this way first select all the nDeviceID to a list for example if nDeviceID is long than List deviceids = dbo. TB_USER_DEVICEs.Where(kl => kl. nUserIdn == 2).Select( p =>p. nDeviceID ).ToList(); second take the reader id which is not in the deviceids list for example if reader id is long than long ReaderID = dbo. .AD_UserInReaders.where( k => ! deviceids.Contains(k.ReaderID ) && k. sUserID == 2).Select (ko =>ko. ReaderID).FirstOrDefault();

Ramesh Maruthi · Answer

Hello Thilanka Even this Linq also works for you from k in dbo.AD_UserInReaders Where k.sUserID== 2 && !(from s in dbo.TB_USER_DEVICE Where s.nUserIdn == 2 Select new {s.nDeviceID}).Contains(new {nDeviceID =k.ReaderID }) Select new { k.ReaderID} What Riddhi said is right I agree but don't depend on tools it makes ur thinking lazy

Riddhi Valecha · Answer

Hi... There is a tool - Linqer. This tool converts all SQL Queries to LINQ. For LINQ Queries I do the following - 1. Make and run the SQL Query in the SQL Editor in SQL Server 2008/2005. 2. If I get the proper result then - Connect Linqer with my Application and copy paste the SQL Query. Just click on Convert button. It will give the SQL Query to LINQ. Hope this helps.

thilanka ishara · Answer

I tried it but getting following Error please help

thilanka ishara · Answer

Thanks but i got this error why is this

Ramesh Maruthi · Answer

var employeeidorders = (from k in Orders where k.OrderID = 001 select k.EmployeeId ).ToList(); List result = Employee.Where(dbl =>!employeeidorders.Contains(dbl.EmployeeID))

thilanka ishara · Answer

i cant uderstand Ramesh answe colud you please help me with Select * From Employee where EmployeeId not in ( Select EmployeeId in orders where orderID==001 )

Dipankar Biswas · Answer

http://blogs.msdn.com/b/vbteam/archive/2007/11/19/converting-sql-to-linq-part-1-the-basics-bill-horst.aspx