SQLProvider


Asynchronous Database Operation

You get more performance by concurrency. The idea of async database operations is to release the business logics thread while the database is doing its job. This can lead a huge performance difference on heavy traffic environment (basically, will your business logics server / web-server crash or not).

In the picture, we talk about the red block, which can be released to serve other customers. As usual with async operations, there will be more thread context switching, which may cause minor performance delays, but concurrency benefits should outweigh the context switching cons.

This is the theory. In practice SQLProvider is calling implementation of async methods from abstract classes under System.Data.Common. The implementation quality of your database connection .NET drivers will define if async is good for you or not. (E.g. The current situation is that MS-SQL-server handles async well and MySQL not so.)

Currently SQLProvider supports async operations on runtime, not design-time.

Your execution thread may change. For transactions to support this, .NET 4.5.1 has a fix for asynchronous transactions that has to be explicitly used.

Async queries and updates

Concept for async queries is this:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
26: 
27: 
28: 
29: 
open System
open System.Threading.Tasks
open FSharp.Data.Sql

type MyWebServer() = 
    member __.``Execute Business Logics`` (id : Guid) : Task<_> = 
        async {
            use transaction = 
                new System.Transactions.TransactionScope(
                // .NET 4.5.1 fix for asynchronous transactions:
                    System.Transactions.TransactionScopeAsyncFlowOption.Enabled
                )
            let context = TypeProviderConnection.GetDataContext cstr
            let! fetched =
                query {
                    for  t2 in context.MyDataBase.MyTable2 do
                    join t1 in context.MyDataBase.MyTable1 on (t2.ForeignId = t1.Id)
                    where (t2.Id = id)
                    select (t1)
                } |> Seq.executeQueryAsync

            fetched |> Seq.iter (fun entity ->
                entity.SetColumn("Updated", DateTime.UtcNow |> box)
            )
            do! context.SubmitUpdatesAsync()

            transaction.Complete()
            return "done!"
        } |> Async.StartAsTask

The functions to work with asynchrony are:

  • Array.executeQueryAsync : IQueryable<'a> -> Async<<'a> []>
  • List.executeQueryAsync : IQueryable<'a> -> Async<'a list>
  • Seq.executeQueryAsync : IQueryable<'a> -> Async>
  • Seq.lengthAsync : IQueryable<'a> -> Async
  • Seq.headAsync : IQueryable<'a> -> Async<'a>
  • Seq.tryHeadAsync : IQueryable<'a> -> Async<'a option>
  • and for your data context: SubmitUpdatesAsync : unit -> Async
  • Seq.sumAsync : IQueryable<'a when 'a : comparison> -> Async<'a>
  • Seq.minAsync : IQueryable<'a when 'a : comparison> -> Async<'a>
  • Seq.maxAsync : IQueryable<'a when 'a : comparison> -> Async<'a>
  • Seq.averageAsync : IQueryable<'a when 'a : comparison> -> Async<'a>

Seq is .NET IEnumerable, which is lazy. So be careful if using Seq.executeQueryAsync to not execute your queries several times.

Database asynchrony can't be used as a way to do parallelism inside one context.

Usually database operations can't be executed as parallel inside one context/transaction. That is an anti-pattern in general: the network lag between database and your logics server is probably the bottleneck of your system. So, in this order:

  1. Try to execute your business logics as database queries, as one big query.
  2. Or sometimes, not often, load eagerly data with single query and process it in the logics server.
  3. Avoid case that you create as many queries as your collection has items.

So if you are still in the worst case, 3, and have to deal with a List>, you cannot say Async.Parallel as that may corrupt your data. To avoid custom imperative while-loops, we have provided a little helper function for you, that is List.evaluateOneByOne.

Avoid network traffic between business logics (BL) and database (DB). When you exit the query-computation, you cause the traffic.

Multiple items
namespace FSharp

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

--------------------
namespace Microsoft.FSharp.Data
namespace FSharp.Data.Sql
type TypeProviderConnection = obj

Full name: Async.TypeProviderConnection
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.MSSQLSERVER: Common.DatabaseProviderTypes = 0
type CaseSensitivityChange =
  | ORIGINAL = 0
  | TOUPPER = 1
  | TOLOWER = 2

Full name: FSharp.Data.Sql.Common.CaseSensitivityChange
Common.CaseSensitivityChange.ORIGINAL: Common.CaseSensitivityChange = 0
namespace System
namespace System.Threading
namespace System.Threading.Tasks
Multiple items
type MyWebServer =
  new : unit -> MyWebServer
  member ( Execute Business Logics ) : id:Guid -> Task<string>

Full name: Async.MyWebServer

--------------------
new : unit -> MyWebServer
member MyWebServer.( Execute Business Logics ) : id:Guid -> Task<string>

Full name: Async.MyWebServer.( Execute Business Logics )
val id : Guid
Multiple items
type Guid =
  struct
    new : b:byte[] -> Guid + 4 overloads
    member CompareTo : value:obj -> int + 1 overload
    member Equals : o:obj -> bool + 1 overload
    member GetHashCode : unit -> int
    member ToByteArray : unit -> byte[]
    member ToString : unit -> string + 2 overloads
    static val Empty : Guid
    static member NewGuid : unit -> Guid
    static member Parse : input:string -> Guid
    static member ParseExact : input:string * format:string -> Guid
    ...
  end

Full name: System.Guid

