Hi

I don't know why, but I'm having the hardest time construction the predicate expression that will accomplish the following:

(Table1.Foo = @param1) AND (Table1.Bar = @param2 OR Table1.Bar = @param3 OR Table1.Bar= @param4)

Can someone lend a helping hand?

TIA Stoop

Stoop wrote:

Hi

I don't know why, but I'm having the hardest time construction the predicate expression that will accomplish the following:

(Table1.Foo = @param1) AND (Table1.Bar = @param2 OR Table1.Bar = @param3 OR Table1.Bar= @param4)

Can someone lend a helping hand?

TIA Stoop

The rules of thumb are: (I've explained them abstractly in the filtering and sorting section, but that can be too abstract to grasp so here we go ) - the complete set of predicates is always a PredicateExpression, unless you have just 1 clause (Field=value for example). So you need a PredicateExpression object for your complete clause set. You can use a single Predicate object (constructed by the PredicateFactory class) as a filter, as a PredicateExpression is a derived class from Predicate. - Per () you need a PredicateExpression. You have 2 of those.

So your full filter can be seen as: completeFilter = filterA AND filterB

Let's construct filterA (If I remember correctly, you use VB.NET, so lets use VB.NET)

Dim filterA As IPredicateExpression = New PredicateExpression()
filterA.Add(PredicateFactory.CompareValue(Table1FieldIndex.Foo, ComparisonOperator.Equal, valueFoo))

That was easy. Now filterB. We can do this in 2 ways: use a CompareRange predicate or use a couple of CompareValue predicates. Lets first construct the filter with CompareValue

Dim filterB As IPredicateExpression = New PredicateExpression()
filterB.Add(PredicateFactory.CompareValue(Table1FieldIndex.Bar, ComparisonOperator.Equal, valueBar1))
filterB.AddWithOr(PredicateFactory.CompareValue(Table1FieldIndex.Bar, ComparisonOperator.Equal, valueBar2))
filterB.AddWithOr(PredicateFactory.CompareValue(Table1FieldIndex.Bar, ComparisonOperator.Equal, valueBar3))

Or a compare range: This will construct a (Table1.Bar IN (@param2, @param3, @param4)) construct

Dim filterB As IPredicateExpression = New PredicateExpression()
filterB.Add(PredicateFactory.CompareRange(Table1FieldIndex.Bar, valueBar1, valueBar2, valueBar3))

Now construct the complete filter

Dim completeFilter As IPredicateExpression = New PredicateExpression()
' you can add them directly in the constructor, but lets do it manually
completeFilter.Add(filterA)
completeFilter.AddWithAnd(filterB)

now, pass your completeFilter as the filter to use.

Thx!

Sorry I couldn't say that earlier, but been gone for the past week.

One more question - is there a property or method that allows you to see the actual SQL Query string after it's constructed? Sure would save a lot of time if one could actually see the SQL statement before it's passed on to the SQL datastore. Setting a break point at this point would let you "debug" your predicate expressions..

Thx again S

Stoop wrote:

Thx!

Sorry I couldn't say that earlier, but been gone for the past week.

One more question - is there a property or method that allows you to see the actual SQL Query string after it's constructed? Sure would save a lot of time if one could actually see the SQL statement before it's passed on to the SQL datastore. Setting a break point at this point would let you "debug" your predicate expressions..

Thx again S

If you're using SelfServicing, you could call the ToQueryText() method of the predicate expression and pass a ByRef integer parameter set to 0. The string returned is the where clause.

No - I'm using the Adapter method....

Stoop wrote:

No - I'm using the Adapter method....

That's tricky as the DataAccessAdapter class will fill in the persistence info itself.

You could add a public method to the DataAccessAdapter class which accepts the predicate expression and which calls the protected method InsertPersistenceInfoObjects(). Do this in a derived class of the DataAccessAdapter class.

Then your predicate expression has the persistence info and you can follow the trick I described with selfservicing.

You can also use the SqlServer profiler to check which queries are executed, or override the On<action> methods in a derived class of the DataAccessAdapter class and grab the query executed from the passed in query object.

SQL Profiler works fine.. Forgot about that tool, thx for reminding me!