Linq to SQL in and Not In

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.

Linq to Entities - SQL IN clause

You need to turn it on its head in terms of the way you're thinking about it. Instead of doing "in" to find the current item's user rights in a predefined set of applicable user rights, you're asking a predefined set of user rights if it contains the current item's applicable value. This is exactly the same way you would find an item in a regular list in .NET.

There are two ways of doing this using LINQ, one uses query syntax and the other uses method syntax. Essentially, they are the same and could be used interchangeably depending on your preference:

Query Syntax:

var selected = from u in users
               where new[] { "Admin", "User", "Limited" }.Contains(u.User_Rights)
               select u

foreach(user u in selected)
{
    //Do your stuff on each selected user;
}

Method Syntax:

var selected = users.Where(u => new[] { "Admin", "User", "Limited" }.Contains(u.User_Rights));

foreach(user u in selected)
{
    //Do stuff on each selected user;
}

My personal preference in this instance might be method syntax because instead of assigning the variable, I could do the foreach over an anonymous call like this:

foreach(User u in users.Where(u => new [] { "Admin", "User", "Limited" }.Contains(u.User_Rights)))
{
    //Do stuff on each selected user;
}

Syntactically this looks more complex, and you have to understand the concept of lambda expressions or delegates to really figure out what's going on, but as you can see, this condenses the code a fair amount.

It all comes down to your coding style and preference - all three of my examples do the same thing slightly differently.

An alternative way doesn't even use LINQ, you can use the same method syntax replacing "where" with "FindAll" and get the same result, which will also work in .NET 2.0:

foreach(User u in users.FindAll(u => new [] { "Admin", "User", "Limited" }.Contains(u.User_Rights)))
{
    //Do stuff on each selected user;
}

LINQ equivalent of SQL NOT IN statement

You can use Contains with !. In addition, if you just want to count rows, you can use Count.

var ids = new List<int> {25, 346, 350, 352, 353, 354, 355, 357, 358, 366, 372, 411};

var querynonSystem = XXXcontext.Rpm_scrty_rpm_usrs.Count(x => 
   x.Inact_ind == "N" &&
   x.Email_id != null &&
   x.Wwid == null &&
   x.Email_id.Trim() != "" &&
   !ids.Contains(x.Dflt_ste_id));

From comment: if you want to retrieve all, you can still use Where and Select.

var querynonSystem = XXXcontext.Rpm_scrty_rpm_usrs.Where(x => 
   x.Inact_ind == "N" &&
   x.Email_id != null &&
   x.Wwid == null &&
   x.Email_id.Trim() != "" &&
   !ids.Contains(x.Dflt_ste_id)).Select(x => x).ToList();

FYI: you cannot call Rpm_scrty_rpm_usrs table class to query. Instead, you need DbContext or some other repository.

Linq to SQL: WHERE IN statement

You can use names.Contains():

string[] names = {"John", "Cassandra", "Sarah"};

var results = (from n in db.Names
               where names.Contains(n.Name)
               select n).ToList();

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

Linq to Sql with the And operator on the same field

Start the query on the order relation instead:

var result = Orders.Where(o => o.OrderDetails.Any(od => od.ProductId == 11) 
                            && o.OrderDetails.Any(od => od.ProductId == 42));

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;

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).

---

Related Topics