locked
LINQ query -> OR predicates.. RRS feed

  • Question

  • I have a simple algorithm to create queries in my Azure tablestores, basically through agglomeration as show below where query is an IQueryable<Matter> object:

     

      var query = context.Matters;


                //if not default, assume it is a parameter

                if (!this.MatterIdentifier.Equals(Guid.Empty))

                {

                    query = query.Where(b => b.Identifier == this.MatterIdentifier);

                }


        if (ClientId != string.Empty)

                {

                    query = query.Where(b => b.ClientId == this.ClientId);

                }

    My problem is when I want to represent OR'd items (like in an SQL IN clause). Below, MatterIds represents a list of Guids. I want each Guid to be OR'd with the others but the whole set to be AND'd with everything before and after,i.e. the query builder continues after the bit below. 

    This example creates a set of AND'd items which is not my intention. How to neatly do ORs?

     

                if (this.MatterIds.Count > 0)

                {

                   query = MatterIds.Aggregate(query, (current, g) => current.Where(b => b.Identifier == g));

                }

     

     

           thanks

    Thursday, March 10, 2011 9:24 PM

Answers

  • Hi Christopher,

    From what I understand, you want to dynamically add "Or" predicates. If yes, would the following code help in what you are trying to do?

       BinaryExpression filter = null;
    
       ParameterExpression param = Expression.Parameter(typeof(MyEntity), "e");
    
       MemberExpression leftRowKey = Expression.Property(param, "RowKey");
    
       MemberExpression leftPartitionkey = Expression.Property(param, "PartitionKey");
    
    
    
       // Add "RowKey == 'somevalue'" if row key is provided
    
       if (!string.IsNullOrEmpty(rowKey1))
    
       {
    
        //MemberExpression left = Expression.Property(param, "RowKey");
    
        BinaryExpression rowKey1Comparison = Expression.Equal(leftRowKey, Expression.Constant(rowKey1));
    
        filter = rowKey1Comparison;
    
       }
    
    
    
       // Add "or RowKey == 'somevalue'" if another row key value is provided
    
       if (!string.IsNullOrEmpty(rowKey2))
    
       {
    
        BinaryExpression rowKey2Comparison = Expression.Equal(leftRowKey, Expression.Constant(rowKey2));
    
        filter = (filter == null) ? rowKey2Comparison : Expression.Or(filter, rowKey2Comparison);
    
       }
    
    
    
       // Add "and PartitionKey == 'somevalue'" if partition key is provided
    
       if(!string.IsNullOrEmpty(partitionKey))
    
       {
    
        BinaryExpression partitionKeyComparison = Expression.Equal(leftPartitionkey, Expression.Constant(partitionKey));
    
        filter = (filter == null) ? partitionKeyComparison : Expression.AndAlso(filter, partitionKeyComparison);
    
       }
    
    
    
       Expression<Func<MyEntity, bool>> filterLambdaExpression = Expression.Lambda<Func<MyEntity, bool>>(filter, param);
    
       DataServiceQuery<MyEntity> query = (DataServiceQuery<MyEntity>)from e in context.CreateQuery<MyEntity>(tableName) select e;
    
       
    
       // the final query is of form PK == "x" And (RK == "y" OR RK == "z")
    
       query = (DataServiceQuery<MyEntity>)query.Where<MyEntity>(filterLambdaExpression);
    
    
    
       Console.WriteLine(query.RequestUri.AbsoluteUri);
    
    
    
    

    Having said that, if you are using OR'd filters on PartitionKey or RowKey it is better to execute multiple queries as it is more efficient. See the following post for some details - http://social.msdn.microsoft.com/Forums/en-US/windowsazuredata/thread/d7765773-74b8-4860-b07c-b9731a2210c7 and more on our team blog: http://blogs.msdn.com/b/windowsazurestorage/archive/2010/11/06/how-to-get-most-out-of-windows-azure-tables.aspx

    • Marked as answer by Brad Calder Sunday, March 27, 2011 12:22 AM
    Saturday, March 26, 2011 8:17 PM

