SQLProvider


Querying

1: 
2: 
3: 
4: 
5: 
6: 
7: 
type sql  = SqlDataProvider<
                Common.DatabaseProviderTypes.SQLITE,
                connectionString,
                ResolutionPath = resolutionPath, 
                CaseSensitivityChange = Common.CaseSensitivityChange.ORIGINAL
            >
let ctx = sql.GetDataContext()

SQLProvider leverages F#'s query {} expression syntax to perform queries against the database. Though many are supported, not all LINQ expressions are.

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
let example =
    query {
        for order in ctx.Main.Orders do
        where (order.Freight > 0m)
        sortBy (order.ShipPostalCode)
        skip 3
        take 4
        select (order)
    }

let test = example |> Seq.toArray |> Array.map(fun i -> i.ColumnValues |> Map.ofSeq)

let item =
    query {
        for order in ctx.Main.Orders do
        where (order.Freight > 0m)
        head
    }

Or async versions:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
let exampleAsync =
    async {
        let! res =
            query {
                for order in ctx.Main.Orders do
                where (order.Freight > 0m)
                select (order)
            } |> Seq.executeQueryAsync
        return res
    } |> Async.StartAsTask

let itemAsync =
    async {
        let! item =
            query {
                for order in ctx.Main.Orders do
                where (order.Freight > 0m)
            } |> Seq.headAsync
        return item
    } |> Async.StartAsTask

Supported Query Expression Keywords

Keyword

Supported

Notes

.Contains()

X

open System.Linq, in where, SQL IN-clause, nested query

.Concat()

X

open System.Linq, SQL UNION ALL-clause

.Union()

X

open System.Linq, SQL UNION-clause

all

X

averageBy

X

averageByNullable

X

contains

X

count

X

distinct

X

exactlyOne

X

exactlyOneOrDefault

X

exists

X

find

X

groupBy

x

Initially only very simple groupBy (and having) is supported: On single-table with single-key-column and direct aggregates like .Count() or direct parameter calls like .Sum(fun entity -> entity.UnitPrice).

groupJoin

groupValBy

head

X

headOrDefault

X

if

X

join

X

last

lastOrDefault

leftOuterJoin

let

x

...but not using tmp variables in where-clauses

maxBy

X

maxByNullable

X

minBy

X

minByNullable

X

nth

X

select

X

skip

X

skipWhile

sortBy

X

sortByDescending

X

sortByNullable

X

sortByNullableDescending

X

sumBy

X

sumByNullable

X

take

X

takeWhile

thenBy

X

thenByDescending

X

thenByNullable

X

thenByNullableDescending

X

where

x

Server side variables must be plain without .NET operations, so you can't say where (col.Days(+1)>2)

To debug your SQL-clauses you can add listener for your logging framework to SqlQueryEvent:

1: 
FSharp.Data.Sql.Common.QueryEvents.SqlQueryEvent |> Event.add (printfn "Executing SQL: %s")

By default query { ... } is IQueryable<T> which is lazy. To execute the query you have to do Seq.toList, Seq.toArray, or some corresponding operation. If you don't do that but just continue inside another query { ... } or use System.Linq .Where(...) etc, that will still be combined to the same SQL-query.

There are some limitation of complexity of your queries, but for example this is still ok and will give you very simple select-clause:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
let randomBoolean = 
    let r = System.Random()
    fun () -> r.NextDouble() > 0.5
let c1 = randomBoolean()
let c2 = randomBoolean()
let c3 = randomBoolean()

let sample =
    query {
        for order in ctx.Main.Orders do
        where ((c1 || order.Freight > 0m) && c2)
        let x = "Region: " + order.ShipAddress
        select (x, if c3 then order.ShipCountry else order.ShipRegion)
    } |> Seq.toArray

It can be for example (but it can also leave [Freight]-condition away and select ShipRegion instead of ShipAddress, depending on your randon values):

