[.net] Linq to Entities - SQL "IN" clause

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?

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

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

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();

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.

Matthias

... looking forward for the next version of .NET and Entity Framework, when everything get's better. :)

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;

Seriously? You folks have never used

where (t.MyTableId == 1 || t.MyTableId == 2 || t.MyTableId == 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 !!!