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, Jul 01 '18 at 12:27
`where ((from q in conn.DEPTs where q.DNAME == "RESERCH" select q.DEPTNO).Contains(query.DEPTNO))` — Chetan, Jul 01 '18 at 12:30
still not working here is the new error Error 1 'System.Linq.IQueryable' does not contain a definition for 'Contains' and the best extension method overload 'System.Linq.ParallelEnumerable.Contains(System.Linq.ParallelQuery, TSource)' has some invalid arguments C:\Users\Slahu\documents\visual studio 2013\Projects\WebApplication10\WebApplication10\Controllers\DefaultController.cs 20 64 WebApplication10 — Slahuddin Chaudhary, Jul 01 '18 at 12:41
`from emp in conn.EMPs where conn.DEPTs.Where(d=>d.DNAME == "RESERCH").Select(d=>d.DEPTNO).Contains(emp.deptno) select new ...` — derloopkat, Jul 01 '18 at 12:44
Off topic: don't abbreviations property and class names. On-topic, make sure `Employee` has a navigation property `Department` after which this becomes ridiculously easy. — Gert Arnold, Jul 01 '18 at 12:53
still getting same errors :: 1) 'System.Linq.IQueryable' does not contain a definition for 'Contains' and the best extension method overload 'System.Linq.ParallelEnumerable.Contains(System.Linq.ParallelQuery, TSource)' has some invalid arguments 2) Instance argument: cannot convert from 'System.Linq.IQueryable' to 'System.Linq.ParallelQuery' — Slahuddin Chaudhary, Jul 01 '18 at 12:54
Please show the `EMP` and `DEPT` classes (or Employee and Department after giving them proper names). You're making this way too hard. — Gert Arnold, Jul 01 '18 at 19:44
EMP........................ EMPNO... NOT NULL NUMBER(4)... ENAME... VARCHAR2(10)... JOB ... VARCHAR2(9)... MGR ... NUMBER(4)... COMM ... NUMBER(7,2)... DEPTNO ... NUMBER(2)... — Slahuddin Chaudhary, Jul 02 '18 at 05:51
Please edit your question. And I mean the *classes* not the database tables. — Gert Arnold, Jul 02 '18 at 06:53
You may find my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) helpful. — NetMage, Jul 02 '18 at 22:10

score 1 · Answer 1 · answered Jul 01 '18 at 13:08

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 01 '18 at 13:08

jdweng

33,250
2
15
20

score 1 · Answer 2 · answered Jul 11 '18 at 11:44

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 '18 at 11:44

Nascent

48
8

Converting SQL subquery to LINQ

2 Answers2