LINQ to SQL query using NOT IN
List<string> states = new List<string> { "CA", "IN", "MD" };
var q = from a in authors
where !states.Contains(a.state)
select new { a.au_lname, a.state };
or
var q = authors.Where( a => !states.Contains( a.state ) )
.Select( a => new { a.au_lname, a.state } );
LINQ to SQL in and not in
You use, where <list>.Contains( <item> )
var myProducts = from p in db.Products
where productList.Contains(p.ProductID)
select p;
Or you can have a list predefined as such:
int[] ids = {1, 2, 3};
var query = from item in context.items
where ids.Contains( item.id )
select item;
For the 'NOT' case, just add the '!' operator before the 'Contains' statement.
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;
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()
.
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 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 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();
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).
SQL Query to LINQ syntax using not exist and join
You can try like this:
(from lk in localizationKey
where (from l in lang
join lv in localizationValue on l.Id equals lv.LanguageId
where (l.Title == "en-US" && lv.LocalizationKeyId == lk.Id)
select l).FirstOrDefault() == null
select lk).ToList();
or
(from lk in localizationKey
where !(from l in lang
join lv in localizationValue on l.Id equals lv.LanguageId
where !(l.Title == "en-US" && lv.LocalizationKeyId == lk.Id)
select l).FirstOrDefault().Any()
select lk).ToList();
Related Topics
Partition by with and Without Keep in Oracle
How to Generate Ranks in MySQL
Concurrency Handling of SQL Transactrion
Removing Duplicate Rows (Based on Values from Multiple Columns) from SQL Table
Script All Data from SQL Server Database
SQL Server Unique Composite Key of Two Field with Second Field Auto-Increment
How to Improve Performance for Datetime Filtering in SQL Server
How to Pass a Temp Table as a Parameter into a Separate Stored Procedure
"Order By" Using a Parameter for the Column Name
Oracle: Updating a Table Column Using Rownum in Conjunction with Order by Clause
Base 36 to Base 10 Conversion Using SQL Only
Prepared Statement on Postgresql in Rails
SQL Server for Xml Path Make Repeating Nodes
Rolling 90 Days Active Users in Bigquery, Improving Preformance (Dau/Mau/Wau)
How to Implement Soft Deletion
SQL Filter Criteria in Join Criteria or Where Clause Which Is More Efficient
Error Trying to Call Stored Procedure with Prepared Statement