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

User · Answer

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

User · Answer

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   gt  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   gt  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   gt  new       Admin    User    Limited    Contains u User Rights            Do stuff on each selected user

User · Answer

Seriously   You folks have never used  where  t MyTableId    1    t MyTableId    2    t MyTableId    3

User · Answer

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 lt People  string gt              p   gt  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 lt Func lt TElement  bool gt  gt  BuildOrExpression lt TElement  TValue gt           Expression lt Func lt TElement  TValue gt  gt  valueSelector           IEnumerable lt TValue gt  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   gt  false       var equals   values Select value   gt           Expression Expression Equal               valueSelector Body               Expression Constant                   value                   typeof TValue                                     var body   equals Aggregate lt Expression gt                accumulate  equal    gt  Expression Or accumulate  equal              return Expression Lambda lt Func lt TElement  bool gt  gt  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

User · Answer

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   gt  selectedFeatures Contains s feaId

User · Answer

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

User · Answer

Real example   var trackList   Model TrackingHistory GroupBy x   gt  x ShipmentStatusId  Select x   gt  x Last    Reverse    List lt int gt  done step1   new List lt int gt     2 3 4 5 6 7 8 9 10 11 14 18 21 22 23 24 25 26    bool isExists   trackList Where x   gt  done step1 Contains x ShipmentStatusId Value   FirstOrDefault      null

User · Answer

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   gt   db CurrentSessionVariableDetails Select s   gt  s CompanyId  Contains c Id   ToList

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

Examples related to .net

Examples related to linq

Examples related to linq-to-entities

Examples related to in-clause