CODEWITHSUNDEEP
c#sqllinqlinq-to-sqlsql-to-linq-conversion
Slahuddin Chaudhary
Slahuddin Chaudhary

Reputation: 156

Converting SQL subquery to LINQ

I am new to LINQ and trying to convert an SQL subquery to lINQ. Can we write subqueries like SQL in LINQ?

here is the database 

Table EMP                                                                                                  
Name       Null?         Type

EMPNO      NOT NULL      NUMBER(4)
NAME                    VARCHAR2(10)
JOB                     VARCHAR2(9)
MGR                     NUMBER(4)
HIREDATE                DATE
SAL                     NUMBER(7,2)
COMM                    NUMBER(7,2)
DEPTNO                  NUMBER(2)


Table Dpt
Name        Null?        Type
DEPTNO      NOT NULL NUMBER(2)
DNAME                VARCHAR2(14)
LOC                  VARCHAR2(13)

Here is the SQL query

SELECT *
FROM emp
WHERE deptno IN (SELECT deptno
                 FROM dept
                 WHERE dname = 'RESEARCH');

Here is my effort:

var r = (from query in conn.EMPs
         where (query.DEPTNO == (from q in conn.DEPTs 
                                 where q.DNAME == "RESERCH"
                                 select q.DEPTNO)) 
         select new
         {
          query
         }).ToList();

Upvotes: 0

Views: 121

Answers (2)

Nascent
Nascent

Reputation: 48

var departmentNos = dept.Where(dpt => dpt.dname == 'RESEARCH').Select(dptNo => dptNo.deptno);

var employees = emp.Where(e => departmentNos.Contains(e.deptno));

employees is the final outcome.

Upvotes: 1

jdweng
jdweng

Reputation: 34421

I like using joins :

var r = (from query in conn.EMPs
        join q in conn.DEPTs on query.deptno equals q.DEPTNO 
        select new { query = query, q = q})
        .Where(x => x.q.DNAME == "RESEARCH")
        .ToList();

Upvotes: 1

Related Questions