Linq to Entities - SQL "IN" clause

Question

In T-SQL you could have a query like:

SELECT * FROM Users WHERE User_Rights IN ("Admin", "User", "Limited")

How would you replicate that in a LINQ to Entities query? Is it even possible?

Flessel

Answer 1

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;
}

Semibreve

maybe I was too quick to mark as answer, but I don't get a .Contains after the { "Admin", "User", "Limited" } VS2008 doesn't like that code one bit. – Flessel

true to my name "FailBoy" I figured it out :P I put into a string[] and then used it and it worked. Thanks! – Flessel

sorry, I forgot to new up the anonymous array ;) I fixed my code example. Glad you figured it out on your own though. – Semibreve

This answer would have been correct had the question been about Linq-to-SQL or Linq in general. However, since it specifically says "Linq-to-Entities", this answer is incorrect. array.Contains is not (yet) supported by Linq-to-Entities. – Eglanteen

@Eglanteen -- that may have been true for earlier versions of EF, but it seems fine for me with EF4. – Mule

@BenAlabaster, I have a collection of items that needed to be checked in the "IN" as in var oIds = (List<int>) Session["OrderIds"]; How do i do the query for this items instead of newing it up and checking for the hard-coded values (here, "Admin","Users", "Limited"), – Misusage

What if i am collecting my items to check the query against like this: var oIds = (List<int>) Session["OrderIds"]; I am stuck with this :IEnumerable<Website> model = from A in _db.Websites join B in _db.OrderToWebsites on A.WebsiteId equals B.WebsiteId ... I am using 2 tables – Misusage

Answer 2

This should suffice your purpose. It compares two collections and checks if one collection has the values matching those in the other collection

fea_Features.Where(s => selectedFeatures.Contains(s.feaId))

Kaki

Answer 3

I will go for Inner Join in this context. If I would have used contains, it would iterate 6 times despite if the fact that there are just one match.

var desiredNames = new[] { "Pankaj", "Garg" }; 

var people = new[]  
{  
    new { FirstName="Pankaj", Surname="Garg" },  
    new { FirstName="Marc", Surname="Gravell" },  
    new { FirstName="Jeff", Surname="Atwood" }  
}; 

var records = (from p in people join filtered in desiredNames on p.FirstName equals filtered  select p.FirstName).ToList(); 

Disadvantages of Contains

Suppose I have two list objects.

List 1      List 2
  1           12
  2            7
  3            8
  4           98
  5            9
  6           10
  7            6

Using Contains, it will search for each List 1 item in List 2 that means iteration will happen 49 times !!!

Irreformable

This completely ignores the fact that the statement is translated into SQL. See here. – Arboreal

Answer 4

This could be the possible way in which you can directly use LINQ extension methods to check the in clause

var result = _db.Companies.Where(c => _db.CurrentSessionVariableDetails.Select(s => s.CompanyId).Contains(c.Id)).ToList();

Oliviaolivie

Answer 5

I also tried to work with an SQL-IN-like thing - querying against an Entity Data Model. My approach is a string builder to compose a big OR-expression. That's terribly ugly, but I'm afraid it's the only way to go right now.

Now well, that looks like this:

Queue<Guid> productIds = new Queue<Guid>(Products.Select(p => p.Key));
if(productIds.Count > 0)
{
    StringBuilder sb = new StringBuilder();
    sb.AppendFormat("{0}.ProductId = Guid\'{1}\'", entities.Products.Name, productIds.Dequeue());
    while(productIds.Count > 0)
    {
        sb.AppendFormat(" OR {0}.ProductId = Guid\'{1}\'",
          entities.Products.Name, productIds.Dequeue());
    }
}

Working with GUIDs in this context: As you can see above, there is always the word "GUID" before the GUID ifself in the query string fragments. If you don't add this, ObjectQuery<T>.Where throws the following exception:

The argument types 'Edm.Guid' and 'Edm.String' are incompatible for this operation., near equals expression, line 6, column 14.

Found this in MSDN Forums, might be helpful to have in mind.

Naara

Answer 6

An alternative method to BenAlabaster answer

First of all, you can rewrite the query like this:

