Hello,

I noticed that dbfunctioncalls should allow me to use the substring predicate in my query.

What I would like to accomplish the following:

SELECT * FROM Customers WHERE SUBSTRING(Customers.SecurityMask, Foo1, 1) & Foo2 > 0

Where Foo1 & Foo2 are variables that are passed in.

Any ideas on how I would accomplish this using DBFunctionCalls? I would hate to have to take the time to create my own predicates for this.

Thanks for the help.

V2 was well worth the upgrade from V1, I love supporting you guys.

Do you have any code you have tried. Sometimes this will help with understanding more about what you are trying to do? Also are you using Adapter or Selfservicing?

I tried to write the DBFunctionCall expression using substring, however, I wasn't sure how to bring in the & operator and the > comparison.

long lRoleKey = Security.GetRoleKey(); //This line gets the role of the current user.
int iFoo1 = (lRoleKey/8)++;
byte byFoo2 = Math.Pow(2, (lRoleKey % 8 - 1));

DataAccessAdapter adapter = new DataAccessAdapter();
EntityCollection<CustomerEntity> ecCustomer = new EntityCollection<CustomerEntity>(new CustomerEntityFactory());
RelationPredicateBucket filter = new RelationPredicateBucket();
/*This is where I get stuck*/
filter.PredicateExpress.Add((DBFunctionCall("SUBSTRING", new object[]{CustomerFields.SecurityMask, iFoo1, 1}) & byFoo2) > 0);
/* I know the above code doesn't even compile*/

adapter.FetchEntityCollection(ecCustomer, filter);

The SQL code I am trying to end up with is:

SELECT * FROM Customer WHERE SUBSTRING(Customer.SecurityMask, Foo1, 1) & Foo2 > 0

SecurityMask is a binary 10 field, I hope that makes more sense.

SUBSTRING requires a non-parameterizable parameter, which is currently (v2.0) not possible with DbFunctionCall. We've planned to make this possible in v2.1.

A way around this is by subclassing DbFunctionCall and doing the emit for SUBSTRING yourself, please peek into the runtime sourcecode, in DbFunctionCall.cs to see how it emits itself.