sql to LINQ

thilanka ishara

12y1.7kLINQ

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

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ramesh Maruthi12y ago
Accepted 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 Maruthi12y ago
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 Valecha12y ago
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 ishara12y ago
I tried it but getting following Error please help
thilanka ishara12y ago
Thanks but i got this error, why is this,
Ramesh Maruthi12y ago
var employeeidorders = (from k in Orders
where k.OrderID = 001
select k.EmployeeId ).ToList();
List result = Employee.Where(dbl =>!employeeidorders.Contains(dbl.EmployeeID))
thilanka ishara12y ago
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 Biswas12y ago
http://blogs.msdn.com/b/vbteam/archive/2007/11/19/converting-sql-to-linq-part-1-the-basics-bill-horst.aspx