--------------------
Guid()
Guid(b: byte []) : unit
Guid(g: string) : unit
Guid(a: int, b: int16, c: int16, d: byte []) : unit
Guid(a: uint32, b: uint16, c: uint16, d: byte, e: byte, f: byte, g: byte, h: byte, i: byte, j: byte, k: byte) : unit
Guid(a: int, b: int16, c: int16, d: byte, e: byte, f: byte, g: byte, h: byte, i: byte, j: byte, k: byte) : unit
Multiple items
type Task =
  new : action:Action -> Task + 7 overloads
  member AsyncState : obj
  member ContinueWith : continuationAction:Action<Task> -> Task + 9 overloads
  member CreationOptions : TaskCreationOptions
  member Dispose : unit -> unit
  member Exception : AggregateException
  member Id : int
  member IsCanceled : bool
  member IsCompleted : bool
  member IsFaulted : bool
  ...

Full name: System.Threading.Tasks.Task

--------------------
type Task<'TResult> =
  inherit Task
  new : function:Func<'TResult> -> Task<'TResult> + 7 overloads
  member ContinueWith : continuationAction:Action<Task<'TResult>> -> Task + 9 overloads
  member Result : 'TResult with get, set
  static member Factory : TaskFactory<'TResult>

Full name: System.Threading.Tasks.Task<_>

--------------------
Task(action: Action) : unit
Task(action: Action, cancellationToken: Threading.CancellationToken) : unit
Task(action: Action, creationOptions: TaskCreationOptions) : unit
Task(action: Action<obj>, state: obj) : unit
Task(action: Action, cancellationToken: Threading.CancellationToken, creationOptions: TaskCreationOptions) : unit
Task(action: Action<obj>, state: obj, cancellationToken: Threading.CancellationToken) : unit
Task(action: Action<obj>, state: obj, creationOptions: TaskCreationOptions) : unit
Task(action: Action<obj>, state: obj, cancellationToken: Threading.CancellationToken, creationOptions: TaskCreationOptions) : unit

--------------------
Task(function: Func<'TResult>) : unit
Task(function: Func<'TResult>, cancellationToken: Threading.CancellationToken) : unit
Task(function: Func<'TResult>, creationOptions: TaskCreationOptions) : unit
Task(function: Func<obj,'TResult>, state: obj) : unit
Task(function: Func<'TResult>, cancellationToken: Threading.CancellationToken, creationOptions: TaskCreationOptions) : unit
Task(function: Func<obj,'TResult>, state: obj, cancellationToken: Threading.CancellationToken) : unit
Task(function: Func<obj,'TResult>, state: obj, creationOptions: TaskCreationOptions) : unit
Task(function: Func<obj,'TResult>, state: obj, cancellationToken: Threading.CancellationToken, creationOptions: TaskCreationOptions) : unit
val async : AsyncBuilder

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.async
val transaction : Transactions.TransactionScope
namespace System.Transactions
Multiple items
type TransactionScope =
  new : unit -> TransactionScope + 7 overloads
  member Complete : unit -> unit
  member Dispose : unit -> unit

Full name: System.Transactions.TransactionScope

--------------------
Transactions.TransactionScope() : unit
Transactions.TransactionScope(scopeOption: Transactions.TransactionScopeOption) : unit
Transactions.TransactionScope(transactionToUse: Transactions.Transaction) : unit
Transactions.TransactionScope(scopeOption: Transactions.TransactionScopeOption, scopeTimeout: TimeSpan) : unit
Transactions.TransactionScope(scopeOption: Transactions.TransactionScopeOption, transactionOptions: Transactions.TransactionOptions) : unit
Transactions.TransactionScope(transactionToUse: Transactions.Transaction, scopeTimeout: TimeSpan) : unit
Transactions.TransactionScope(scopeOption: Transactions.TransactionScopeOption, transactionOptions: Transactions.TransactionOptions, interopOption: Transactions.EnterpriseServicesInteropOption) : unit
Transactions.TransactionScope(transactionToUse: Transactions.Transaction, scopeTimeout: TimeSpan, interopOption: Transactions.EnterpriseServicesInteropOption) : unit
val context : obj
val fetched : seq<obj>
val query : Linq.QueryBuilder

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.query
val t2 : obj
custom operation: join var in collection on (outerKey = innerKey). Note that parentheses are required after 'on'

Calls Linq.QueryBuilder.Join
val t1 : obj
custom operation: where (bool)

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

Calls Linq.QueryBuilder.Select
Multiple items
module Seq

from FSharp.Data.Sql

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

from Microsoft.FSharp.Collections
val executeQueryAsync : (Linq.IQueryable<'a> -> Async<seq<'a>>)

Full name: FSharp.Data.Sql.Seq.executeQueryAsync
val iter : action:('T -> unit) -> source:seq<'T> -> unit

Full name: Microsoft.FSharp.Collections.Seq.iter
val entity : obj
Multiple items
type DateTime =
  struct
    new : ticks:int64 -> DateTime + 10 overloads
    member Add : value:TimeSpan -> DateTime
    member AddDays : value:float -> DateTime
    member AddHours : value:float -> DateTime
    member AddMilliseconds : value:float -> DateTime
    member AddMinutes : value:float -> DateTime
    member AddMonths : months:int -> DateTime
    member AddSeconds : value:float -> DateTime
    member AddTicks : value:int64 -> DateTime
    member AddYears : value:int -> DateTime
    ...
  end

Full name: System.DateTime

--------------------
DateTime()
   (+0 other overloads)
DateTime(ticks: int64) : unit
   (+0 other overloads)
DateTime(ticks: int64, kind: DateTimeKind) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, calendar: Globalization.Calendar) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, kind: DateTimeKind) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, calendar: Globalization.Calendar) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, millisecond: int) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, millisecond: int, kind: DateTimeKind) : unit
   (+0 other overloads)
property DateTime.UtcNow: DateTime
val box : value:'T -> obj

Full name: Microsoft.FSharp.Core.Operators.box
Transactions.TransactionScope.Complete() : unit
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:TaskCreationOptions * ?cancellationToken:Threading.CancellationToken -> Task<'T>
Fork me on GitHub