CODEWITHSUNDEEP
c#linq
johhny.b
johhny.b

Reputation: 33

The WHERE IN clause with two parameters in linq

When I have WHERE IN with one parameter, I use Contains but when I have two parameters it does not work. I need to write a subquery in SQL in linq:

SELECT ename, sal, deptno
FROM emp
WHERE (sal, deptno) IN
(SELECT MIN(sal), deptno
FROM emp
GROUP BY deptno);


var min = (from emp in Emps
                       group emp by new { Dzial = emp.Deptno} into grouped
                       select new
                       {
                           grouped.Key.Dzial,
                           wynik = grouped.Min(x => x.Sal)
                       });

            var result = (from emp in Emps
                          where min.Contains(emp.Deptno, emp.Sal)
                          select new
                          {
                              emp.Ename,
                              emp.Sal,
                              emp.Deptno
                          });

I tried to divide Where into two separate ones, but that doesn't change anything.

Upvotes: 1

Views: 174

Answers (1)

PWNTech
PWNTech

Reputation: 36

You should simply GroupBy and then use SelectMany to apply your "IN" condition inside Where like this:

var result = Emps.GroupBy(g => g.Deptno).SelectMany(a => a.Where(b => b.Sal == a.Min(c => c.Sal)));

I've also prepared a little example on DotNetFiddle: https://dotnetfiddle.net/p8aYJv

Upvotes: 2

Related Questions