CODEWITHSUNDEEP
c#linq
Piotrek17
Piotrek17

Reputation: 43

How to write subquery with the any quantifier using linq?

I have a problem with using any in linq I do not know how to do it correctly. I have to write this in linq: 

SELECT ename, job, deptno
FROM emp
WHERE sal > ANY
(
    SELECT DISTINCT sal
    FROM emp
    WHERE deptno = 30
);

I write only this:

var min = (from emp in Emps
           where emp.Deptno == 30
           select emp.Sal
          ).Distinct();

var result = (from emp in Emps
              where min.Any() > emp.Sal
              select new
              {
                  emp.Ename
              });

Upvotes: 0

Views: 73

Answers (1)

gunr2171
gunr2171

Reputation: 17520

Linq doesn't have an any/some operator in the way Sql Server does.

var salariesInTargetDepartment = Emps
    .Where(x => x.Deptno == 30)
    .Select(x => x.Sal)
    .Distinct()
    .ToList(); // the ToList is not required, but seeing you're going to be executing
               // against this query many times, it will be better to cache the results.

var matchingEmployees = Emps
    .Where(emp => salariesInTargetDepartment
        .Any(target => emp.Sal > target)
    );

The where clause in the second statement says "Only include this record if this record's Sal property is greater than at least one entry in the salariesInTargetDepartment collection."

Upvotes: 1

Related Questions