1: 
2: 
3: 
4: 
5: 
    SELECT 
        [_arg2].[ShipAddress] as 'ShipAddress',
        [_arg2].[ShipCountry] as 'ShipCountry' 
    FROM main.Orders as [_arg2] 
    WHERE (([_arg2].[Freight]> @param1)) 

Expressions

These operators perform no specific function in the code itself, rather they are placeholders replaced by their database-specific server-side operations. Their utility is in forcing the compiler to check against the correct types.

1: 
let bergs = ctx.Main.Customers.Individuals.BERGS

Operators

You can find some custom operators using FSharp.Data.Sql:

  • |=| (In set)
  • |<>| (Not in set)
  • =% (Like)
  • <>% (Not like)
  • !! (Left join)
Multiple items
type LiteralAttribute =
  inherit Attribute
  new : unit -> LiteralAttribute

Full name: Microsoft.FSharp.Core.LiteralAttribute

--------------------
new : unit -> LiteralAttribute
val connectionString : string

Full name: Querying.connectionString
val resolutionPath : string

Full name: Querying.resolutionPath
Multiple items
namespace FSharp

--------------------
namespace Microsoft.FSharp
Multiple items
namespace FSharp.Data

--------------------
namespace Microsoft.FSharp.Data
namespace FSharp.Data.Sql
type sql = SqlDataProvider<...>

Full name: Querying.sql
type SqlDataProvider

Full name: FSharp.Data.Sql.SqlDataProvider


<summary>Typed representation of a database</summary>
                    <param name='ConnectionString'>The connection string for the SQL database</param>
                    <param name='ConnectionStringName'>The connection string name to select from a configuration file</param>
                    <param name='DatabaseVendor'> The target database vendor</param>
                    <param name='IndividualsAmount'>The amount of sample entities to project into the type system for each SQL entity type. Default 1000.</param>
                    <param name='UseOptionTypes'>If true, F# option types will be used in place of nullable database columns. If false, you will always receive the default value of the column's type even if it is null in the database.</param>
                    <param name='ResolutionPath'>The location to look for dynamically loaded assemblies containing database vendor specific connections and custom types.</param>
                    <param name='Owner'>The owner of the schema for this provider to resolve (Oracle Only)</param>
                    <param name='CaseSensitivityChange'>Should we do ToUpper or ToLower when generating table names?</param>
                    <param name='TableNames'>Comma separated table names list to limit a number of tables in big instances. The names can have '%' sign to handle it as in the 'LIKE' query (Oracle and MSSQL Only)</param>
                    <param name='OdbcQuote'>Odbc quote characters: Quote characters for the table and column names: `alias`, [alias]</param>
                    <param name='SQLiteLibrary'>Use System.Data.SQLite or Mono.Data.SQLite or select automatically (SQLite only)</param>
                    
namespace FSharp.Data.Sql.Common
type DatabaseProviderTypes =
  | MSSQLSERVER = 0
  | SQLITE = 1
  | POSTGRESQL = 2
  | MYSQL = 3
  | ORACLE = 4
  | MSACCESS = 5
  | ODBC = 6

Full name: FSharp.Data.Sql.Common.DatabaseProviderTypes
Common.DatabaseProviderTypes.SQLITE: Common.DatabaseProviderTypes = 1
type CaseSensitivityChange =
  | ORIGINAL = 0
  | TOUPPER = 1
  | TOLOWER = 2

Full name: FSharp.Data.Sql.Common.CaseSensitivityChange
Common.CaseSensitivityChange.ORIGINAL: Common.CaseSensitivityChange = 0
val ctx : SqlDataProvider<...>.dataContext

Full name: Querying.ctx
SqlDataProvider<...>.GetDataContext() : SqlDataProvider<...>.dataContext


<summary>Returns an instance of the SQL Provider using the static parameters</summary>

SqlDataProvider<...>.GetDataContext(transactionOptions: Transactions.TransactionOptions) : SqlDataProvider<...>.dataContext


