How to check record exists in group by subquery in linq to sql

Question

I want to get the result of which records exist in a group by records in Linq. I have got the query in SQL but am struggling to get it in LINQ

In SQL query

 select LEVEL, DESCR
   from ACTIVITY_LKUP 
   WHERE LEVEL IN ( //Checking row exists
    select TOP 2 b.LEVEL
    from ACTIVITY_LKUP b
    WHERE b.LEVEL > (
    select max(b.LEVEL)
    from ACTIVITY a, ACTIVITY_LKUP b
    WHERE b.TYPE = a.ACTIVITY_TYPE
    and a.JOB_CANDIDATE_ID = 1
   )
  group by b.level //Grouping here
  )
order by LEVEL

In Linq query

 var duplicate =(from lkup in ACTIVITY_LKUP
                where lkup.LEVEL== (from actlk in ACTIVITY_LKUP //Iam Not able to check row exists in  the group by
                where actlk.LEVEL >
                (
                 from act in ACTIVITY
                 join lkup in ACTIVITY_LKUP on act.ACTIVITY_TYPE equals lkup.TYPE
                 where act.JOB_CANDIDATE_ID == 1
                 orderby lkup.LEVEL
                 select lkup.LEVEL
                 ).First()            
                group actlk by actlk.LEVEL into lggrp
                select new { LEVEL = lggrp.Key,DESCR=lggrp.Select(x=>x.DESCR)              
              }).Take(2)
             select lkup)

Showing error line "Operator '==' cannot be applied to operands of type 'int?' and 'System.Linq.IQueryable".

How can we do it in LINQ? Help me anyone.

It looks like lkup.LEVEL is an 'int?' or Nullable whereas the value return for comparison is a collection or 'IQueryable' Which makes sense when you are using .'Take(2)' which in this case would return an IQueryable with a length of 2. You could try changing Take(2) to .FirstOrDefault(). — CalebB, Apr 20 '15 at 13:53
Also [here](http://stackoverflow.com/questions/1024559/when-to-use-first-and-when-to-use-firstordefault-with-linq) is a good question that describes the difference between the return of First()/FirstOrDefault() and Take(int). — CalebB, Apr 20 '15 at 13:57

Sanjay · Accepted Answer · 2015-04-21T03:29:14.060

You can also try joining equal columns within the query, like

var duplicate=(from lg in reslandentity.ACTIVITY_LKUP
               join grp in
                  (from actlk in reslandentity.ACTIVITY_LKUP
                    where actlk.LEVEL >
                     (
                       from act in reslandentity.ACTIVITY
                       join lkup in reslandentity.ACTIVITY_LKUP on act.ACTIVITY_TYPE equals lkup.TYPE
                       where act.JOB_CANDIDATE_ID == 1
                       orderby lkup.LEVEL
                       select lkup.LEVEL
                     ).FirstOrDefault()
                      orderby actlk.LEVEL
                      group actlk by actlk.LEVEL into lggrp
                      select new { LEVEL = lggrp.Key }).Take(2) 
                 on lg.LEVEL equals grp.LEVEL
                 select lg).ToList();

Hope it helps you..!!

How to check record exists in group by subquery in linq to sql

1 Answers1