Linq to Entities - Sql "IN" clause

Question

Hey guys,

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? Thanks!

Answer 1

If you're using VS2008/.net 3.5, see Alex James' tip #8: http://blogs.msdn.com/alexj/archive/2009/03/26/tip-8-writing-where-in-style-queries-using-linq-to-entities.aspx

Otherwise just use the array.Contains(someEntity.Member) method.

Answer 2

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