Allow for custom query modification

[Kizzik]

This may seem like a bit of an odd one, but it could have a huge impact on performance for me, so may be useful for others.

For one of my entities I'm using a SQL full text index contains. As (to my knowledge at least) there's no way of specifying this using standard OData functions, I'm using a custom query string parameter.

Currently I effectively have something like this:

ctx.SomeEntity.Where(e => EF.Functions.Contains(e.Text, someText)).GetQuery(mapper, opts);

This gets me the results I'm after, but the issue with this is that the translated SQL query performs the full text search before everything else, resulting in a huge performance hit.

Now, I can do something like this:

var query = db.SomeEntity.GetQuery(mapper, opts);

if (!string.IsNullOrEmpty(someText)) {
    query = query.Where(e => EF.Functions.Contains(e.Text, someText));
}

However the issue is that pagination has already been applied, so it will only only be searching within the current page of results.

I haven't been able to find away to modify what's in the ODataQueryOptions to allow me to split things up (eg: apply ODataQueryOptions's select/expand/filter via one GetQuery, make my own modifications, then apply another ODataQueryOptions's take/skip/count via another GetQuery), nor am I aware of a way to extend the OData functions to allow me to specify usage of a full text index contains.

I'm not really sure what the best way of supplying a query modification would be (eg: Does it make sense to have some options in the QuerySettings?), or if there's a better way to achieve what I'm after, so hoping to start this discussion to see what approaches we can come up with.

[Kizzik]

I think I may have been thinking about this entirely the wrong way, and that a custom FilterBinder will do what I'm after. Talk about getting tunnel vision...

[BlaiseD]

I would have had the same tunnel vision. No harm in your other approach either - make the filter in GetQueryable a list of filters instead - supplying the additional filters though a new QuerySettings property.

[Kizzik]

After spending several hours bashing my head against this, I think the initial approach may actually be what's required. I've hit nothing but dead ends playing with the FilterBinder as binding to EF.Functions.Contains internally requires a ColumnExpression (which is abstract). The expression at the time of execution of the FilterBinder is referencing my DTO, which knows nothing of being a DB entity so has no ColumnExpression to use, so unless there's some way to obtain the mapped model's ColumnExpression (even if it's a hard-coded mapping as a POC) I don't think extending the OData capabilities would be possible. I'm a bit of a novice when it comes to Expressions and the internals of AutoMapper so I'm not sure if it's feasible to obtain or generate an appropriate expression at that time - I'm very open to suggestions if you have any bright ideas!

Failing that, if I can find some extra time I'll see if I can get the base code for this up and running to make a PR for a change. My initial thought would be to apply the change when generating the filter expression here:

Expression<Func<TModel, bool>> filter = options.ToFilterExpression<TModel>(
                querySettings?.ODataSettings?.HandleNullPropagation ?? HandleNullPropagationOption.False,
                querySettings?.ODataSettings?.TimeZone);

As otherwise the filter for the count in ApplyOptions could potentially be off.

[BlaiseD]

Are you saying you have the following?

            Expression<Func<Entity, bool>> filter = e => EF.Functions.Contains(e.Text, someText);
            Expression<Func<DTO, bool>> filterDto = e => EF.Functions.Contains(e.Text, someText)
            Expression<Func<Entity, bool>> filterMapped = mapper.MapExpression<Expression<Func<Entity, bool>>>(filterDto);

            query = query.Where(filter);//works
            
            query = query.Where(filterMapped);//fails

If the first one doesn't work you'll need to first get that working. If just the second fails best to post a repro.

[Kizzik]

Apologies for the delay - priorities shifted so I've been moved onto something else for a little bit. I'll follow up once I've had a chance to repro outside my own solution.