Hello, Could you please advice how should I properly deal with a predicate of the type FieldLikePredicate with the property PatternIsRegEx set to true, to achieve proper regexp syntax in a sql query? I mean proper for the designated sql dialect - eg. WHERE ... LIKE REGEXP ... for MySQL or WHERE ... REGEXP_LIKE for Oracle.

Please check the example posted here: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=6373&StartAtMessage=0&#35141

Be aware that the regexp filtering on the like predicate is only for in-memory filtering, so what you're trying to do isn't possible by default. If you want to get the specific like predicates being generated into SQL predicates, please create your own like predicate class, by using the FieldLikePredicate class from the runtime sourcecode and alter in your variant (e.g. the RegExpFieldLikePredicate class) the ToQueryText method to emit the proper SQL.

Then in your code use the class you made instead of the default one.

Thanks! For a moment I thought that I can define a template of the sql like regexp query, dedicated for the db I use, somewhere in LLBLGen parameters. Now I see how I can do it (with the ToQueryText method).

BTW if I need to filter a related collection, may I have a hint how to apply a FieldLikePredicate filter to the related collection's DefaultView?

etk wrote:

BTW if I need to filter a related collection, may I have a hint how to apply a FieldLikePredicate filter to the related collection's DefaultView?

If you have one entity (say order) and want to filter their OrderDetails collection, then use the same code posted posted by Walaa over order.OrderDetails collection.

Hello daelmo, That's clear but I thought about such a case: Eg. I have Orders and OrderDetails collections, Orders collection is the data source for a grid and I want to filter the related OrderDetails using regexp for a selected field of OrderDetails. I have no idea how to apply permanent regexp filter for all the OrderDetails collection through the Orders.

I'm not sure I understand you 100%.

Do you want to filter Orders on fields of their related OrderDetails? For example: Only fetch orders having an orderDetail of a specific product.

Or Do you want to fetch Orders and then only fetch a subset of their OrderDetails based on some filter?

I have no idea how to apply permanent regexp filter for all the OrderDetails collection through the Orders.

Do you mean by "permenant", is applied by default whenever ou prefetchPath OrderDetails? And would this also mean that you wont be able to fetch the filtered out OrderedDetails?

I'd like first to fetch all the Orders and their related OrderDetails from a database. Then apply a regexp filter to the OrderDetails' DefaultView and filter them in-memory. The Orders should stay untouched. The regexp filter is entered by user and as long as he keeps it ("permanently") I want the filter to be applied, even if I refetch the data. When I refetch the Orders and related OrderDetails, I want to refetch them all but have it filtered in-memory just after the fetch.

The idea behind is to allow the user to filter the OrderDetails inside the application and avoid frequent direct db operations.

Good. So what's the problem if you want to apply this regex filter in-memory?

Normally you can use a Regex with Linq (i.e. Linq2Objects) to filter an in memory collection. Did you try this out?

Simply I don't know how to apply the regexp filter for the OrderDetails through Orders collection. OrderDetails is the related collection and I don't know how to cast its DefaultView to EntityView<OrderDetailsEntity>; something like:

EntityView<OrderDetailsEntity> orderDetailsView = orders./*cast to OrderDetails  collection*/.DefaultView;

I haven't tried a Linq query yet but I will.

etk wrote:

Simply I don't know how to apply the regexp filter for the OrderDetails through Orders collection. OrderDetails is the related collection and I don't know how to cast its DefaultView to EntityView<OrderDetailsEntity>; something like:

EntityView<OrderDetailsEntity> orderDetailsView = orders./*cast to OrderDetails  collection*/.DefaultView;

You can't do that as the OrderDetails collection is only accessible through each individual order entity. You can do it in a foreach, obtaining IEntityView2 objects, but how would you use that in your main dataSource that is bound to the grid?

I wouldn't but I thought there is a way to do it - your tool is so versatile that I supposed it is possible, only I don't see the right approach. I've created a custom class inheriting from LikePredicate, as you earlier suggested, which implements the native SQL regexp query. Works only takes few seconds. Thanks for all the advices!

etk wrote:

I wouldn't but I thought there is a way to do it - your tool is so versatile that I supposed it is possible, only I don't see the right approach.

It's very versatile, but what you are asking is a challenge, even with Linq2Objects is difficult to achieve. It would be easier if you project the results to some custom DTO class to bound to your grid, but that would means more coding work for you.

etk wrote:

I've created a custom class inheriting from LikePredicate, as you earlier suggested, which implements the native SQL regexp query. Works only takes few seconds. Thanks for all the advices!

If the hit is not significant, I think that is the way to go here. Thanks for the feedbak.