none
WHERE IN clause?

    Question

  • Hi, we just started experimenting with LINQ to Entities and we're looking to be able to accomplish the same functionality as a WHERE IN statement in SQL because we have a single field that we want to compare to multiple values. For example:

     

    Code Block

    SELECT *

    FROM Foo

    WHERE blah IN (1, 3, 5, 7)

     

     

    Currently the list of values we wanted to compare to is in a List<T>. We tried the following LINQ query, but get an error:

     

    Code Block

    List<int> statusesToFind = ...;

     

    var foos = from foo in myEntities.Foos

               join status in (from statusToFind in statusesToFind

                               select statusToFind) on foo.Status equals status

               select foo;

     

     

    The error we received was:

    "Unable to create a constant value of type 'System.Collections.Generic.IEnumerable`1'. Only primitive types (for instance Int32, String and Guid) are supported in this context."

     

    We also tried this query:

    Code Block

    List<int> statusesToFind = ...;

     

    var foos = from foo in myEntities.Foos

               where statusesToFind.Contains(foo.Status)

               select foo;

     

     

    This version seems more along the lines of what we're trying to say literally, but it complains about not being able to translate the Contains method to something usable in the query, which I suppose makes sense.

     

    Anyway, does anyone have any hints on how to accomplish the equivalent of a WHERE IN?

     

    TIA,

    Amit

    Friday, January 11, 2008 11:39 PM

