How would you do a not in query with LINQ?
I don't know if this will help you but..
NorthwindDataContext dc = new NorthwindDataContext();
dc.Log = Console.Out;
var query =
from c in dc.Customers
where !(from o in dc.Orders
select o.CustomerID)
.Contains(c.CustomerID)
select c;
foreach (var c in query) Console.WriteLine( c );
from The NOT IN clause in LINQ to SQL by Marco Russo
NOT IN clause in LINQ to Entities
If you are using an in-memory collection as your filter, it's probably best to use the negation of Contains(). Note that this can fail if the list is too long, in which case you will need to choose another strategy (see below for using a strategy for a fully DB-oriented query).
var exceptionList = new List<string> { "exception1", "exception2" };
var query = myEntities.MyEntity
.Select(e => e.Name)
.Where(e => !exceptionList.Contains(e.Name));
If you're excluding based on another database query using Except
might be a better choice. (Here is a link to the supported Set extensions in LINQ to Entities)
var exceptionList = myEntities.MyOtherEntity
.Select(e => e.Name);
var query = myEntities.MyEntity
.Select(e => e.Name)
.Except(exceptionList);
This assumes a complex entity in which you are excluding certain ones depending some property of another table and want the names of the entities that are not excluded. If you wanted the entire entity, then you'd need to construct the exceptions as instances of the entity class such that they would satisfy the default equality operator (see docs).
Using 'NOT IN' Clause in LINQ Query
You should've done it the other way around. Call Contains()
on the subquery, the one that has multiple items in it :
!(from t2 in db.ASN_ITEM
where t2.SCAN_STAT != 2
select t2.AWB_NO
).Distinct()
.Contains(t.AWB_NO)
Also, you have to select AWB_NO
directly, as shown above, instead of projecting to anonymous type. Doing the latter will prevent usage of Contains()
, since item type in collection will be different from object type passed as parameter of Contains()
.
NOT IN Condition in Linq
Except
expects argument of type IEnumerable<T>
, not T
, so it should be something like
_empIds = _cmn.GetEmployeeCenterWise(_loggedUserId)
.Select(e => e.Id)
.Except(new[] {_loggedUserId})
.ToList();
Also note, this is really redundant in the case when exclusion list contains only one item and can be replaces with something like .Where(x => x != _loggedUserId)
how to use Linq NOT IN
var _result = from a in tblContractor
where !(from b in tbSiteByCont
where FKSiteID == 13
select b.FKConID)
.Contains(a.PKConID)
select a;
or
var siteLst = tbSiteByCont.Where(y => y.FKSiteID == 13)
.Select(x => x.FKConID);
var _result = tblContractor.Where(x => !siteLst.Contains(x.PKConID));
Trouble converting a Query Syntax LINQ query using a 'not in (subquery)' into Method Syntax
All you need to do is to call the .Contains
method on your subquery, just like in the query syntax:
Foo = await _context.foo
.Where(r => r.pid == PId)
.Where(r => !DraftStatusExceptionList.Contains(r.Stat))
.Where(r => r.Csstat != "UNK" || !String.IsNullOrEmpty(r.Csstat))
.Where(r => !_context.foo
.Where(rr => rr.pid == PId)
.Select(rr => rr.fooId)
.Contains(r.fooId))
.OrderBy(r => r.Sdate)
.ThenBy(r => r.Sdate2)
.ThenBy(r => r.recordlocator)
.ToListAsync();
In the last Where
method call, you're pulling out from the database some fooIds
that match the condition into a new collection, and then check your main collection's element against it.
Note the new variable name rr
used in the subquery to distinguish elements in the query and subquery, and the negation of .Contains
method result.
Edit: slight code corrections and adding ordering method calls.
How to make select not in clause using linq sql
TOP
makes no sense without an ORDER BY
.
Assuming you're paging the results sorted by the customer ID, try:
List<Customer> page = context.Customers
.OrderBy(c => c.CustomerID)
.Skip(3).Take(3)
.ToList();
Linq NOT IN query - based on SQL query
The rough equivalent of a (not) in
is using Contains()
. Since the inner subquery doesn't reference the outer, you could write it like this:
var completedIds =
(from r in ctx.References
where r.Status == "COMPLETED"
select r.Id).Distinct();
var count =
(from r in ctx.References
where !completedIds.Contains(r.ID)
where r.Status == "FAILED"
select r.Id).Distinct().Count();
Sql Query with not in clause of subquery to LINQ query
This
from a in user_user_connection
join b in user_user_connection on
new {From=a.userid_to, To=a.userid_from} equals new {From=b.userid_from, To=b.userid_to} into c
from d in c.DefaultIfEmpty()
where d == null
select a;
is similar to
select a.*
from user_user_connection a
left join user_user_connection b on a.userid_to = b.userid_from and a.userid_from = b.userid_to
where b.userid_from is null
which should match your not in query.
if you want a specific userid_from
you can add another where
from a in user_user_connection
where a.userid_from == 3464
join b in user_user_connection on
new {From=a.userid_to, To=a.userid_from} equals new {From=b.userid_from, To=b.userid_to} into c
from d in c.DefaultIfEmpty()
where d == null
select a;
Related Topics
How to Autheticate to Ibm Mq C# with Tls-Certificate
Can You Develop Linux Applications with Xamarin
Why Filesystemwatcher Doesn't Work in Linux Container Watching Windows Volume
.Net Core MAChine Key Alternative for Webfarm
How to Tell a Lambda Function to Capture a Copy Instead of a Reference in C#
JSON.Net Throws Stackoverflowexception When Using [JSONconvert()]
How to Decompile a .Net Exe into Readable C# Source Code
ASP.NET MVC Custom Error Handling Application_Error Global.Asax
How to Create a Navigation Menu in Dotnet Application
How to Use Visual Studio Code to Develop Unity3D Projects in Ubuntu
Embedding JavaScript Engine into .Net
Difference Between Equals/Equals and == Operator
Most Elegant Way to Generate Prime Numbers
Show a Form Without Stealing Focus
Why Can't I Use the 'Await' Operator Within the Body of a Lock Statement