How to recover a table from a database context via reflection in SqlProvider?

10 views

Skip to first unread message

Yuri Albuquerque

unread,

Jan 9, 2017, 9:23:16 AM1/9/17

to F# Discussions

I'm trying to have a powerful client-side querying API, where the client can specify which table to run the query in and the conditions to the query. Of course that's no replacement to full-blown LINQ or SQL, it's just so the JS client can make complex queries on tables, without joins.

Now, most of it is done, as below. The client sends me a JSON-serialized Predicate (which I deserialize with JSON.NET), and I can easily compose it into a query.

module Query =
    open System.Linq
    open FSharp.Data.Sql.Common
    open FSharpComposableQuery

    type Predicate =
    | All
    | Greater of string * System.IComparable
    | GreaterEq of string * System.IComparable
    | Lesser of string * System.IComparable
    | LesserEq of string * System.IComparable
    | Equal of string * obj
    | Diff of string * obj
    | And of Predicate * Predicate
    | Or of Predicate * Predicate
    | Not of Predicate

    let satisfies (table : IQueryable<SqlEntity>) =
        <@ fun p -> query {
           for c in table do
           if p c
           then yield c
          } @>

    let rec eval t =
        match t with
            | All                   -> <@ fun _ -> true @>
            | Greater (column, n)   -> <@ fun (c : SqlEntity) -> c.GetColumn column > n @>
            | GreaterEq (column, n) -> <@ fun c -> c.GetColumn column >= n @>
            | Lesser (column, n)    -> <@ fun c -> c.GetColumn column < n @>
            | LesserEq (column, n)  -> <@ fun c -> c.GetColumn column <= n @>
            | Equal (column, n)     -> <@ fun c -> c.GetColumn column = n @>
            | Diff (column, n)      -> <@ fun c -> c.GetColumn column <> n @>
            | And (p1, p2)          -> <@ fun c -> (%eval p1) c && (%eval p2) c @>
            | Or (p1, p2)           -> <@ fun c -> (%eval p1) c || (%eval p2) c @>
            | Not p                 -> <@ fun c -> not((%eval p) c) @>

    let predicate = Or (Equal ("myColumn", "myValue"), Equal ("myColumn", "myOtherValue"))
    let t = query {
        for c in <someDataProvidedTable> do
        select (c :> SqlEntity)
    }
    let result = query { yield! (%satisfies t) (%eval predicate) } |> Seq.toArray

module DataLayer =
    open FSharp.Data.Sql
    open FSharp.Data.Sql.Common
    let [<Literal>] ConnectionString = "Data Source=" + __SOURCE_DIRECTORY__ + @"/db.sqlite3;Version=3"
    type Sql = SqlDataProvider<
                ConnectionString = ConnectionString,
                DatabaseVendor = Common.DatabaseProviderTypes.SQLITE,
                IndividualsAmount = 1000,
                UseOptionTypes = true >
    let ctx = Sql.GetDataContext()
    let db = ctx.Maint

All that is left to parameterize is... the table. DataLayer.db has table objects equivalent to my tables, and my functions accept these table objects. But I want the client to be able to send me the table as a string, and I inspect DataLayer.db to retrieve a table from it.

Now, I know I could solve this by having a dictionary from string to table object. But that would make the whole construct less usable (we're generating the schema and the client from an external DSL). Of course, I have tried DataLayer.db.GetType().InvokeMethod, but I get the following exception no matter what the parameters are.

System.MissingMethodException: Method 'FSharp.Data.Sql.Runtime.SqlDataContext.FsmIssue' not found.
  at System.RuntimeType.InvokeMember (System.String name, System.Reflection.BindingFlags bindingFlags, System.Reflection.Binder binder, System.Object target, System.Object[] providedArgs, System.Reflection.ParameterModifier[] modifiers, System.Globalization.CultureInfo culture, System.String[] namedParams) [0x008a0] in <dca3b561b8ad4f9fb10141d81b39ff45>:0 
  at System.Type.InvokeMember (System.String name, System.Reflection.BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object target, System.Object[] args) [0x00000] in <dca3b561b8ad4f9fb10141d81b39ff45>:0 
  at <StartupCode$FSI_0062>.$FSI_0062.main@ () [0x0001a] in <906a7cff96cb466bbad0babb35abf8de>:0 
  at (wrapper managed-to-native) System.Reflection.MonoMethod:InternalInvoke (System.Reflection.MonoMethod,object,object[],System.Exception&)
  at System.Reflection.MonoMethod.Invoke (System.Object obj, System.Reflection.BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) [0x00038] in <dca3b561b8ad4f9fb10141d81b39ff45>:0 
Stopped due to error