Linq with "not in" and "Count" query

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

Answer 1

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();

Answer 2

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();

Answer 3

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

Answer 4

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.