SQL filter trick need not involve IN

[avanmeul]

Description

SQL filter trick triggers error about IN; but it shouldn't.

A common idiom in SQL is to give the user drop downs for filter criteria, then to use those in a query like this (wherein @myFilter is a filter criterion that came from such a drop down; or was left unpopulated, ergo no filtering on that criterion):

select * from dbo.myTable as t where @myFilter is null or @myFilter = t.Id

Unfortunately, using such a trick in SQLProvider triggered an error about IN (but the translation to SQL need not involve the use of an IN clause on the SQL side).

Repro steps

1. Modify a SQLProvider query by adding a where clause that tries to use logic like this:

where (myFilter.IsNone || t.ID = myFilter.Value)

2. This will trigger a run-time error involving the use of an IN clause in SQL.

Expected behavior

The filter trick shouldn't trigger an IN clause in SQL.

Actual behavior

Actual code snippet (slightly scrubbed):

let locId = locationId |> Option.ofNullable let exactDateEvents = query { for evt in ctx.Dbo.XXX do for sch in evt.dbo.XXX1 by ID do for loc in evt.dbo.XXX2 by ID do where (locId.IsNone || loc.Id = locId.Value) for req in evt.XXX3 by Id do select ({ evtId = evt.Id; eventId = maybeEmptyString evt.EventId; }) } |> Seq.toArray

Actual error:

System.Exception HResult=0x80131500 Message=Unsupported expression. Ensure all server-side objects won't have any .NET-operators/methods that can't be converted to SQL. The In and Not In operators only support the inline array syntax. (get_IsNone(null) OrElse (tupledArg.Item3.GetColumn("ID") == null.Value)) Source=FSharp.Core StackTrace: at Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.EvaluateQuotation(FSharpExpr e) at Microsoft.FSharp.Linq.QueryModule.EvalNonNestedInner(CanEliminate canElim, FSharpExpr queryProducingSequence) at Microsoft.FSharp.Linq.QueryModule.EvalNonNestedOuter(CanEliminate canElim, FSharpExpr tm) at Microsoft.FSharp.Linq.QueryModule.clo@1727-1.Microsoft-FSharp-Linq-ForwardDeclarations-IQueryMethods-Execute[a,b](FSharpExpr1 q) at [. . .] at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.<GetExecutor>b__9(Object instance, Object[] methodParameters) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.Execute(Object instance, Object[] arguments) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary2 arguments, CancellationToken cancellationToken)

Known workarounds

Don't use anything that will trigger IN clause errors (but that's too restrictive).

Possible workarounds include: subqueries, or fetching separate parts and post filtering, etc..

Related information

• SQL Server 2017
• Windows 10
• SQLProvider 1.1.54
• .Net 4.5.2

[Thorium]

Option type works if myFilter is defined inside the SQL context. Now that the myFilter Option<_> is defined outside the Linq (and it's not SqlEntity), we cannot be sure can it be translated to SQL or not: e.g. you could override IsNone and create some weird side-effects.

Luckily there is an easy way to go around it: transfer a primitive type like boolean. So if you add this to be your third line of code: let hasFilter = locId.IsNone and then use that in the Linq clause, it works fine. More info in best-practices chapter of querying documentation.

[avanmeul]

Thanks: I'll try that. What you say makes sense (it pays to think like a compiler). It's also easy to forget that the option type in F# isn't pure.

[Thorium]

This is what I was referring to: http://fsprojects.github.io/SQLProvider/core/querying.html#Using-booleans-and-simple-variables-from-outside-a-scope-in-where-clauses

[avanmeul]

That documentation makes it sound like this is merely a matter of readability; but the issue herein is way beyond that: it's about getting the semantics you expect.. The issue here involves subtleties of purity (and how that influences the SQL being generated) that people coming from a C# background wouldn't know to watch out for. I think this issue needs its own documentation use case: it's really that important.

[Thorium]

On some level we could evaluate the IsSome / IsNone for simple option type while generating the SQL-query, but there could be also side effects like "should this sleep 1 or n times?":

let myQuery x = 
   //some other query { ... } here
   System.Threading.Thread.Sleep(5000); x

let exactDateEvents =
   query {
      for evt in ctx.Dbo.XXX do
      where ((Some "x" |> Option.map myQuery).IsSome || evt.Id = 3)
      select ({ evtId = evt.Id; eventId = maybeEmptyString evt.EventId; })
    } |> Seq.toArray

Edit: PRs for documentation improvements gladly accepted. ;-)

[avanmeul]

I'd like to do a PR for documentation; but I'm not there yet (don't know how to do that yet).

[Thorium]

http://fsprojects.github.io/SQLProvider/core/contributing.html