Is there anyway I can create a not in clause like I would have in SQL Server in Linq to Entities?
This question is related to
c#
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).
I have the following extension methods:
public static bool IsIn<T>(this T keyObject, params T[] collection)
{
return collection.Contains(keyObject);
}
public static bool IsIn<T>(this T keyObject, IEnumerable<T> collection)
{
return collection.Contains(keyObject);
}
public static bool IsNotIn<T>(this T keyObject, params T[] collection)
{
return keyObject.IsIn(collection) == false;
}
public static bool IsNotIn<T>(this T keyObject, IEnumerable<T> collection)
{
return keyObject.IsIn(collection) == false;
}
Usage:
var inclusionList = new List<string> { "inclusion1", "inclusion2" };
var query = myEntities.MyEntity
.Select(e => e.Name)
.Where(e => e.IsIn(inclusionList));
var exceptionList = new List<string> { "exception1", "exception2" };
var query = myEntities.MyEntity
.Select(e => e.Name)
.Where(e => e.IsNotIn(exceptionList));
Very useful as well when passing values directly:
var query = myEntities.MyEntity
.Select(e => e.Name)
.Where(e => e.IsIn("inclusion1", "inclusion2"));
var query = myEntities.MyEntity
.Select(e => e.Name)
.Where(e => e.IsNotIn("exception1", "exception2"));
I created it in a more similar way to the SQL, I think it is easier to understand
var list = (from a in listA.AsEnumerable()
join b in listB.AsEnumerable() on a.id equals b.id into ab
from c in ab.DefaultIfEmpty()
where c != null
select new { id = c.id, name = c.nome }).ToList();
I took a list and used,
!MyList.Contains(table.columb.tostring())
Note: Make sure to use List and not Ilist
I took a list and used,
!MyList.Contains(table.columb.tostring())
Note: Make sure to use List and not Ilist
Try:
from p in db.Products
where !theBadCategories.Contains(p.Category)
select p;
What's the SQL query you want to translate into a Linq query?
I created it in a more similar way to the SQL, I think it is easier to understand
var list = (from a in listA.AsEnumerable()
join b in listB.AsEnumerable() on a.id equals b.id into ab
from c in ab.DefaultIfEmpty()
where c != null
select new { id = c.id, name = c.nome }).ToList();
Try:
from p in db.Products
where !theBadCategories.Contains(p.Category)
select p;
What's the SQL query you want to translate into a Linq query?
I have the following extension methods:
public static bool IsIn<T>(this T keyObject, params T[] collection)
{
return collection.Contains(keyObject);
}
public static bool IsIn<T>(this T keyObject, IEnumerable<T> collection)
{
return collection.Contains(keyObject);
}
public static bool IsNotIn<T>(this T keyObject, params T[] collection)
{
return keyObject.IsIn(collection) == false;
}
public static bool IsNotIn<T>(this T keyObject, IEnumerable<T> collection)
{
return keyObject.IsIn(collection) == false;
}
Usage:
var inclusionList = new List<string> { "inclusion1", "inclusion2" };
var query = myEntities.MyEntity
.Select(e => e.Name)
.Where(e => e.IsIn(inclusionList));
var exceptionList = new List<string> { "exception1", "exception2" };
var query = myEntities.MyEntity
.Select(e => e.Name)
.Where(e => e.IsNotIn(exceptionList));
Very useful as well when passing values directly:
var query = myEntities.MyEntity
.Select(e => e.Name)
.Where(e => e.IsIn("inclusion1", "inclusion2"));
var query = myEntities.MyEntity
.Select(e => e.Name)
.Where(e => e.IsNotIn("exception1", "exception2"));
Try:
from p in db.Products
where !theBadCategories.Contains(p.Category)
select p;
What's the SQL query you want to translate into a Linq query?
Source: Stackoverflow.com