Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Linq Query Support #65

Open
albert-du opened this issue Jul 15, 2022 · 5 comments
Open

Linq Query Support #65

albert-du opened this issue Jul 15, 2022 · 5 comments

Comments

@albert-du
Copy link

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
Copy link
Contributor

bartelink commented Jul 15, 2022

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
Copy link
Author

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
Copy link
Owner

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
Copy link
Author

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
Copy link
Owner

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

3 participants