Linq Query Support

[albert-du]

It would be nice to be able to query a database using linq without directly writing sql.

The following syntax would be possible:

open FSharp.CosmosDb
open Newtonsoft.Json

type User = 
  { [<JsonProperty(PropertyName="id")>]
    Id: string
    Name: string }

let users =
    Cosmos.fromConnectionString "..."
    |> Cosmos.database "UserDb"
    |> Cosmos.container "UserContainer"
    |> Cosmos.linq (fun users ->
        query {
            for user in users do
            where (user.Name = "Aaron")
        })
    |> Cosmos.execAsync<User>

I've been experimenting with extended query builders that can replace F# functions with .Net BCL methods for the purposes of querying cosmos db

open Microsoft.Azure.Cosmos
open Microsoft.Azure.Cosmos.Linq
open Newtonsoft.Json
open System
open System.Linq
open FSharp.Linq
open FSharp.Quotations
open FSharp.Quotations.ExprShape
open FSharp.Quotations.DerivedPatterns

let rec replace expr =
    // replaces 'abs' with 'System.Math.Abs' and 'acos' with 'System.Math.Acos'
    match expr with 
    | SpecificCall <@@ abs @@> (_, [typ], args) ->
        let e = replace args.Head
        if typ = typeof<int8> then
            <@@ (Math.Abs : int8 -> int8) %%e @@>
        elif typ = typeof<int16> then
            <@@ (Math.Abs : int16 -> int16) %%e @@>
        elif typ = typeof<int32> then
            <@@ (Math.Abs : int32 -> int32) %%e @@>
        elif typ = typeof<int64> then
            <@@ (Math.Abs : int64 -> int64) %%e @@>
        elif typ = typeof<float32> then
            <@@ (Math.Abs : float32 -> float32) %%e @@>
        elif typ = typeof<float> then
            <@@ (Math.Abs : float -> float) %%e @@>
        elif typ = typeof<decimal> then
            <@@ (Math.Abs : decimal -> decimal) %%e @@>
        else 
            failwith $"Invalid argument type for translation of 'abs': {typ.FullName}"
    | SpecificCall <@@ acos @@> (_, [typ], args) ->
        let e = replace args.Head
        <@@ (Math.Acos : float -> float) %%e @@>
    | ShapeVar v -> Expr.Var v
    | ShapeLambda(v, expr) -> Expr.Lambda(v, replace expr)
    | ShapeCombination(o, args) -> 
        RebuildShapeCombination(o, List.map replace args)

type CosmosQueryBuilder() =
    inherit QueryBuilder()

    member _.Run(e: Expr<QuerySource<'a, IQueryable>>) =
        let r = Expr.Cast<Linq.QuerySource<'a, System.Linq.IQueryable>>(replace e)
        base.Run r

let cosmosQuery = CosmosQueryBuilder()

(task {
    use cosmosClient = new CosmosClient("...")
    let! resp = cosmosClient.CreateDatabaseIfNotExistsAsync "TestDB"
    let database = resp.Database
    let! resp = database.CreateContainerIfNotExistsAsync("TestContainer", "/id")
    let container = resp.Container

    let q = 
        // Utilization, this doesn't work with the normal 'query' ce
        cosmosQuery {
            for p in container.GetItemLinqQueryable<Person>() do
            let o = abs p.Number
            where (o > 2)
            select ((float o) + 1.2)
        }
    use setIterator = q.ToFeedIterator<float>()

    while setIterator.HasMoreResults do
        let! items = setIterator.ReadNextAsync()
        for item in items do
            printfn $"%A{item}"
}).Wait()

[bartelink]

I know it's only an example, but can you clarify what sort of stuff you're intending to handle in your query support ?

I've not read or stretched it but I believe Microsoft.Azure.Cosmos has prerry complete LINQ support - are you intending to do some fixups in front of that, or replace the the whole engine?

If you're leaning towards the replacing the whole thing, it may also be worthwhile to peruse https://github.com/fsprojects/FSharp.AWS.DynamoDB (obviously it has a different query syntax, but there are lots of aspects of it which have been implemented very cleanly)

[albert-du]

Currently I'm looking to support built in F# functions by replacing them with the BCL equivalents

Math operations are supported normally but not the F# versions

Math functions: Supports translation from .NET Abs, Acos, Asin, Atan, Ceiling, Cos, Exp, Floor, Log, Log10, Pow, Round, Sign, Sin, Sqrt, Tan, and Truncate to the equivalent built-in mathematical functions. https://docs.microsoft.com/en-us/azure/cosmos-db/sql/sql-query-linq-to-sql

For example, trying to use the "abs" function in a query ce with Cosmos fails, but the equivalent "System.Math.Abs" method works.

This query builder is just to show extending the operations available by mapping F# functions to BCL methods.

[aaronpowell]

I'll admit that I'm somewhat torn here - I like the idea of having a strongly-typed query engine, such as you get with LINQ, so that you can have more confidence in the queries that you write, but at the same time I ponder it relative to the analyzer part of this project (and which was one of the key motivations in building it). I want to explore a bit more how they play together, can we get better analysis of the query-to-generate by using LINQ, to maybe suggest where your projections are not valid for the database you're working with? I'm not sure, but it's something to explore.

[albert-du]

I believe Linq could be leveraged to provide greater insight with the analyzer. For instance, the Cosmos Linq provider allows for translation of strongly typed queries directly into sql which could be a basis for providing autocomplete suggestions or additional code linting.

[aaronpowell]

Yes, it does allow for strongly typed query creation because you're working off the in/out types, but I have to look at the analyzer and see if the type information is available - Last time I looked you were really only working with strings (as you're working on the tokens in the AST), but that might be outdated.