CODEWITHSUNDEEP
sqllinq
Anup
Anup

Reputation: 9738

Linq - How to Use NOT IN

I am having following query & i need to write it in linq. I am stuck in NOT IN part.

SELECT A.CODE,
       A.DATETIME,
       A.DATE 
FROM TABLE_IO A 
WHERE  A.DATE>= '01/06/2015' AND A.DATE<='01/06/2015'        
AND A.CODE NOT IN(
                   SELECT CODE 
                   FROM TABLE_ENTRY B 
                   WHERE A.CODE=B.CODE AND A.DATE=B.ENTRY_DATE AND METHOD='M'
                  )

How to write NOT IN part?

var data = ctx.TABLE_IO.Where(m=>m.Date >= '01/06/2015' && m.Date <= '01/06/2015')
                       .Select(m=>m).ToList();

Upvotes: 0

Views: 63

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460048

You can use !Any:

DateTime dateToCompare = new DateTime(2015, 6, 1);
var data = ctx.TABLE_IO
    .Where(m => m.Date >= dateToCompare && m.Date <= dateToCompare)
    .Where(m => !ctx.TABLE_ENTRY
        .Any(te => m.Code == te.Code && m.Date == te.ENTRY_DATE && te.METHOD == "M"))
    .ToList();

I would prefer this, i'm fairly sure that it will be translated to a performant NOT EXISTS which has also no issues with null values like NOT IN/Contains.

The direct translation of NOT IN/Contains would be this:

var data = ctx.TABLE_IO
    .Where(m => m.Date >= dateToCompare && m.Date <= dateToCompare)
    .Where(m => !ctx.TABLE_ENTRY.Select(te => te.Code).Contains(m.Code))
    .ToList();

Upvotes: 3

gd73
gd73

Reputation: 635

Basically you can do something such as (pseudo queries below)

var exclusions = table_B.Where(b => [exclusion condition] ).Select(b => b.Id)
var data = ctx.TABLE_IO.Where(m => !exclusions.Contains(m.Id))

Upvotes: 0

Related Questions