New Posts New Posts RSS Feed: Multiple Where clause
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Multiple Where clause
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
k_cire0426 View Drop Down
Newbie
Newbie
Avatar

Joined: 21-Jan-2012
Posts: 12 		Post Options Post Options   Quote k_cire0426 Quote  Post ReplyReply Direct Link To This Post Topic: Multiple Where clause
    Posted: 13-Feb-2012 at 9:14pm
Hello,

How do I convert this SQL Where Expression to CompositePredicateDescription?

WHERE (AccountID = '123' AND Group = 'Group1') 
OR AccountID = '1234'

thanks
Back to Top
DenisK View Drop Down
IdeaBlade
IdeaBlade


Joined: 25-Aug-2010
Posts: 715 		Post Options Post Options   Quote DenisK Quote  Post ReplyReply Direct Link To This Post Posted: 14-Feb-2012 at 6:42pm
Hi k_cire0426,

Here's an example.

      var pd1 = new PredicateDescription("AccountID", FilterOperator.IsEqualTo, "123");
      var pd2 = new PredicateDescription("Group", FilterOperator.IsEqualTo, "Group1");
      var pd3 = new PredicateDescription("AccountID ", FilterOperator.IsEqualTo, "1234");
      
      var pd = (pd1.And(pd2)).Or(pd3);
Back to Top
k_cire0426 View Drop Down
Newbie
Newbie
Avatar

Joined: 21-Jan-2012
Posts: 12 		Post Options Post Options   Quote k_cire0426 Quote  Post ReplyReply Direct Link To This Post Posted: 15-Feb-2012 at 9:34pm
hello denisK, :)

Your sample is working, however it seems not working when combining each predicatedescription in a for loop one by one. I have all my PredicateDescription in the ArrayList..

thanks...
Back to Top
DenisK View Drop Down
IdeaBlade
IdeaBlade


Joined: 25-Aug-2010
Posts: 715 		Post Options Post Options   Quote DenisK Quote  Post ReplyReply Direct Link To This Post Posted: 16-Feb-2012 at 11:01am
Would you be able to post a code snippet?
Back to Top
k_cire0426 View Drop Down
Newbie
Newbie
Avatar

Joined: 21-Jan-2012
Posts: 12 		Post Options Post Options   Quote k_cire0426 Quote  Post ReplyReply Direct Link To This Post Posted: 16-Feb-2012 at 4:44pm
hello denisK,

Here is the sample code. 

The code below assuming that we have 3 predicates and the 1 and 2 predicate will be in group.

            CompositePredicateDescription compPredicates = null;
            IPredicateDescription predicate = null;
 
            for (int arrCount = 0; arrCount <= arrPredicates.Count; arrCount++)
            {
                if (predicate != null)
                {
                    if (compPredicates == null)
                    {
                        compPredicates = predicate.And((IPredicateDescription)arrPredicates[arrCount]);
                        //tried this as well
                        //compPredicates = (predicate.And((IPredicateDescription)arrPredicates[arrCount]));
                    }
                    else
                    {
			

			//here is the part where i add the parenthesis, but i tried the above as well...
                        compPredicates = (compPredicates).Or((IPredicateDescription)arrPredicates[arrCount]);
                    }
                }
                else
                {
                    predicate = (IPredicateDescription)arrPredicates[arrCount];
                }
            }

   

   
   
  		
 

 

  Back to Top
 

 

  
   
   
   DenisK View Drop Down
   
   
IdeaBlade
   
IdeaBlade
   

   

Joined: 25-Aug-2010
   
Posts: 715
   
  		
  
   
    Post Options Post Options
    
      Quote DenisK Quote  Post ReplyReply
   
   Direct Link To This Post Posted: 16-Feb-2012 at 7:24pm
 

 

  
   
   
   

   The following works when I tested it.



[TestMethod]
    public void DynamicQueryCombiningPredicates() {
      //T11526
      var pd1 = new PredicateDescription("UnitPrice", FilterOperator.IsGreaterThanOrEqualTo, 24);
      var pd2 = new PredicateDescription("Discontinued", FilterOperator.IsEqualTo, true);
      var pd3 = new PredicateDescription("Category.Name", FilterOperator.StartsWith, "P");
      var arrPredicates = new ArrayList { pd1, pd2, pd3 };
      
      CompositePredicateDescription compPredicates = null;
      IPredicateDescription predicate = null;

      for (int arrCount = 0; arrCount < arrPredicates.Count; arrCount++) {
        if (predicate != null) {
          if (compPredicates == null) {
            compPredicates = predicate.And((IPredicateDescription)arrPredicates[arrCount]);
            //tried this as well
            //compPredicates = (predicate.And((IPredicateDescription)arrPredicates[arrCount]));
          } else {

            //here is the part where i add the parenthesis, but i tried the above as well...
            compPredicates = (compPredicates).Or((IPredicateDescription)arrPredicates[arrCount]);
          }
        } else {
          predicate = (IPredicateDescription)arrPredicates[arrCount];
        }
      }

      var r1 = _em1.Products.Where(p => (p.UnitPrice >= 24 && p.Discontinued) || p.Category.Name.StartsWith("P")).ToList();
      var r2 = _em1.Products.Where(compPredicates).ToList();
      Assert.IsTrue(r1.Count == r2.Count);
    }

I had to change

arrCount <= arrPredicates.Count

to 

arrCount < arrPredicates.Count 

or else it throws index out of range exception.

   

   
   
  		
 

 

  Back to Top
 

 

  
   
   
   k_cire0426 View Drop Down
   
   
Newbie
   
Newbie
   
Avatar
   

Joined: 21-Jan-2012
   
Posts: 12
   
  		
  
   
    Post Options Post Options
    
      Quote k_cire0426 Quote  Post ReplyReply
   
   Direct Link To This Post Posted: 17-Feb-2012 at 12:56am
 

 

  
   
   
   

   i wonder how it works in your end...I am checking the script on profiler and it definitely not grouping.. i'll find what's causing this... 
   

   
   
  		
 

 

  Back to Top
 






 

  
    Post Reply Post Reply 
  
  
  
 







 

  
   Forum Jump
   
  
  Forum Permissions  View Drop Down