Answers

  • The Entity Framework does not currently support collection-valued parameters ('statusesToFind' in your example). To work around this restriction, you can manually construct an expression given a sequence of values using the following utility method:

    static Expression<Func<TElement, bool>> BuildContainsExpression<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();

        // p => valueSelector(p) == values[0] || valueSelector(p) == ...

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

    }

    Using this utility method, you can rewrite:

    var query1 = from e in context.Entities

                 where ids.Contains(e.ID)

                 select e;

    as

    var query2 = context.Entities.Where(

        BuildContainsExpression<Entity, int>(e => e.ID, ids));

     Thanks,

    -Colin

     

     

     

    Monday, January 14, 2008 1:12 PM
  • Hello All,

    I still see activity in this thread from time to time, so I thought it was worth mentioning that in EF4 we added support for the Contains method and at least in this specific case for collection-valued parameters. Therefore this kind of code now works right out of the box and it is not necesary to use any additinal expression building method:

    var statusesToFind = new List<int> {1, 2, 3, 4};
    
    var foos = from foo in myEntities.Foos
          where statusesToFind.Contains(foo.Status)
          select foo;
    
    

    Hope this helps,
    Diego


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, November 26, 2010 1:59 PM
  •  

    I came up against this recently and before I found this post I came up with an alternative solution using Entity SQL that might be interesting to some readers. I doubt there's any difference in performance, but for my small brain this is easier to understand... Smile

     

    int[] interestingOrderIds = new int[] { 10780, 10781, 10782 };

    string csvIds = string.Join(",", interestingOrderIds.Cast<string>().ToArray());

     

    string query = "SELECT VALUE O FROM Orders AS O WHERE O.OrderID IN {" + csvIds + "}";

     

    ObjectQuery<Orders> orders = new ObjectQuery<Orders>(query, context);

     

    The orders is then composable in the usual manner: e.g. you could go on to do:

     

    orders = (ObjectQuery<Orders>)orders.Where(o => o.Customers.CustomerID == "ALFKI");

     

    etc.

     

    James.

    Friday, March 21, 2008 10:23 PM
  • Diego,

    To answer my own question and for anyone else...

    The problem was not the "Contains" method but with the Telerik MVC JavaScript Serializer (which is part of the grid's built-in ajax API) which parsed the list in a way that the default model binder could not interpret.  Therefore, the list was always null, and blew up the query with the above error.

    So, I created a custom model binder that always instantiates a generic list property (whether it is empty or not). Then I finished the Link-to-Entities statement as follows (which works nicely):

    someMethod(criteria c)

    {

    ...

    context.Records.Where(item => (c.list.All(n => (item.Formats.Select(y => y.ID)).Contains(n)) || c.list.Count == 0))

    ...

    }

     

    Hope this helps someone!

    Tuesday, March 01, 2011 10:52 PM

All replies

  • The Entity Framework does not currently support collection-valued parameters ('statusesToFind' in your example). To work around this restriction, you can manually construct an expression given a sequence of values using the following utility method:

    static Expression<Func<TElement, bool>> BuildContainsExpression<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();

        // p => valueSelector(p) == values[0] || valueSelector(p) == ...

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

    }

    Using this utility method, you can rewrite:

    var query1 = from e in context.Entities

                 where ids.Contains(e.ID)

                 select e;

    as

    var query2 = context.Entities.Where(

        BuildContainsExpression<Entity, int>(e => e.ID, ids));

     Thanks,

    -Colin

     

     

     

    Monday, January 14, 2008 1:12 PM
  •  

    That definitely seems like the direction I need to go in, but there's a few concepts there I haven't dealt with before (Expressions, namely).  Can the BuildContainsExpression method be used with anonymous types?
    Monday, January 14, 2008 4:45 PM
  • The short answer is yes. Technically speaking that method will work with anonymous types.

     

    The long answer is no:

     

    To use the BuildContainsExpression with the EntityFramework you will want <T> to be an entity.

     

    The thing to bear in mind is that what it is doing is this:

     

    Converting BuildContainsExpression( e => e.ID, { 1, 2, 3, 4} ) to something like

     

    where e.ID == 1 || e.ID == 2 || e.ID == 3 || e.ID ==4

     

    where the e in question is the thing you are looking for.

     

    Make sense?

     

     

     

    Friday, January 25, 2008 1:26 AM
  •  

    I came up against this recently and before I found this post I came up with an alternative solution using Entity SQL that might be interesting to some readers. I doubt there's any difference in performance, but for my small brain this is easier to understand... Smile

     

    int[] interestingOrderIds = new int[] { 10780, 10781, 10782 };

    string csvIds = string.Join(",", interestingOrderIds.Cast<string>().ToArray());

     

    string query = "SELECT VALUE O FROM Orders AS O WHERE O.OrderID IN {" + csvIds + "}";

     

    ObjectQuery<Orders> orders = new ObjectQuery<Orders>(query, context);

     

    The orders is then composable in the usual manner: e.g. you could go on to do:

     

    orders = (ObjectQuery<Orders>)orders.Where(o => o.Customers.CustomerID == "ALFKI");

     

    etc.

     

    James.

    Friday, March 21, 2008 10:23 PM
  • Would it be possible to get this code method in VB.net.


    Tuesday, May 20, 2008 2:35 AM
  • Here is a solution that uses Entity SQL syntax. This is probably the best solution until Entities support for "Contains" is added. Assuming your context is called context and you have an entity set called Foo with a property called Id:

    Code Snippet

    IQueryable<Foo> foos = context.Foo.Where("it.Id in {1, 2, 3, ...}");



    Hope this helps someone!
    • Proposed as answer by 刘冰 Monday, June 21, 2010 3:10 AM
    Tuesday, May 20, 2008 5:21 PM
  • To all the VB programmers out there here is the vb version.
    Credit goes to my co-worker Chris Baran @ Rockfish Interactive

     Public Shared Function BuildContainsExpression(Of TElement, TValue)( _
             ByVal valueSelector As Expression(Of Func(Of TElement, TValue)), _
             ByVal values As IEnumerable(Of TValue) _
                ) As Expression(Of Func(Of TElement, Boolean))

                ' validate arguments
                If IsNothing(valueSelector) Then Throw New ArgumentNullException("valueSelector")
                If IsNothing(values) Then Throw New ArgumentNullException("values")

                Dim p As ParameterExpression = valueSelector.Parameters.Single()
                If Not values.Any Then
                    Return _
                        Function(e) False
                End If

                Dim equals = values.Select( _
                    Function(v) _
                        Expression.Equal(valueSelector.Body, Expression.Constant(v, GetType(TValue))) _
                )

                Dim body = equals.Aggregate( _
                    Function(accumulate, equal) _
                        Expression.Or(accumulate, equal) _
                )

                Return Expression.Lambda(Of Func(Of TElement, Boolean))(body, p)
            End Function
    Wednesday, July 30, 2008 6:47 PM
  • Anyone notice that the same logic that do not work on a winform like:

    List<int> statusesToFind = ...;

     

    var foos = from foo in myEntities.Foos

               where statusesToFind.Contains(foo.Status)

               select foo;


    Works beautifully on LINQPad (http://www.linqpad.net/)?

    I don't know what it does differently, but it works just fine :/


    Wednesday, August 20, 2008 6:06 PM
  • LINQPad uses LINQ to SQL, which supports this style of query.

    Joe
    Thursday, August 21, 2008 12:14 AM
  • So, Colin.. I don't know if you could help me?! I am new to Linq, but I am trying to do the same type of compate, but the twist is that I am trying to compare/intersect. Instead of just comparing a list of ints to each entity, I want to compare a list of ints to a property on each entity that also has a list of ints (if there are ANY matches, I want to return 'true'). I have been hacking around, but can't seem to accomplish this. This is such a simple SQL query, but I can't seem to do this with the Linq to Entity framework. Any help is greatly appriciated Smile

    I have an open question at stackoverflow.

    Tuesday, September 23, 2008 8:57 PM
  • Hi,

    I followed up on stackoverflow (which has some additional context). Basically, you can apply the same technique to an Any operator.

    Thanks,

    -Colin

    Wednesday, September 24, 2008 12:29 PM
  • hi,

    I am new to LINQ, I hope you don't mind that I want to ask you a question.

    I need to use IN or Contains clause in my query below. After I read your post, I still couldn't figure out how to convert what I want to do to your solution. Can you help?

    string aCategories = "1,3,5";

    List<int> list = new List<int>();
    string[] cs = aCategories.Split(',');
    foreach(string c in cs)
        list.Add(Convert.ToInt32(c));

    var blist = from books in Search.BookSet
                    from p in books.BookCategoryRelation
                    where (books.ReadingLevel >= aMinReadingLevel
                         && books.ReadingLevel <= aMaxReadingLevel
                         && list.Contains(p.tblCategories.CategoryID))      //p.tblCategories.CategoryID in list
                   select books;
    Monday, October 13, 2008 5:33 PM
  • Hi Colin,

    Thanks for the example code... I'm trying to use it in a bit of a different way... Instead of on the ObjectQuery<Entity> right off of the context, I want to use it to filter an EntityCollection<Entity> within an IQueryable<Entity>, like this:

    ...
    Code Snippet

    userData = userData.Where( ud => ud.Memberships.Where(BuildContainsExpression<Membership, int>( m => m.MembershipType.id, memberTypeIDList)).Count() > 0);


    ...

    The error I get back on the IQueryable<Membership> list is:

    Error    1    The type arguments for method 'System.Linq.Enumerable.Where<TSource>(System.Collections.Generic.IEnumerable<TSource>, System.Func<TSource,bool>)' cannot be inferred from the usage. Try specifying the type arguments explicitly. 

    I'm very new to LINQ, and the Entity Framework, so perhaps I'm using the wrong type of function for this... I'm trying to dynamically reduce the list of core objects based on navigation property values, so that I can chain the output of this call through other similar functions to reduce the list further.

    Thank you!

    Warren
    Wednesday, October 22, 2008 7:59 PM
  • Hi Warren,

     

    "ud.Memberships" is an IEnumerable, not an IQueryable. As a result, the Where method takes a Func<Membership, bool> rather than an Expression<Func<Membership, bool>> as returned by the BuildContainsExpression method.

     

    There's another wrinkle... Because the call to BuildContainsExpression is within the body of the lambda expression, it will show up as an unsupported method call in the resulting query tree. You can avoid this by turning the query inside out (starting with the memberships):


     

    Code Snippet

    userData =

        (from m in objectContext.Memberships.Where(BuildContainsExpression...)

         join u in userData on m.UserData equals u

         select u).Distinct();

     

     

    In general, the BuildContainsExpression was meant as an illustration of how to construct expressions by hand. It will not be appropriate for all scenarios.

     

    Thanks,

    -Colin

     

    Wednesday, October 22, 2008 8:45 PM
  •  Colin Meek - MSFT wrote:


    var query2 = context.Entities.Where(

        BuildContainsExpression<Entity, int>(e => e.ID, ids));

     


    I can't figure out how this works. I mean, I can't get it to compile because I don't know what to put in that "Entity" parameters. What's that "Entity" in your example? A variable? A class? A type?

    Can you post a concrete example on how this works?

    Thanks
    Monday, November 17, 2008 12:44 PM
  • In the usage example, Entity is the type of "e" and is an entity type. If you were using a model with a Categories entity set, the query could look like this:

    var query2 = context.Categories.Where(  
        BuildContainsExpression<Categoryint>(e => e.ID, ids));  
     

    Hope this helps,
    Diego
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Saturday, December 20, 2008 12:30 PM
  • Here an extension class allowing you to do queryable.WhereIn(...)

    public static class QueryableExtensions 
        private static Expression<Func<TElement, bool>> GetWhereInExpression<TElement, TValue>(Expression<Func<TElement, TValue>> propertySelector, IEnumerable<TValue> values) 
        { 
            ParameterExpression p = propertySelector.Parameters.Single(); 
            if (!values.Any()) 
                return e => false
     
            var equals = values.Select(value => (Expression)Expression.Equal(propertySelector.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); 
        } 
     
        /// <summary> 
        /// Return the element that the specified property's value is contained in the specifiec values 
        /// </summary> 
        /// <typeparam name="TElement">The type of the element.</typeparam> 
        /// <typeparam name="TValue">The type of the values.</typeparam> 
        /// <param name="source">The source.</param> 
        /// <param name="propertySelector">The property to be tested.</param> 
        /// <param name="values">The accepted values of the property.</param> 
        /// <returns>The accepted elements.</returns> 
        public static IQueryable<TElement> WhereIn<TElement, TValue>(this IQueryable<TElement> source, Expression<Func<TElement, TValue>> propertySelector, params TValue[] values) 
        { 
            return source.Where(GetWhereInExpression(propertySelector, values)); 
        } 
     
        /// <summary> 
        /// Return the element that the specified property's value is contained in the specifiec values 
        /// </summary> 
        /// <typeparam name="TElement">The type of the element.</typeparam> 
        /// <typeparam name="TValue">The type of the values.</typeparam> 
        /// <param name="source">The source.</param> 
        /// <param name="propertySelector">The property to be tested.</param> 
        /// <param name="values">The accepted values of the property.</param> 
        /// <returns>The accepted elements.</returns> 
        public static IQueryable<TElement> WhereIn<TElement, TValue>(this IQueryable<TElement> source, Expression<Func<TElement, TValue>> propertySelector, IEnumerable<TValue> values) 
        { 
            return source.Where(GetWhereInExpression(propertySelector, values)); 
        } 

    Example:

    var values = new string [] {"Veronica""Heather"}; 
    var query1 = myContext.Contacts.WhereIn(e => e.FirstName, values); 
    var query2 = myContext.Contacts.WhereIn(e => e.FirstName, "Peter""John"); 



    Friday, January 16, 2009 11:25 PM
  • is there away of converting this so i can do WhereNotIn?

    Model.Contact.WhereIn(e => e.ID, list)

    i'm also trying to passing in a collection of Contact records instead of strings, can you give any tips on what i'd need to do to get this working?

     thanks,

     JC

    Wednesday, February 04, 2009 11:51 PM
  • Can someone display an example call in VB? I'm racking myself up trying to get this to work.
    Thanx.
    Thursday, March 05, 2009 8:40 PM
  • How does one convert:

    var query2 = context.Entities.Where(

        BuildContainsExpression<Entity, int>(e => e.ID, ids));


    to VB.NET??

     


    I.W Coetzer
    Friday, March 06, 2009 1:31 PM
  • This is essentially, what I'm trying to get to work ...

    Hi,

    This works,

    Dim result = (From certificate In context.SupplierCertificate _  
                  Where certificate.IsDeleted = 0 _  
                  And certificate.SupplierMaster.SourceId.Contains("AAA") _  
                  Select certificate).ToList() 

    This does not work,

    Dim result = (From certificate In context.SupplierCertificate _  
                  Where certificate.IsDeleted = 0 _  
                  And certificate.SupplierMaster.SourceId.Contains("AAA,BBB") _  
                  Select certificate).ToList() 

    why? How do I get a comma separated list to work, like an IN statement in normal SQL?

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/59346439-498d-4bdd-99b7-05bbaf473bb9
    I.W Coetzer
    Friday, March 06, 2009 1:39 PM
  • I.W Coetzer said:

    How does one convert:

    var query2 = context.Entities.Where(

        BuildContainsExpression<Entity, int>(e => e.ID, ids));


    to VB.NET??

     


    I.W Coetzer



    That's what I'm asking.
    Thanx.
    Friday, March 06, 2009 3:05 PM
  • Donno, I think it should be something like:

    Dim query2 = context.Entities.Where( _  
         BuildContainsExpression(Of Entity, Integer)(Function(e) e.ID, ids)    

    Shimmy
    Sunday, March 22, 2009 11:42 PM
  • Colin Meek said:

    The Entity Framework does not currently support collection-valued parameters ('statusesToFind' in your example). To work around this restriction, you can manually construct an expression given a sequence of values using the following utility method:

    static Expression<Func<TElement, bool>> BuildContainsExpression<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();

        // p => valueSelector(p) == values[0] || valueSelector(p) == ...

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

    }

    Using this utility method, you can rewrite:

    var query1 = from e in context.Entities

                 where ids.Contains(e.ID)

                 select e;

    as

    var query2 = context.Entities.Where(

        BuildContainsExpression<Entity, int>(e => e.ID, ids));

     Thanks,

    -Colin

     

     

     




    Can someone please translate it to VB??
    I tried:

        Public Function BuildContainsExpression(Of TElement, TValue)(ByVal valueSelector As Expression(Of Func(Of TElement, TValue)), ByVal values As IEnumerable(Of TValue)) As Expression(Of Func(Of TElement, Boolean))  
            If valueSelector Is Nothing Then 
                Throw New ArgumentNullException("valueSelector")  
            End If 
            If values Is Nothing Then 
                Throw New ArgumentNullException("values")  
            End If 
            Dim p As ParameterExpression = valueSelector.Parameters.Single()  
     
            If Not values.Any() Then Return Function(e) False 
     
            Dim equals = values.Select(Function(value) DirectCast(Expression.Equal(valueSelector.Body, Expression.Constant(value, GetType(TValue))), Expression))  
            Dim body = equals.Aggregate(Of Expression)(Function(accumulate, equal) Expression.Or(accumulate, equal))
            '''Here I have a problem, somehow in VB you have to provide a parameter seed (type Expression)
            '''seed - the initial accumulator value.
     
     
            Return Expression.Lambda(Of Func(Of TElement, Boolean))(body, p)  
        End Function 
     


    Shimmy
    Sunday, March 22, 2009 11:45 PM
  • Actuall it's:

    Dim body = equals.Aggregate(Function(accumulate, equal) Expression.Or(accumulate, equal))

    Sample:

    Imports System.Linq.Expressions  
     
    Module Module1  
     
        Sub Main()  
            Dim IDs = New Integer() {1, 4, 5, 6, 8}  
     
            Using bss As New BssEntities  
                Dim x = (From c In bss.Contact) _  
                        .Where(ContainsExpression(Function(con As Contact) con.ContactId, IDs))  
     
                Dim gh = x.ToList  
                Stop 
            End Using  
        End Sub 
     
     
        Public Function ContainsExpression(Of TElement, TValue)(ByVal valueSelector As Expression(Of Func(Of TElement, TValue)), ByVal values As IEnumerable(Of TValue)) As Expression(Of Func(Of TElement, Boolean))  
            If valueSelector Is Nothing Then 
                Throw New ArgumentNullException("valueSelector")  
            End If 
            If values Is Nothing Then 
                Throw New ArgumentNullException("values")  
            End If 
            Dim p As ParameterExpression = valueSelector.Parameters.Single()  
     
            If Not values.Any() Then Return Function(e) False 
     
            Dim equals = values.Select(Function(value) DirectCast(Expression.Equal(valueSelector.Body, Expression.Constant(value, GetType(TValue))), Expression))  
            Dim body = equals.Aggregate(Function(accumulate, equal) Expression.Or(accumulate, equal))  
     
            Return Expression.Lambda(Of Func(Of TElement, Boolean))(body, p)  
        End Function 
     
    End Module 
     
     
    • Proposed as answer by punkmonkey Monday, March 23, 2009 4:59 PM
    Sunday, March 22, 2009 11:49 PM
  • You rock. I'd mark this as the answer but the button eludes me.
    Monday, March 23, 2009 4:58 PM
  • So in the upcoming release v2, has the contains implementation improved to the fact that it works like in linq to sql like below

    Code Block

    List<int> statusesToFind = ...;

     

    var foos = from foo in myEntities.Foos

               where statusesToFind.Contains(foo.Status)

               select foo;

    Thanks
    Zeeshan Hirani

    Monday, March 23, 2009 5:08 PM
  • Thanks Zeeshan!

    I love u!

    thanks for your book, that was how I learned EF!



    ladies & gentlemen! take a look on his wonderful blog and download the book, it will open your eyes!

    http://weblogs.asp.net/zeeshanhirani/


    Thanks 'punkmonkey 2 u 2 4 the encouragement!!!



    Shimmy
    Monday, March 23, 2009 11:47 PM
  • I am having some trouble using the BuildContainsExpression in a many-to-many objectquery - I think the easiest way is to give an exampe;

    ObjectQuery<Company> lQuery = mRepository.CreateQuery<Company>("Company").Include("Evaluations").Where(lCompany => lCompany.Categories.Where(BuildContainsExpression<Category, int>(lCategory => lCategory.Id, pCategoryIds)));

    The above code does not work - but what I am trying to do is getting all companies which are in one or more of certain Categories (pCategoryIds). Any ideas how to build my query?

    Wednesday, March 25, 2009 5:07 PM
  • JC2003 said:

    is there away of converting this so i can do WhereNotIn?


    That's pretty easy actually:

    public static class QueryableExtensions 
        private static Expression<Func<TElement, bool>> 
            GetWhereNotInExpression<TElement, TValue>( 
            Expression<Func<TElement, TValue>> propertySelector, 
            IEnumerable<TValue> values) 
        { 
            ParameterExpression p = 
                propertySelector.Parameters.Single(); 
     
            if (!values.Any()) 
            { 
                return e => true
            } 
     
            var unequals = values.Select(value =>  
                (Expression)Expression.NotEqual( 
                    propertySelector.Body,  
                    Expression.Constant(value, typeof(TValue)) 
                ) 
            ); 
     
            var body = unequals.Aggregate<Expression>( 
                (accumulate, unequal) => Expression.And(accumulate, unequal)); 
     
            return Expression.Lambda<Func<TElement, bool>>(body, p); 
        } 
     
        public static IQueryable<TElement> 
            WhereNotIn<TElement, TValue>(this IQueryable<TElement> source, 
            Expression<Func<TElement, TValue>> propertySelector, 
            params TValue[] values) 
        { 
            return source.Where(GetWhereNotInExpression( 
                propertySelector, values)); 
        } 
     
        public static IQueryable<TElement> 
            WhereNotIn<TElement, TValue>(this IQueryable<TElement> source, 
            Expression<Func<TElement, TValue>> propertySelector, 
            IEnumerable<TValue> values) 
        { 
            return source.Where(GetWhereNotInExpression( 
                propertySelector, values)); 
        } 
    }  

    Does this help?

    Visit my blog: http://www.cuttingedge.it/blogs/steven/
    • Edited by NET Junkie Thursday, March 26, 2009 11:57 AM There was a bug in the GetWhereNotInExpression method.
    • Proposed as answer by NET Junkie Sunday, April 05, 2009 3:38 PM
    Thursday, March 26, 2009 9:49 AM
  • I've been trying the solutions here, but I keep getting the same error "Bitwise operators are not supported in queries. " The query I am trying to perform is:

     var query =
                    MyDBContext.KeywordsQuery.
                    Where<Keyword>(BuildContainsExpression<Keyword, int>(
                    k => (int)k.Value, valuesQuery));

    before this I was getting this error "Query operator 'Contains' is not supported" with this query:

     var query1 = from keyword in MyDBContext.KeywordsQuery
                    where keywordValues.Contains((int)keyword.Value)
                    select keyword;

    As far as I can tell Contains() is supported with Linq to SQL, but I can't seem to find anything on what I am experiencing.
    Tuesday, June 23, 2009 6:57 PM
  • I have decide to stick with the code below to do this kind of thing,
    I know it is making use of text to build the query but the BuildContainsExpression is more difficult to understand than a simply IN statement.

    Public Function GetCustomGroups(ByVal surveyCode As Guid, ByVal shortKeys As List(Of String)) As List(Of tblCustomOrgUnits)
    	Try
    		Dim query As String = String.Empty
    		Dim shortKeyList As String = String.Empty
    
    		'create a comma separated list of the shortKeys, which will be used in the IN statement.
    		For Each item As String In shortKeys
    			If (shortKeyList.Equals(String.Empty)) Then
    				shortKeyList &= "'" & item & "'"
    			Else
    				shortKeyList &= "," & "'" & item & "'"
    			End If
    		Next
    
    		'build a LINQ query that will be used to retrieve a list of objects
    		query &= "SELECT VALUE customGroup FROM tblCustomOrgUnits AS customGroup"
    		query &= " "
    		query &= "WHERE customGroup.survey_code = @surveyCode "
    		query &= " "
    		If (Not shortKeyList.Equals(String.Empty)) Then query &= "AND customGroup.identifier IN {" & shortKeyList & "}"
    		query &= " "
    		query &= "ORDER BY customGroup.name"
    
    		'instantiate the new ObjectQuery from the query that was built above
    		Dim listQuery = New ObjectQuery(Of tblCustomOrgUnits)(query, _context)
    
    		'add any parameters that can be provided directly (excluding things like the IN statement parameter(s))
    		listQuery.Parameters.Add(New ObjectParameter("surveyCode", surveyCode))
    
    		'return a list of entities only if one or more is returned by the list query above.
    		'if no items can be returned then rather return Nothing (null)
    		If (listQuery.Count = 0) Then
    			Return Nothing
    		Else
    			Return listQuery.ToList()
    		End If
    	Catch ex As Exception
    		Throw (ex)
    	End Try
    End Function

    I.W Coetzer
    Monday, June 29, 2009 9:34 AM
  • Maybe you can simplify this? or provide a full working example that does not make use of any "TEXT"
    I have also tried the other examples and failed to implement it therefore decided to stick with the working solution above until I understand and can implement the other way of doing this successfully!

    Bye

    I.W Coetzer
    Monday, June 29, 2009 9:35 AM
  • However you will quickly find out that when using Contains, there is a limit of 2100 items in your local collection due to how each item is translated into an individual parameter in the generated SQL statement. If EF uses the same approach as with Linq to SQL I'm sad to say that anybody who uses over 2100 items in their local collection will not be able to use this feature.

    If you would like to see Microsoft solve this issue please check out the link below and vote for my suggested workaround.

    https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=475984
    Tuesday, July 21, 2009 4:40 PM
  • Thanks for posting the solution for NOT IN.
    Friday, October 09, 2009 11:41 AM
  • Is that possible to use the BuildContainsExpression approach in Compiled Queries.
    http://ssis-stuff.blogspot.com
    Thursday, October 15, 2009 1:36 PM

  • I've been trying the solutions here, but I keep getting the same error "Bitwise operators are not supported in queries. " The query I am trying to perform is:

     var query =
                    MyDBContext.KeywordsQuery.
                    Where<Keyword>(BuildContainsExpression<Keyword, int>(
                    k => (int)k.Value, valuesQuery));

    before this I was getting this error "Query operator 'Contains' is not supported" with this query:

     var query1 = from keyword in MyDBContext.KeywordsQuery
                    where keywordValues.Contains((int)keyword.Value)
                    select keyword;

    As far as I can tell Contains() is supported with Linq to SQL, but I can't seem to find anything on what I am experiencing.

    I got the same Problem as him. Doing this over RIA services from a silverlight client. I am always getting this "Bitwise operators are not supported in queries." - error. No clue whatsoever. PLEAAASE HELP !

    My Code looks like this:

                List<int> itemsIds = new List<int>();
                foreach(Item i in items)
                    itemsIds.Add(i.itemId);
                var qry = Context.GetItemPropertyQuery().Where(EntityHelper.BuildContainsExpression<ItemProperty, int>(e => e.itemId, itemsIds));
                this.ExecuteItemPropertyQuery(qry);


    and the load

    private void ExecuteItemPropertyQuery(EntityQuery<ItemProperty> qry)
            {
                // Execute the query
                Context.Load<ItemProperty>(qry, op =>
                {
              ....
    },null);
    }


    someone got a clue what I am doing wrong? would be reall appreciated.

    full error looks like this:


    System.NotSupportedException was unhandled by user code
      Message="Bitwise operators are not supported in queries"
      StackTrace:
           at System.Windows.Ria.Linq.QuerySerializer.Visitor.VisitBinary(BinaryExpression b)
           at System.Windows.Ria.Linq.ExpressionVisitor.Visit(Expression exp)
           at System.Windows.Ria.Linq.QuerySerializer.Visitor.VisitBinary(BinaryExpression b)
           at System.Windows.Ria.Linq.ExpressionVisitor.Visit(Expression exp)
           at System.Windows.Ria.Linq.QuerySerializer.Visitor.VisitBinary(BinaryExpression b)
           at System.Windows.Ria.Linq.ExpressionVisitor.Visit(Expression exp)
           at System.Windows.Ria.Linq.QuerySerializer.Visitor.VisitBinary(BinaryExpression b)
           at System.Windows.Ria.Linq.ExpressionVisitor.Visit(Expression exp)
           at System.Windows.Ria.Linq.QuerySerializer.Visitor.VisitBinary(BinaryExpression b)
           at System.Windows.Ria.Linq.ExpressionVisitor.Visit(Expression exp)
           at System.Windows.Ria.Linq.QuerySerializer.Visitor.VisitBinary(BinaryExpression b)
           at System.Windows.Ria.Linq.ExpressionVisitor.Visit(Expression exp)
           at System.Windows.Ria.Linq.QuerySerializer.Visitor.VisitBinary(BinaryExpression b)
           at System.Windows.Ria.Linq.ExpressionVisitor.Visit(Expression exp)
           at System.Windows.Ria.Linq.QuerySerializer.Visitor.VisitBinary(BinaryExpression b)
           at System.Windows.Ria.Linq.ExpressionVisitor.Visit(Expression exp)
           at System.Windows.Ria.Linq.QuerySerializer.Visitor.VisitBinary(BinaryExpression b)
           at System.Windows.Ria.Linq.ExpressionVisitor.Visit(Expression exp)
           at System.Windows.Ria.Linq.ExpressionVisitor.VisitLambda(LambdaExpression lambda)
           at System.Windows.Ria.Linq.QuerySerializer.Visitor.VisitLambda(LambdaExpression lambda)
           at System.Windows.Ria.Linq.ExpressionVisitor.Visit(Expression exp)
           at System.Windows.Ria.Linq.ExpressionVisitor.VisitUnary(UnaryExpression u)
           at System.Windows.Ria.Linq.QuerySerializer.Visitor.VisitUnary(UnaryExpression u)
           at System.Windows.Ria.Linq.ExpressionVisitor.Visit(Expression exp)
           at System.Windows.Ria.Linq.ExpressionVisitor.VisitExpressionList(ReadOnlyCollection`1 original)
           at System.Windows.Ria.Linq.QuerySerializer.Visitor.VisitMethodCall(MethodCallExpression m)
           at System.Windows.Ria.Linq.ExpressionVisitor.Visit(Expression exp)
           at System.Windows.Ria.Linq.QuerySerializer.Visitor.Visit(Expression expr, List`1& queryPartsList)
           at System.Windows.Ria.Linq.QuerySerializer.Serialize(IQueryable query)
           at System.Windows.Ria.Data.HttpDomainClient.GetOperationData(IDictionary`2 parameters, IQueryable query)
           at System.Windows.Ria.Data.HttpDomainClient.BeginQueryCore(String queryName, IDictionary`2 parameters, IQueryable query, Boolean hasSideEffects, AsyncCallback callback, Object userState)
           at System.Windows.Ria.Data.DomainClient.BeginQuery(String queryName, IDictionary`2 parameters, IQueryable query, Boolean hasSideEffects, AsyncCallback callback, Object userState)
           at System.Windows.Ria.Data.DomainContext.Load(EntityQuery query, MergeOption mergeOption, Delegate callback, Object userState)
           at System.Windows.Ria.Data.DomainContext.Load[TEntity](EntityQuery`1 query, MergeOption mergeOption, Action`1 callback, Object userState)
           at System.Windows.Ria.Data.DomainContext.Load[TEntity](EntityQuery`1 query, Action`1 callback, Object userState)
           at XSlide.Data.Catalogues.ItemCatalog.ExecuteItemPropertyQuery(EntityQuery`1 qry)
           at XSlide.Data.Catalogues.ItemCatalog.GetItemProperties(ObservableCollection`1 items)
           at XSlide.Data.ViewModels.ItemsViewModel.LoadItemProperties()
           at XSlide.Data.Controls.SlideDrawCanvas._viewModel_LoadComplete(Object sender, EventArgs e)
           at XSlide.Data.ViewModels.ItemsViewModel.<>c__DisplayClasse.<_catalog_ItemLoadingComplete>b__d()
      InnerException: 
    

    Sunday, October 18, 2009 11:42 PM
  • well finally someone on the silverlight forums heard me and I fixed that problem by using a simple OrElse instead of a Or in the BuildContainsExpression. Now it works :)

    I'll put the code for those who had the same problem:

            public static Expression<Func<TElement, bool>> BuildContainsExpression<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.OrElse(accumulate, equal));
                return Expression.Lambda<Func<TElement, bool>>(body, p);
    
            }


    cheers
    Tim
    Monday, October 19, 2009 9:55 PM
  • Hi Zeeshan/Colin,
    Can you please fit the below "BuildContainsExpression" query in my Join query:

    var query = dao.LineItemInterface.Where(BuildContainsExpression<LineItemInterface, string>(l => l.BillingGroupID, strArray));

    My Join Query

    var

     

    q = from l in blendedItems

     

    join v in blendedItemsView

     

    on l.LineItemID equals v.LineItemID

     

    where v.Status == "Blended"

    && strArray.Contains(v.BillingGroupID)  //should go here...

     

    select l;

    Thanks in advance,
    Abrar

    Tuesday, November 10, 2009 9:04 PM
  • i faced same kind of problem  and solve it some thing like that
    Entity Framework does not currently support collection-valued parameters so i take help of join 
    i make join





                hydgisgdbEntities db = new hydgisgdbEntities();


                var qurey = (from c1 in ((from c in db.landmarks
                                          where c.easting >= (long)MinX &&
                                        c.easting <= (long)MaxX &&
                                        c.northing >= (long)MinY &&
                                        c.northing <= (long)MaxY 
                                          
                                                                                select c).ToList())
                                join c2 in subcat.ToList()  on c1.lmsubcategory   equals c2
                                          
                                                        select c1).ToList();


    subcat.ToList() list arry int i convert in to list and make job with 

    for more help visit 
    Usamawahabkhan.blogspot.com
    Wednesday, November 11, 2009 10:46 AM
  • Hi,
    I have a "WHERE IN" query which works fine with one condition, now i want to add one more condition in my existing query.
    Can you please help me out in writing the second condition.

    my second condition is "WHERE cust.Region == _strRegionName"

    below is my query with first condition with "WHERE IN or BuildContainsExpression" which is working fine, i want to add second condition in this query:

    var query = dao.Customers.Where(BuildContainsExpression<Customers, string>(c => c.CustomerID, strArray));


    static Expression<Func<TElement, bool>> BuildContainsExpression<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();

                        // p => valueSelector(p) == values[0] || valueSelector(p) == ...

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

                    }

    Thanks,
    Maria

    Wednesday, November 11, 2009 3:42 PM
  • If you want to add another condition with and operator, simply add it after the query with another where function.
    But I'm trying to solve the same problem with OR, and still didn't manage to solve it.
    Ghamsary
    Monday, November 23, 2009 7:38 PM
  • Friday, November 27, 2009 7:39 PM
  • Hi Colin,

    Thank you for publishing your utility function.  Most helpful.

    I am a fairly seasoned developer but quite new to LINQ and the EF.  I am still developing my first EF application using VS.NET 2008 and ASP 3.5 SP1 and have really been enjoying the experience, less a few of the big gotchas that are out there (such as this WHERE IN problem).  Perhaps you could help steer me in the right direction on using your BuildContainsExpression() with the WHERE clause when I have multiple WHERE conditions.  I want to be able to write the equivalent of:

    SELECT
         customer.CustomerID, customer.CustomerName
    FROM
         customer
    WHERE
         (customer.IsDeleted = 0 AND customer.IsEnabled = 1) OR customer.CustomerID IN ('1, 2, 3')
    ORDER BY
         customer.SortIndex ASC

    Any help you can provide for using the entity WHERE along with your BuildContainsExpression along with my own expressions would be greatly appreciated.

    Thanks again for your original post.

    - David
    Tuesday, December 22, 2009 2:54 AM
  • All solutions presented here (WhereIn, BuildContainsExpression, ESQL) have the same problem: if the value list is extensive (> 600-800 items ) in the VS IDE works fine but in a WCF service (IIS installed) there is a strange error that brings down the service altogether, and you can not handle that error in any log or CATCH.

    Seems that when LinQ generates the expression exceeds one limit of IIS. In my tests allowed up to 800 values IIS6 and IIS7 to 500.

    The only solution I found to this problem was to divide the values in batches, generate a complete expression for each lot and later along with UNION.

    Here I will leave the extension wherein modified to support this situation:

     

    Please sorry my poor english.

     

            public static IQueryable<TElement> WhereIn<TElement, TValue>(this IQueryable<TElement> source, Expression<Func<TElement, TValue>> propertySelector, params TValue[] values)
            {
                return source.Where(GetWhereInExpression(propertySelector, values));
            }
    
            public static IQueryable<TElement> WhereIn<TElement, TValue>(this IQueryable<TElement> source, Expression<Func<TElement, TValue>> propertySelector, IEnumerable<TValue> values)
            {
                //Agregar WHERE por los (iLote) primeros valores
                int iLote = 500;
                var queryOriginal = source;
                source = queryOriginal
                    .Where(GetWhereInExpression(propertySelector, values.Take(iLote).ToList()));
                //Si hay más valores, se van agregando en lotes con UNION
                for (int i = iLote; i < values.Count(); i += iLote)
                {
                    source = source.Union(
                        queryOriginal.WhereIn(propertySelector, values.Skip(i).Take(iLote).ToList())
                        );
                }
                return source;
                //return source.Where(GetWhereInExpression(propertySelector, values));
            }
    

     

    • Proposed as answer by sarponet Thursday, January 28, 2010 8:41 PM
    Thursday, January 28, 2010 4:40 PM
  • Hi,

    I use EF and I want to make a query with a "GetType()" method

    I have a list of objects "people" and two child: "Collab" and "Extern"

    The error query:

    --------------------------------------------------------------------------

    res =

    from p in GetAllPeople()

     

     

    where p.GetType() == typeof(Extern)

     

     

    select p;

    The GetType don't be use in where clause :-(

    --------------------------------------------------------------------------

    I try to use a method to make my test between type:

    res = GetAllPeople().Where(p => (isSameType(

    typeof(Collab), p) && (p as Collab).MyGroup.intId == p_group)

    || (isSameType(

     

    typeof(Extern), p)));

     

     

    private bool isSameType(Type t, object o)

    {

     

    return o.GetType() == t;}

    But I see that I should use Expression.Lambda :-(

    After a lot of investigation, i'm loose in all this lambda expression.

    Please, show me a sample to make a 'isSameType' method (just an equal clause)

     

    so thanks

     

    Maxime

    Wednesday, April 28, 2010 8:05 AM
  • How can I get the WhereIn method?
    Monday, June 21, 2010 3:09 AM
  • thanks a million

    the fantastic "it."

    it really works

    Monday, June 21, 2010 3:11 AM
  • Hi -

     

      I have used the WhereIn code and the BuildcontainsExpression code.   Both build the appropriate OR clause.

     

     The problem I have is that my original query contained an order by clause which is now gone from the generated SQL. 

     

      Here is my code.

     

    var qrySelectAll = from trip in db.Trips
                    from tc in db.TripCountries
                    where tc.Trips.TripID == trip.TripID
                    orderby tc.Country, trip.TripBeginDate
                    select trip;
    
    
    List<int> listTripIDs = (from country in db.TripCountries where country.Country.Equals(psTripCountry) select country.Trips.TripID).ToList<int>();
    
    
    qrySelectAll = qrySelectAll.Where(QueryableExtensions.BuildContainsExpression<Trips, int>(trip => trip.TripID, listTripIDs));
    
    
    qrySelectAll = qrySelectAll.WhereIn(trip => trip.TripID, listTripIDs);
    
    
     qrySelectAll.ToList()

     

    I don't have time to troubleshoot this issue.  Has anyone else come across this issue and fixed it?

     

    In my example code I included calls to both the WhereIn and the BuildContainsExpression.  I will only utilize one of those methods once this issue is fixed.

     

    Thanks 

    Wednesday, June 30, 2010 7:19 PM
  • This blows....  (with an int array of 2554...)
    Thursday, July 15, 2010 1:31 PM
  • Thanks James. That worked for me.
    Tuesday, October 05, 2010 6:38 PM
  • What should happen when the 'values' collection is empty?  currently, the result set is empty which I think is incorrect.  When the 'values' collection is empty it means that the filter is not to be applied and should return the result w/o applying any filters.  In SQL, this syntax won't even be possible.  So no argument there.  any thoughts?

     //Old
        if (!values.Any()) 
          return e => false; 
    //new
        if (!values.Any()) 
          return e => true; 
    
    

    thanks

    Tuesday, November 16, 2010 7:48 PM
  • Hello All,

    I still see activity in this thread from time to time, so I thought it was worth mentioning that in EF4 we added support for the Contains method and at least in this specific case for collection-valued parameters. Therefore this kind of code now works right out of the box and it is not necesary to use any additinal expression building method:

    var statusesToFind = new List<int> {1, 2, 3, 4};
    
    var foos = from foo in myEntities.Foos
          where statusesToFind.Contains(foo.Status)
          select foo;
    
    

    Hope this helps,
    Diego


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, November 26, 2010 1:59 PM
  • Please know a solution for using dynamic linq to entity "OR"?

    Wednesday, December 08, 2010 3:13 AM

  • Please know a solution for using dynamic linq to entity "OR"?
    Wednesday, December 08, 2010 3:14 AM
  • Hi Diego,

    this seems to be only half way true, since CompiledQuery doesn't work with collections.

    (throws a NotSupportedException: "...Only scalar parameters (such as Int32, Decimal, and Guid) are supported."

    Sample:

     

    public
    
     class
    
     TestQuery
    {
     public
    
     static
    
     Expression<Func<AdventureWorksEntities, IEnumerable<int
    
    >, IEnumerable<Product>>> Exp
     {
      get
    
    
      {
       return
    
     (context, ids) =>
         from
    
     product in
    
     context.Products
         where
    
     ids.Contains(product.ProductID)
         select
    
     product;
      }
     }
    
     public
    
     static
    
     Func<AdventureWorksEntities, IEnumerable<int
    
    >, IEnumerable<Product>> ExpCompiled =
      CompiledQuery.Compile(Exp);
    
     public
    
     static
    
     IEnumerable<Product> Execute(IEnumerable<int
    
    > ids)
     {
      using
    
     (AdventureWorksEntities context = new
    
     AdventureWorksEntities())
       return
    
     ExpCompiled.Invoke(context, ids).ToArray();
     }
    }
    
    

    Update: Found this post , explaining why CompiledQuery cannot work with enumerables.

    Tuesday, December 14, 2010 10:21 AM
  • Diego,

    I am not able to query on multiple items when a mapping table is present.

    I wrote the following in EF 4 using VS 2010...

    Formats [1,2,3,4,5]

    RecordFormatMapping [[1,31],[2,32],[3,33],[4,33],[5,33]]

    Records [31,32,33,34,35] (values for property ID)

     

    var list = new List<long> { 2,3,4 }; (list of format IDs)

    context.Records.Where(x => x.Formats.Any(y => list.Contains(y.ID)) || list.Count == 0) 

    (should return an IQueryable<Record> containing record IDs 32,33)

     

    Unless there is a mistake in my logic, this gives me an error of:

    Unable to create a constant value of type 'System.Collections.Generic.IList`1'. Only primitive types ('such as Int32, String, and Guid') are supported in this context

     

    Is this type of query possible in EF 4?

     

    Thank you

    • Proposed as answer by Vince2010 Tuesday, March 01, 2011 10:30 PM
    • Unproposed as answer by Vince2010 Tuesday, March 01, 2011 10:30 PM
    Friday, February 25, 2011 1:23 AM
  • Diego, Contains is good.  Would it be possible to get a NotContains as well?
    Tuesday, March 01, 2011 8:17 PM
  • Diego,

    To answer my own question and for anyone else...

    The problem was not the "Contains" method but with the Telerik MVC JavaScript Serializer (which is part of the grid's built-in ajax API) which parsed the list in a way that the default model binder could not interpret.  Therefore, the list was always null, and blew up the query with the above error.

    So, I created a custom model binder that always instantiates a generic list property (whether it is empty or not). Then I finished the Link-to-Entities statement as follows (which works nicely):

    someMethod(criteria c)

    {

    ...

    context.Records.Where(item => (c.list.All(n => (item.Formats.Select(y => y.ID)).Contains(n)) || c.list.Count == 0))

    ...

    }

     

    Hope this helps someone!

    Tuesday, March 01, 2011 10:52 PM
  • Coffee Guy,

    If "Not Contains" is what you need, you can simply negate the Contains, e.g.:

    var foos = from foo in myEntities.Foos
          where !statusesToFind.Contains(foo.Status)
          select foo;

     

    Hope this helps,
    Diego 


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, March 02, 2011 5:38 AM
  • How to convert this linq query into lambda expression

    Dim

     

    query = From c In Me.ObjectContext.Clientes

                Join uc In Me.ObjectContext.UsuarioCliente On c.idCompania Equals uc.idCompania And c.idCliente Equals uc.idCliente

                Where c.idCompania = "AC" And uc.Usuario = "admin"

                Select c

    HELP ME PLEASEEEEEEEEEE! :(

     
    Friday, July 08, 2011 1:24 PM
  • Where can I go to download the newest version of EF 4.0 ?

    Specifically I am coding this

           public List<MessageTemplateSchedule> GetMessageTemplateSchedules(List<MessageTemplateSchedule> messageTemplateSchedules)
            {
                List<MessageTemplateSchedule> schedules = new List<MessageTemplateSchedule>();
                using (Terryberry.Infrastructure.Data.EF.TerryberryAppsEntities db = new Data.EF.TerryberryAppsEntities())
                {
                    try
                    {
                       var templateSchedules = from dbMtSchedules in db.MessageTemplateSchedules
                                                       where messageTemplateSchedules.Exists(p=>dbMtSchedules.MessageTemplateId == p.MessageTemplate.Id)
                                                       select dbMtSchedules;
                        //todo: construct schedules
                        return schedules;
                    }
                    catch (Exception e)
                    {
                            
                        throw new Exception("Could not get the Messagetemplates",e);
                    }
                }
            }


    • Edited by degan67 Wednesday, October 05, 2011 5:16 PM
    Wednesday, October 05, 2011 5:11 PM
  • Gracias duendecilla

     (thanks little elf [Colin Meek])

     

    Me funciono tu método mágico

    (I worked your magic method)

     

    =D

    • Edited by ch2o Wednesday, November 09, 2011 4:11 PM
    Wednesday, November 09, 2011 4:10 PM
  • Hi, Colin!

     

    Very very very much! This solution it work perfectly!

     

    You help me and others million programmers around the world!


    Fábio Oliveira Desenvolvedor Microsoft Dynamics ERP AX - BI
    Sunday, January 22, 2012 2:28 PM
  • Hello all :)

    im getting a very strange issue:

    this code:

    var query = myContext.myEntity.Where(x => x.meaning.Contains("abc") || x.meaning.Contains("bac") || x.meaning.Contains("whatever"));

    works....

    but i cant know how many conditions the user could type in the search box, so...

    string[] parts = mySearch.Split(',');

    var query = myContext.myEntity.WhereIn(x => x.meaning, parts);

    this... dont work

    and...

    var query = from c in myContext.myEntity

                       where parts.Contains(c.meaning)

                       select c;

    dont work....

    someone can help ?

    Thank you :)

    RaouL.

    Tuesday, March 13, 2012 11:49 PM
  • actually i've solved in a very horror mode:

    var strArray = someText.Split(,);
    List<MyEntity> list = new List<MyEntity>();
    var query = context.MyEntity.ToList();
    
    foreach(var obj in query)
    {
       if(obj.meaning != null)
       {
          foreach(string s in strArray)
          {
            if(obj.meaning.Contains(s))
               list.Add(obj);
          }
       }
    }
    
    doSomeStuffWithTheList(list);
    


    Wednesday, March 14, 2012 9:23 AM
  • it works for me,

    great.

    thanks Frederic Ouellet


    savsani s.m.

    Wednesday, August 29, 2012 5:49 AM
  • var statusesToFind = new int[] {14,8,2};

    var foos = from foo in myEntities.Foos  where statusesToFind .contains(foo.status) select foo;

     

    I Hope This will Help You  !!!


          

    Monday, December 10, 2012 10:59 AM