locked
Linq with "not in" and "Count" query RRS feed

  • Question

  • User1804579801 posted

    I want a query like 

    select count(*) from Table1 where Id not in (Select Id from Table2)


    select count(*) from Table1  where Id in (Select Id from Table2)

    These in Linq .

    I have checked with Contains but i t  gives me runtime error 

    var ss = (from reg in db.Table1  where !db.Table2.Contains(reg) select reg).Count();

    Please suggest

    Tuesday, September 11, 2018 7:30 AM

All replies

  • User-746821919 posted

    This may help you.

                            var table2IdList = (from table2 in context.Table2
                                               select id).ToList();
    
                            var count = (from table1 in context.Table1
                                         where !table2IdList.Contains(table1.Id)
                                         select table1).Count();
    

    Tuesday, September 11, 2018 11:13 AM
  • User-471420332 posted

            

    var _result = from a in db.Table1
    where a.Status == "Active" && !(from b in db.Table2
    select b.RegId)
    .Contains(a.RegId)
    select a;

    var Finalcounts = _result.Count();

    Tuesday, September 11, 2018 11:41 AM
  • User1520731567 posted

    Hi nagapavanich,

    I want a query like 

    select count(*) from Table1 where Id not in (Select Id from Table2)


    select count(*) from Table1  where Id in (Select Id from Table2)

    These in Linq .

    I have checked with Contains but i t  gives me runtime error 

    var ss = (from reg in db.Table1  where !db.Table2.Contains(reg) select reg).Count();

    According to you requirement,you could refer to:

    var exceptionList = new List<int> { 1, 2,3,4 };
    var query = db.Table1.Select(e => e.Id)
                             .Where(e => !exceptionList.Contains(e)).ToList().Count();

    More details,you could refer to:

    https://forums.asp.net/p/2146160/6227266.aspx?Re+LINQ+using+NOT+IN

    Best Regards.

    Yuki Tao

    Wednesday, September 12, 2018 5:42 AM
  • User-271186128 posted

    Hi Sir, 

    var ss = (from reg in db.Table1  where !db.Table2.Contains(reg) select reg).Count();

    please modify your code as below:

    var ss = (from reg in db.Table1 where !db.Table2.Select(c=>c.Id).Distinct().ToList().Contains(reg.Id) select reg).Count();

    The Distinct() method is used to remove the duplicate values.

    Wednesday, September 19, 2018 6:51 AM