<summary>Returns an instance of the SQL Provider</summary>
                              <param name='transactionOptions'>TransactionOptions for the transaction created on SubmitChanges.</param>

SqlDataProvider<...>.GetDataContext(connectionString: string) : SqlDataProvider<...>.dataContext


<summary>Returns an instance of the SQL Provider</summary>
                              <param name='connectionString'>The database connection string</param>

SqlDataProvider<...>.GetDataContext(connectionString: string, transactionOptions: Transactions.TransactionOptions) : SqlDataProvider<...>.dataContext


<summary>Returns an instance of the SQL Provider</summary>
                              <param name='connectionString'>The database connection string</param>
                              <param name='transactionOptions'>TransactionOptions for the transaction created on SubmitChanges.</param>

SqlDataProvider<...>.GetDataContext(connectionString: string, resolutionPath: string) : SqlDataProvider<...>.dataContext


<summary>Returns an instance of the SQL Provider</summary>
                              <param name='connectionString'>The database connection string</param>
                              <param name='resolutionPath'>The location to look for dynamically loaded assemblies containing database vendor specific connections and custom types</param>

SqlDataProvider<...>.GetDataContext(connectionString: string, resolutionPath: string, transactionOptions: Transactions.TransactionOptions) : SqlDataProvider<...>.dataContext


<summary>Returns an instance of the SQL Provider</summary>
                              <param name='connectionString'>The database connection string</param>
                              <param name='resolutionPath'>The location to look for dynamically loaded assemblies containing database vendor specific connections and custom types</param>
                              <param name='transactionOptions'>TransactionOptions for the transaction created on SubmitChanges.</param>
val example : System.Linq.IQueryable<SqlDataProvider<...>.dataContext.main.OrdersEntity>

Full name: Querying.example
val query : Linq.QueryBuilder

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.query
val order : SqlDataProvider<...>.dataContext.main.OrdersEntity
property SqlDataProvider<...>.dataContext.Main: SqlDataProvider<...>.dataContext.mainSchema
property SqlDataProvider<...>.dataContext.mainSchema.Orders: SqlDataProvider<...>.dataContext.mainSchema.main.Orders


<summary>The table Orders belonging to schema main</summary>
custom operation: where (bool)

