Conditional Where clause

[Jmaharman]

I was trying to add a conditional where clause in the CE but hitting dead ends. If I was using SqlKata directly I'd be able to wrap it in an if statement.

if (condition == true) {
    query.Where("Field", "Value")
}

I tried to do similar in the CE but the compiler tells me:

A custom operation may not be used in conjunction with 'use', 'try/with', 'try/finally', 'if/then/else' or 'match' operators within this computation expression.

I started to play around with adding a new customOperator that received a type which included the condition, but I ran into compiler errors I don't understand and quickly realised how very little I know about how computational expressions.

Firstly, is there a way of doing this with the library as it is today? If not, did you have an idea of how this might be possible? I'm happy to give it a go if pointed in the right direction.

[JordanMarr]

I can see how it would be useful to be able to do conditional where statements, especially for scenarios where there is a query spec passed in with optional filter parameters. This would definitely be a worthwhile feature to implement. 🤔

For an immediate workaround, I think the easiest option would be to conditionally create two versions of the same query:

use ctx = openContext()
let query = 
    if filterByLastName then
        select {
            for e in employeesTable do
            where (e.LastName = "Smith")
        }
    else
        select {
            for e in employeesTable do
            select e
        }

let! results = query |> ctx.ReadAsync HydraReader.Read

Another (more manual) option would be to conditionally modify the underlying SqlKata.Query:

use ctx = openContext()
let query = 
    select {
        for e in employeesTable do
        select e
    }

// Manually edit SqlKata.Query
let kataQuery = query.ToKataQuery()
if filterByLastName then 
    kataQuery.Where("LastName", "=", "Smith") |> ignore

// Convert back to SelectQuery and run
let results = 
    kataQuery
    |> SelectQuery<dbo.Employees> 
    |> ctx.ReadAsync HydraReader.Read

If your conditional filtering is extensive enough, the 2nd approach may be worth it for more fine grained control of the filtering criteria.

[JordanMarr]

As for implementing conditional queries, I can imagine a few options off the top of my head:

1) Add a whereIf operation to the builder that takes a where expression as well as an if conditional bool:

type EmployeeFilters = {
    FirstName: string option
    LastName: string option
}

let getEmployees(filters: EmployeeFilters) =
    use ctx = openContext()
    select {
        for e in employeesTable do
        whereIf (e.LastName = filters.LastName.Value, filters.LastName.IsSome)
        whereIf (e.FirstName = filters.FirstName.Value, filters.FirstName.IsSome)
    }
    |> ctx.ReadAsync HydraReader.Read

This approach might also need to handle things like andWhereIf and orWhereIf.

2) Create a set of query helper functions for dynamically modifying the query:

let getEmployees(filters: EmployeeFilters) =
    let query = 
        select {
            for e in employeesTable do
            select e
        }

    let query = 
        if filters.LastName.IsSome then
            query |> ConditionalFilters.where (fun e -> e.LastName = filters.LastName.Value)

        if filters.FirstName.IsSome then
            query |> ConditionalFilters.where (fun e -> e.FirstName = filters.FirstName.Value)

    query |> ctx.ReadAsync HydraReader.Read

This might also need to handle andWhere / orWhere. And then I suppose there would also be a need for conditionally setting the orderBy per the user's sort settings. 🤔

[Jmaharman]

Good idea with the workarounds, that'll get me by for now.

With regards to implementing whereIf, I've tried a few things. Very similar to what you mention above:

  [<CustomOperation("whereIf", MaintainsVariableSpace = true)>]
  member this.WhereCondition (state:QuerySource<'T>, [<ProjectionParameter>] whereExpressionAndCondition) = 
      let query = state |> getQueryOrDefault
      if snd whereExpressionAndCondition then
          this.Where(state, (fst whereExpressionAndCondition))
      else
          QuerySource<'T, Query>(query, state.TableMappings)

Using it like so:

select {
    for o in orderHeaderTable do
    whereIf ((o.OnlineOrderFlag = true), true)
    select (o)
}

At first had the following error message:

Type constraint mismatch. The type 
    'SalesOrderHeader -> bool * bool'    
is not compatible with type
    'Expression<Func<SalesOrderHeader,bool>> * bool'

When I realised F# couldn't automatically turn the where code into an expression with the tuple, so I made a helper in an attempt to build the where expression elsewhere.

// Expression helper
type Spec =
    static member Where<'T>(e: Expression<Func<'T, bool>>) = e
// Create the expression separately
let whereExp = Spec.Where<SalesOrderHeader>((fun o -> o.AccountNumber = Some "Test"))

let query =
    select {
        for o in orderHeaderTable do
        whereIf (whereExp, true)
        select (o)
    }

This however gives me the following error:

Type constraint mismatch. The type 
    ''a -> Expression<Func<SalesOrderHeader,bool>> * bool'    
is not compatible with type
    'Expression<Func<SalesOrderHeader,bool>> * bool'

I was then curious as to whether I could use the whereExp directly with the already existing "where" function

where (whereExp)

That gives the error:

This expression was expected to have type
    'bool'    
but here has type
    'Expression<Func<SalesOrderHeader,bool>>'

I think this is the biggest clue as to why it doesn't work with how I am using it, but I just don't know enough F# yet to know why \o_o/

If you have any hints as to what I might need to look into / read up on to understand this I'd be very grateful.

[JordanMarr]

There’s not much material available on creating CEs so most of my success has just been experimentation and trial and error TBH. Sometimes you get lucky and find an old Tomas Petricek blog post with some useful tidbits.

[JordanMarr]

Try this:

    /// Sets the WHERE condition if applyFilter is true
    [<CustomOperation("whereIf", MaintainsVariableSpace = true)>]
    member this.WhereIf (state: QuerySource<'T>, [<ProjectionParameter>] whereExpression, applyFilter) = 
        let query = state |> getQueryOrDefault
        let where = LinqExpressionVisitors.visitWhere<'T> whereExpression (FQ.fullyQualifyColumn state.TableMappings)
        if applyFilter
        then QuerySource<'T, Query>(query.Where(fun w -> where), state.TableMappings)
        else QuerySource<'T, Query>(query, state.TableMappings)

The tricky part is that it converts it to a curried function, so you have to call it like this:

    select {
        for a in addressTable do
        whereIf (a.AddressLine2 <> None) (1 = 1)
    }

[Jmaharman]

Ah, I did try multiple parameters but it was probably the currying that threw me.

Thanks, I’ll have a play around and we can see how it feels to use.

On 11 Sep 2021, at 18:54, Jordan Marr @.***> wrote:

 Try this:

/// Sets the WHERE condition if applyFilter is true
[<CustomOperation("whereIf", MaintainsVariableSpace = true)>]
member this.WhereIf (state: QuerySource<'T>, [<ProjectionParameter>] whereExpression: Expression<Func<'T, bool>>, applyFilter: bool) = 
    let query = state |> getQueryOrDefault
    let where = LinqExpressionVisitors.visitWhere<'T> whereExpression (FQ.fullyQualifyColumn state.TableMappings)
    if applyFilter
    then QuerySource<'T, Query>(query.Where(fun w -> where), state.TableMappings)
    else QuerySource<'T, Query>(query, state.TableMappings)

The tricky part is that it converts it to a curried function, so you have to call it like this:

    test "Where If" {
        let query = 
            select {
                for a in addressTable do
                whereIf (a.AddressLine2 <> None) (1 = 1)
            }

        query.ToKataQuery() |> toSql |> printfn "%s"
    }

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or unsubscribe. Triage notifications on the go with GitHub Mobile for iOS or Android.

[JordanMarr]

If I put out a new release that makes a few internal modules public, it would be really easy for you to extend SelectExpressionBuilder like this:

let getQueryOrDefault (state: QuerySource<'Result>) = 
    match state with
    | :? QuerySource<'Result, SqlKata.Query> as qs -> qs.Query
    | _ -> SqlKata.Query()   

type SelectExpressionBuilder<'T> with

    /// Sets the WHERE condition if applyFilter is true
    [<CustomOperation("whereIf", MaintainsVariableSpace = true)>]
    member this.WhereIf (state: QuerySource<'T>, [<ProjectionParameter>] whereExpression, applyFilter) = 
        let query = state |> getQueryOrDefault
        let where = LinqExpressionVisitors.visitWhere<'T> whereExpression (FQ.fullyQualifyColumn state.TableMappings)
        if applyFilter
        then QuerySource<'T, SqlKata.Query>(query.Where(fun w -> where), state.TableMappings)
        else QuerySource<'T, SqlKata.Query>(query, state.TableMappings)

(Currently the LinqExpressionVisitors module is internal.)

That would make it much easier to experiment with potential changes to the API in a real project, because I suspect that some other things will come to light over time.

[Jmaharman]

I don't necessarily need access to the LinqExpressionVisitors right now, as I can call it via this.Where. Perhaps we carry on as we are, rather than exposing something that you will probably want to bring back internally in the future.

I'll continue and see where I get, then post back here with how things have gone and whether I do need access or not.

[Jmaharman]

I've not been able to get back to where I was using this, but my initial feedback is that reversing the order of parameters seems easier to read because the boolean value is often shorter than the where clause itself, which then allows you to skip to the next line if you trying to think through the query being built.

I definitely see no need to rush this in though, it would be good to use it in the field more first.