Converting SQL subquery to LINQ

Question

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();

I had done this using joins but I am wondering can we do this using subqueries. if yes, then what's the right format because I am getting wrong syntax error while executing this. — Slahuddin Chaudhary
– Slahuddin Chaudhary, Commented Jul 1, 2018 at 12:27
where ((from q in conn.DEPTs where q.DNAME == "RESERCH" select q.DEPTNO).Contains(query.DEPTNO)) — Chetan
– Chetan, Commented Jul 1, 2018 at 12:30
Please edit your question. And I mean the classes not the database tables. — Gert Arnold
– Gert Arnold, Commented Jul 2, 2018 at 6:53

jdweng · Accepted Answer · 2018-07-01 13:08:18Z

1

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();

answered Jul 1, 2018 at 13:08

jdweng

34.5k3 gold badges17 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nascent · Accepted Answer · 2018-07-11 11:44:43Z

1

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.

answered Jul 11, 2018 at 11:44

Nascent

488 bronze badges

Collectives™ on Stack Overflow

Converting SQL subquery to LINQ

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related