var matches = from Users in people
        where Users.User_Rights == "Admin" ||
              Users.User_Rights == "Users" || 
              Users.User_Rights == "Limited"
        select Users;

Certainly this is more 'wordy' and a pain to write but it works all the same.

So if we had some utility method that made it easy to create these kind of LINQ expressions we'd be in business.

with a utility method in place you can write something like this:

var matches = ctx.People.Where(
        BuildOrExpression<People, string>(
           p => p.User_Rights, names
        )
);

This builds an expression that has the same effect as:

var matches = from p in ctx.People
        where names.Contains(p.User_Rights)
        select p;

But which more importantly actually works against .NET 3.5 SP1.

Here is the plumbing function that makes this possible:

public static Expression<Func<TElement, bool>> BuildOrExpression<TElement, TValue>(
        Expression<Func<TElement, TValue>> valueSelector, 
        IEnumerable<TValue> values
    )
{     
    if (null == valueSelector) 
        throw new ArgumentNullException("valueSelector");

    if (null == values)
        throw new ArgumentNullException("values");  

    ParameterExpression p = valueSelector.Parameters.Single();

    if (!values.Any())   
        return e => false;

    var equals = values.Select(value =>
        (Expression)Expression.Equal(
             valueSelector.Body,
             Expression.Constant(
                 value,
                 typeof(TValue)
             )
        )
    );
   var body = equals.Aggregate<Expression>(
            (accumulate, equal) => Expression.Or(accumulate, equal)
    ); 

   return Expression.Lambda<Func<TElement, bool>>(body, p);
}

I'm not going to try to explain this method, other than to say it essentially builds a predicate expression for all the values using the valueSelector (i.e. p => p.User_Rights) and ORs those predicates together to create an expression for the complete predicate

Source: http://blogs.msdn.com/b/alexj/archive/2009/03/26/tip-8-writing-where-in-style-queries-using-linq-to-entities.aspx

Sandpit

Answer 7

Real example:

var trackList = Model.TrackingHistory.GroupBy(x => x.ShipmentStatusId).Select(x => x.Last()).Reverse();
List<int> done_step1 = new List<int>() {2,3,4,5,6,7,8,9,10,11,14,18,21,22,23,24,25,26 };
bool isExists = trackList.Where(x => done_step1.Contains(x.ShipmentStatusId.Value)).FirstOrDefault() != null;

Idealism

Answer 8

This isn't exactly the IN operator, but it might help you get the expected result and maybe a more generic approach (as it allows two collections to be compared) : INTERSECT

here's a working example

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

I guess performance should be benchmarked (against the currently accepted answer) to fully validate this solution...

EDIT :

As Gert Arnold asked for an example (EF 6) : This piece of code gives me any user whose first and/or last name matches "John" or "Doe" :

// GET: webUsers
public async Task<ActionResult> Index()
{
  var searchedNames = new[] { "John", "Doe" };

  return 
    View(
      await db
        .webUsers
        .Where(u => new[] { u.firstName, u.lastName }.Intersect(searchedNames).Any())
        .ToListAsync()
    );

  //return View(await db.webUsers.ToListAsync());
}

Goth

How is this "more generic"? It's a very contrived solution. Absolutely not better than a simple Contains. – Arboreal

Instead of comparing an atomic value to check if its contained inside a collection, the code can now extend both side of the comparison (collections) which allows less refactoring in case you need to extend your use case. I agree this is a bit overkill in the OP's situation, but it works. – Goth

Please prove that statement by posting working code. – Arboreal

I think what I really meant was set operators are more generic way of seeing the OP's problem. (The IN operator feels like a particular use case of the INTERSECT operator IMO...) – Goth

The above code works fine. if you wish, I can send you my working code. – Goth

This may help you understand my point. – Goth

Please try to "extend both side of the comparison" and run it against Entity Framework to under stand my point. – Arboreal

Your example extends one side of the comparison and it doesn't translate in EF core. To me there's only some academic value here. – Arboreal

You could ofc add strings in the searchedNames. – Goth

Answer 9

Query syntax:

string[] month = { "jan", "feb", "mar" };

var qry = from c in populationdata
            where c.birthmonth in month
            select c;

Will select records from "populationdata" where the month is "in" the array of strings "month".

Flit