Calls Linq.QueryBuilder.Where
property SqlDataProvider<...>.dataContext.main.OrdersEntity.Freight: decimal
custom operation: sortBy ('Key)

Calls Linq.QueryBuilder.SortBy
property SqlDataProvider<...>.dataContext.main.OrdersEntity.ShipPostalCode: string
custom operation: skip (int)

Calls Linq.QueryBuilder.Skip
custom operation: take (int)

Calls Linq.QueryBuilder.Take
custom operation: select ('Result)

Calls Linq.QueryBuilder.Select
val test : Map<string,obj> []

Full name: Querying.test
Multiple items
module Seq

from FSharp.Data.Sql

--------------------
module Seq

from Microsoft.FSharp.Collections
val toArray : source:seq<'T> -> 'T []

Full name: Microsoft.FSharp.Collections.Seq.toArray
Multiple items
module Array

from FSharp.Data.Sql

--------------------
module Array

from Microsoft.FSharp.Collections
val map : mapping:('T -> 'U) -> array:'T [] -> 'U []

Full name: Microsoft.FSharp.Collections.Array.map
val i : SqlDataProvider<...>.dataContext.main.OrdersEntity
property Common.SqlEntity.ColumnValues: seq<string * obj>
Multiple items
module Map

from Microsoft.FSharp.Collections

--------------------
type Map<'Key,'Value (requires comparison)> =
  interface IEnumerable
  interface IComparable
  interface IEnumerable<KeyValuePair<'Key,'Value>>
  interface ICollection<KeyValuePair<'Key,'Value>>
  interface IDictionary<'Key,'Value>
  new : elements:seq<'Key * 'Value> -> Map<'Key,'Value>
  member Add : key:'Key * value:'Value -> Map<'Key,'Value>
  member ContainsKey : key:'Key -> bool
  override Equals : obj -> bool
  member Remove : key:'Key -> Map<'Key,'Value>
  ...

Full name: Microsoft.FSharp.Collections.Map<_,_>

--------------------
new : elements:seq<'Key * 'Value> -> Map<'Key,'Value>
val ofSeq : elements:seq<'Key * 'T> -> Map<'Key,'T> (requires comparison)

Full name: Microsoft.FSharp.Collections.Map.ofSeq
val item : SqlDataProvider<...>.dataContext.main.OrdersEntity

Full name: Querying.item
custom operation: head

Calls Linq.QueryBuilder.Head
val exampleAsync : System.Threading.Tasks.Task<seq<SqlDataProvider<...>.dataContext.main.OrdersEntity>>

Full name: Querying.exampleAsync
val async : AsyncBuilder

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.async
val res : seq<SqlDataProvider<...>.dataContext.main.OrdersEntity>
val executeQueryAsync : (System.Linq.IQueryable<'a> -> Async<seq<'a>>)

Full name: FSharp.Data.Sql.Seq.executeQueryAsync
Multiple items
type Async
static member AsBeginEnd : computation:('Arg -> Async<'T>) -> ('Arg * AsyncCallback * obj -> IAsyncResult) * (IAsyncResult -> 'T) * (IAsyncResult -> unit)
static member AwaitEvent : event:IEvent<'Del,'T> * ?cancelAction:(unit -> unit) -> Async<'T> (requires delegate and 'Del :> Delegate)
static member AwaitIAsyncResult : iar:IAsyncResult * ?millisecondsTimeout:int -> Async<bool>
static member AwaitTask : task:Task -> Async<unit>
static member AwaitTask : task:Task<'T> -> Async<'T>
static member AwaitWaitHandle : waitHandle:WaitHandle * ?millisecondsTimeout:int -> Async<bool>
static member CancelDefaultToken : unit -> unit
static member Catch : computation:Async<'T> -> Async<Choice<'T,exn>>
static member FromBeginEnd : beginAction:(AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
static member FromBeginEnd : arg:'Arg1 * beginAction:('Arg1 * AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
static member FromBeginEnd : arg1:'Arg1 * arg2:'Arg2 * beginAction:('Arg1 * 'Arg2 * AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
static member FromBeginEnd : arg1:'Arg1 * arg2:'Arg2 * arg3:'Arg3 * beginAction:('Arg1 * 'Arg2 * 'Arg3 * AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
static member FromContinuations : callback:(('T -> unit) * (exn -> unit) * (OperationCanceledException -> unit) -> unit) -> Async<'T>
static member Ignore : computation:Async<'T> -> Async<unit>
static member OnCancel : interruption:(unit -> unit) -> Async<IDisposable>
static member Parallel : computations:seq<Async<'T>> -> Async<'T []>
static member RunSynchronously : computation:Async<'T> * ?timeout:int * ?cancellationToken:CancellationToken -> 'T
static member Sleep : millisecondsDueTime:int -> Async<unit>
static member Start : computation:Async<unit> * ?cancellationToken:CancellationToken -> unit
static member StartAsTask : computation:Async<'T> * ?taskCreationOptions:TaskCreationOptions * ?cancellationToken:CancellationToken -> Task<'T>
static member StartChild : computation:Async<'T> * ?millisecondsTimeout:int -> Async<Async<'T>>
static member StartChildAsTask : computation:Async<'T> * ?taskCreationOptions:TaskCreationOptions -> Async<Task<'T>>
static member StartImmediate : computation:Async<unit> * ?cancellationToken:CancellationToken -> unit
static member StartWithContinuations : computation:Async<'T> * continuation:('T -> unit) * exceptionContinuation:(exn -> unit) * cancellationContinuation:(OperationCanceledException -> unit) * ?cancellationToken:CancellationToken -> unit
static member SwitchToContext : syncContext:SynchronizationContext -> Async<unit>
static member SwitchToNewThread : unit -> Async<unit>
static member SwitchToThreadPool : unit -> Async<unit>
static member TryCancelled : computation:Async<'T> * compensation:(OperationCanceledException -> unit) -> Async<'T>
static member CancellationToken : Async<CancellationToken>
static member DefaultCancellationToken : CancellationToken

Full name: Microsoft.FSharp.Control.Async

--------------------
type Async<'T>

Full name: Microsoft.FSharp.Control.Async<_>
static member Async.StartAsTask : computation:Async<'T> * ?taskCreationOptions:System.Threading.Tasks.TaskCreationOptions * ?cancellationToken:System.Threading.CancellationToken -> System.Threading.Tasks.Task<'T>
val itemAsync : System.Threading.Tasks.Task<SqlDataProvider<...>.dataContext.main.OrdersEntity>

Full name: Querying.itemAsync
val item : SqlDataProvider<...>.dataContext.main.OrdersEntity
val headAsync : (System.Linq.IQueryable<'a> -> Async<'a>)

Full name: FSharp.Data.Sql.Seq.headAsync
module QueryEvents

from FSharp.Data.Sql.Common
val SqlQueryEvent : IEvent<string>

Full name: FSharp.Data.Sql.Common.QueryEvents.SqlQueryEvent
Multiple items
module Event

from Microsoft.FSharp.Control

--------------------
type Event<'T> =
  new : unit -> Event<'T>
  member Trigger : arg:'T -> unit
  member Publish : IEvent<'T>

Full name: Microsoft.FSharp.Control.Event<_>

--------------------
type Event<'Delegate,'Args (requires delegate and 'Delegate :> Delegate)> =
  new : unit -> Event<'Delegate,'Args>
  member Trigger : sender:obj * args:'Args -> unit
  member Publish : IEvent<'Delegate,'Args>

Full name: Microsoft.FSharp.Control.Event<_,_>

--------------------
new : unit -> Event<'T>

--------------------
new : unit -> Event<'Delegate,'Args>
val add : callback:('T -> unit) -> sourceEvent:IEvent<'Del,'T> -> unit (requires delegate and 'Del :> System.Delegate)

Full name: Microsoft.FSharp.Control.Event.add
val printfn : format:Printf.TextWriterFormat<'T> -> 'T

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.printfn
val randomBoolean : (unit -> bool)

Full name: Querying.randomBoolean
val r : System.Random
namespace System
Multiple items
type Random =
  new : unit -> Random + 1 overload
  member Next : unit -> int + 2 overloads
  member NextBytes : buffer:byte[] -> unit
  member NextDouble : unit -> float

Full name: System.Random

--------------------
System.Random() : unit
System.Random(Seed: int) : unit
System.Random.NextDouble() : float
val c1 : bool

Full name: Querying.c1
val c2 : bool

Full name: Querying.c2
val c3 : bool

Full name: Querying.c3
val sample : (string * string) []

Full name: Querying.sample
val x : string
property SqlDataProvider<...>.dataContext.main.OrdersEntity.ShipAddress: string
property SqlDataProvider<...>.dataContext.main.OrdersEntity.ShipCountry: string
property SqlDataProvider<...>.dataContext.main.OrdersEntity.ShipRegion: string
val bergs : SqlDataProvider<...>.dataContext.main.CustomersEntity

Full name: Querying.bergs
property SqlDataProvider<...>.dataContext.mainSchema.Customers: SqlDataProvider<...>.dataContext.mainSchema.main.Customers


<summary>The table Customers belonging to schema main</summary>
property SqlDataProvider<...>.dataContext.mainSchema.main.Customers.Individuals: SqlDataProvider<...>.dataContext.main.Customers.Individuals


<summary>Get individual items from the table. Requires single primary key.</summary>
property SqlDataProvider<...>.dataContext.main.Customers.Individuals.BERGS: SqlDataProvider<...>.dataContext.main.CustomersEntity
Fork me on GitHub