All replies

  • Hi,

    Does this work?

    query= query.Where(b=>this.MatterIds.Contains(b.Identifier));

    Thanks,


    Mog Liang
    Please mark the replies as answers if they help or unmark if not.
    If you have any feedback about my replies, please contact msdnmg@microsoft.com.
    Microsoft One Code Framework
    Friday, March 11, 2011 8:22 AM
  • No unfortunately. That query defines where b.Identifier is within the set of MatterIds. 

    What I need is to discover whether each element in the matterIds set is within the tablestore table...



    e.g.



    for (int i = 0; i < MatterIds.Count;i++)

    {

      query = query.Where(b => b.Identifier == this.MatterIds[0]);

    }

    Friday, March 11, 2011 12:40 PM
  • ***missed a bit*****

    but what I am missing is how to make the query accumulator (query = query.Where()) treat it as an OR, not an AND relation. 

     

     

    Friday, March 11, 2011 12:41 PM
  • Hi,

    You may try use PredicateBuilder to create Or relation, please refer to this thread

    http://stackoverflow.com/questions/2101540/linq-or-equivalent-of-where

    Thanks,


    Mog Liang
    Please mark the replies as answers if they help or unmark if not.
    If you have any feedback about my replies, please contact msdnmg@microsoft.com.
    Microsoft One Code Framework
    • Proposed as answer by Mog Liang Thursday, March 17, 2011 10:09 AM
    Monday, March 14, 2011 8:04 AM
  • Well... it doesn't seem to work, at least not in my algorithm. I basically use an agglutinative structure which works great with the implicit AND. Note what I have placed in bold below the code using PredicateBuilder. It throws an exception: 

    {Error translating Linq expression to URI: The expression (False And Invoke(f => (f.Identifier != e03e46ce-6355-447c-9f12-cf4eaf31ff60),[10007])) is not supported.}

     

    Here's what I have:

     

        private IQueryable<Matter> GetQuery(ref MatterCloudContext context)

            {

                var query = context.Matters;

     

                //if not default, assume it is a parameter

                if (!this.MatterIdentifier.Equals(Guid.Empty))

                {

                    query = query.Where(b => b.Identifier == this.MatterIdentifier);

                }

     

     

    #region experimental

     

                if (this.MatterIds.Count > 0)

                {

                    //see http://www.albahari.com/nutshell/predicatebuilder.aspx

                    var predicate = PredicateBuilder.False<Matter>();

                    foreach (Guid g in MatterIds)

                    {

                        predicate = PredicateBuilder.False<Matter>();

                        predicate = predicate.Or(f => f.Identifier == g);

                        query = query.Where(predicate);

                    }

                }

     

                if (this.MatterIdsToExclude.Count > 0)

                {

                    //see http://www.albahari.com/nutshell/predicatebuilder.aspx

                    var predicate = PredicateBuilder.False<Matter>();

                    foreach (Guid g in MatterIdsToExclude)

                    {

                        predicate = PredicateBuilder.False<Matter>();

                        predicate = predicate.And(f => f.Identifier != g);

                        query = query.Where(predicate);

                    }

                }

    #endregion 

     

               ...

                return query;

     

            }

    Monday, March 21, 2011 8:04 PM
  • Hi Christopher,

    From what I understand, you want to dynamically add "Or" predicates. If yes, would the following code help in what you are trying to do?

       BinaryExpression filter = null;
    
       ParameterExpression param = Expression.Parameter(typeof(MyEntity), "e");
    
       MemberExpression leftRowKey = Expression.Property(param, "RowKey");
    
       MemberExpression leftPartitionkey = Expression.Property(param, "PartitionKey");
    
    
    
       // Add "RowKey == 'somevalue'" if row key is provided
    
       if (!string.IsNullOrEmpty(rowKey1))
    
       {
    
        //MemberExpression left = Expression.Property(param, "RowKey");
    
        BinaryExpression rowKey1Comparison = Expression.Equal(leftRowKey, Expression.Constant(rowKey1));
    
        filter = rowKey1Comparison;
    
       }
    
    
    
       // Add "or RowKey == 'somevalue'" if another row key value is provided
    
       if (!string.IsNullOrEmpty(rowKey2))
    
       {
    
        BinaryExpression rowKey2Comparison = Expression.Equal(leftRowKey, Expression.Constant(rowKey2));
    
        filter = (filter == null) ? rowKey2Comparison : Expression.Or(filter, rowKey2Comparison);
    
       }
    
    
    
       // Add "and PartitionKey == 'somevalue'" if partition key is provided
    
       if(!string.IsNullOrEmpty(partitionKey))
    
       {
    
        BinaryExpression partitionKeyComparison = Expression.Equal(leftPartitionkey, Expression.Constant(partitionKey));
    
        filter = (filter == null) ? partitionKeyComparison : Expression.AndAlso(filter, partitionKeyComparison);
    
       }
    
    
    
       Expression<Func<MyEntity, bool>> filterLambdaExpression = Expression.Lambda<Func<MyEntity, bool>>(filter, param);
    
       DataServiceQuery<MyEntity> query = (DataServiceQuery<MyEntity>)from e in context.CreateQuery<MyEntity>(tableName) select e;
    
       
    
       // the final query is of form PK == "x" And (RK == "y" OR RK == "z")
    
       query = (DataServiceQuery<MyEntity>)query.Where<MyEntity>(filterLambdaExpression);
    
    
    
       Console.WriteLine(query.RequestUri.AbsoluteUri);
    
    
    
    

    Having said that, if you are using OR'd filters on PartitionKey or RowKey it is better to execute multiple queries as it is more efficient. See the following post for some details - http://social.msdn.microsoft.com/Forums/en-US/windowsazuredata/thread/d7765773-74b8-4860-b07c-b9731a2210c7 and more on our team blog: http://blogs.msdn.com/b/windowsazurestorage/archive/2010/11/06/how-to-get-most-out-of-windows-azure-tables.aspx

    • Marked as answer by Brad Calder Sunday, March 27, 2011 12:22 AM
    Saturday, March 26, 2011 8:17 PM