What is the linq equivalent to the SQL IN operator
.Contains
var resultset = from x in collection where new[] {2,3,4,5}.Contains(x) select x
Of course, with your simple problem, you could have something like:
var resultset = from x in collection where x >= 2 && x <= 5 select x
Convert a sql query having in operator to linq
The equivalent to a SQL IN
clause in LINQ is a Contains
statement.
IEnumerable<long> validWigetItemIds; // fill from Database or input parameters
var result = DbContext.JobItems
.Where(ji => validWigetItemIds.Contains(ji.WidgetItemId)); // equivalent to IN clause
// other LINQ statements as required by your usecase
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;
}
What is the SQL LIKE operator equivalent in LINQ having multiple % operators in the input text?
Posting the comments given by @Fabio and @CodeNotFound as answer for reference.
In EntityFramework version 6.2.0:
var users = (from usr in Context.Users
where DbFunctions.Like(usr.Username, "%test%email%")
select usr).ToList();
Where IN clause in LINQ
This expression should do what you want to achieve.
dataSource.StateList.Where(s => countryCodes.Contains(s.CountryCode))
IN Operator in Linq
var activeMembers = (
from member in db.ContactSet
where activeProducts.Select(x=>x.ID).Contains(member.ID));
What is the linq equivalent of the below sql query
Assuming SelectedProducts
is an array of product ids (integers):
var cats = db.Categories.Where(o => SelectedProducts.Contains(o.CategoryId));
var pids = cats.Select(o => o.ProductId);
Reason: SQL IN
operator is implemented oppositely in LINQ
to SQL. The question highlights a common mistake in LINQ developers trying to translate from SQL, expecting an [attribute] [operator] [set]
syntax.
Using an abstract set language we can highlight syntax differences
- SQL uses a "Element
is included
in Set" syntax - LINQ uses a "Set
contains
Element" syntax
So any IN
clause must be reverted using the Contains
operator. It will translate to attribute IN (SET)
anyways.
Related Topics
How to Pass Table Name as a Parameter in Oracle
How to Create an Index for Elements of an Array in Postgresql
In VS or of Oracle, Which Faster
What Is the Problem with Foreign Key Cascade Multiple Paths and Cycles
Join a Count Query on Generate_Series() and Retrieve Null Values as '0'
Datareader.Getstring() via Columnname
SQL Set Default Not Working in Ms Access
How to Bulk Insert Only New Rows in Postresql
How to Select Nth Column in a Select Clause from a Table/View
SQL Server Equivalent of Wm_Concat Function
How to Get List of Values in Group_By Clause
SQL Server 2008 Iif Statement Does Not Seem Enabled
Django Prefetch_Related with Limit
How to Use Update Trigger to Update Another Table
Moving Average Based on Timestamps in Postgresql
How to Run SQL Statements on a Named Range Within an Excel Sheet