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:

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:

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

Also stored procedures do support InvokeAsync.

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.

Why Not to Use Async

As with all the technical choices, there are drawbacks to consider.

Multiple items
type LiteralAttribute = inherit Attribute new: unit -> LiteralAttribute
<summary>Adding this attribute to a value causes it to be compiled as a CLI constant literal.</summary>
<category>Attributes</category>


--------------------
new: unit -> LiteralAttribute
[<Literal>] val resolutionPath: string = "C:\git\SQLProvider\docs\content\core/../../files/sqlite"
[<Literal>] val connectionString: string = "Data Source=C:\git\SQLProvider\docs\content\core\..\northwindEF.db;Version=3;Read Only=false;FailIfMissing=True;"
val cstr: string
Multiple items
namespace FSharp

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

--------------------
namespace Microsoft.FSharp.Data
namespace FSharp.Data.Sql
type TypeProviderConnection = SqlDataProvider<...>
type 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 50. Note GDPR/PII regulations if using individuals with ContextSchemaPath.</param> <param name='UseOptionTypes'>If set, F# option types will be used in place of nullable database columns. If not, 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'>Oracle: The owner of the schema for this provider to resolve. PostgreSQL: A list of schemas to resolve, separated by spaces, newlines, commas, or semicolons.</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='ContextSchemaPath'>The location of the context schema previously saved with SaveContextSchema. When not empty, will be used to populate the database schema instead of retrieving it from then database.</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> <param name='SsdtPath'>A path to an SSDT .dacpac file.'</param>
namespace FSharp.Data.Sql.Common
[<Struct>] type DatabaseProviderTypes = | MSSQLSERVER = 0 | SQLITE = 1 | POSTGRESQL = 2 | MYSQL = 3 | ORACLE = 4 | MSACCESS = 5 | ODBC = 6 | FIREBIRD = 7 | MSSQLSERVER_DYNAMIC = 8 | MSSQLSERVER_SSDT = 9
Common.DatabaseProviderTypes.SQLITE: Common.DatabaseProviderTypes = 1
[<Struct>] type NullableColumnType = | NO_OPTION = 0 | OPTION = 1 | VALUE_OPTION = 2
Common.NullableColumnType.OPTION: Common.NullableColumnType = 1
<summary> Option types are Option&lt;_&gt;. (Old true.) </summary>
[<Struct>] type CaseSensitivityChange = | ORIGINAL = 0 | TOUPPER = 1 | TOLOWER = 2
Common.CaseSensitivityChange.ORIGINAL: Common.CaseSensitivityChange = 0
[<Struct>] type SQLiteLibrary = | SystemDataSQLite = 0 | MonoDataSQLite = 1 | AutoSelect = 2 | MicrosoftDataSqlite = 3
Common.SQLiteLibrary.SystemDataSQLite: Common.SQLiteLibrary = 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>

--------------------
new: unit -> MyWebServer
val id: Guid
Multiple items
[<Struct>] type Guid = new: b: byte[] -> unit + 5 overloads member CompareTo: value: Guid -> int + 1 overload member Equals: g: Guid -> bool + 1 overload member GetHashCode: unit -> int member ToByteArray: unit -> byte[] member ToString: unit -> string + 2 overloads member TryFormat: destination: Span<char> * charsWritten: byref<int> * ?format: ReadOnlySpan<char> -> bool member TryWriteBytes: destination: Span<byte> -> bool static member (<>) : a: Guid * b: Guid -> bool static member (=) : a: Guid * b: Guid -> bool ...
<summary>Represents a globally unique identifier (GUID).</summary>

--------------------
Guid ()
Guid(b: byte[]) : Guid
Guid(b: ReadOnlySpan<byte>) : Guid
Guid(g: string) : Guid
Guid(a: int, b: int16, c: int16, d: byte[]) : Guid
Guid(a: int, b: int16, c: int16, d: byte, e: byte, f: byte, g: byte, h: byte, i: byte, j: byte, k: byte) : Guid
Guid(a: uint32, b: uint16, c: uint16, d: byte, e: byte, f: byte, g: byte, h: byte, i: byte, j: byte, k: byte) : Guid
Multiple items
type Task = interface IAsyncResult interface IDisposable new: action: Action -> unit + 7 overloads member ConfigureAwait: continueOnCapturedContext: bool -> ConfiguredTaskAwaitable member ContinueWith: continuationAction: Action<Task,obj> * state: obj -> Task + 19 overloads member Dispose: unit -> unit member GetAwaiter: unit -> TaskAwaiter member RunSynchronously: unit -> unit + 1 overload member Start: unit -> unit + 1 overload member Wait: unit -> unit + 4 overloads ...
<summary>Represents an asynchronous operation.</summary>

--------------------
type Task<'TResult> = inherit Task new: ``function`` : Func<obj,'TResult> * state: obj -> unit + 7 overloads member ConfigureAwait: continueOnCapturedContext: bool -> ConfiguredTaskAwaitable<'TResult> member ContinueWith: continuationAction: Action<Task<'TResult>,obj> * state: obj -> Task + 19 overloads member GetAwaiter: unit -> TaskAwaiter<'TResult> member WaitAsync: timeout: TimeSpan -> Task<'TResult> + 2 overloads member Result: 'TResult static member Factory: TaskFactory<'TResult>
<summary>Represents an asynchronous operation that can return a value.</summary>
<typeparam name="TResult">The type of the result produced by this <see cref="T:System.Threading.Tasks.Task`1" />.</typeparam>


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

--------------------
Task(``function`` : Func<'TResult>) : Task<'TResult>
Task(``function`` : Func<obj,'TResult>, state: obj) : Task<'TResult>
Task(``function`` : Func<'TResult>, cancellationToken: Threading.CancellationToken) : Task<'TResult>
Task(``function`` : Func<'TResult>, creationOptions: TaskCreationOptions) : Task<'TResult>
Task(``function`` : Func<obj,'TResult>, state: obj, cancellationToken: Threading.CancellationToken) : Task<'TResult>
Task(``function`` : Func<obj,'TResult>, state: obj, creationOptions: TaskCreationOptions) : Task<'TResult>
Task(``function`` : Func<'TResult>, cancellationToken: Threading.CancellationToken, creationOptions: TaskCreationOptions) : Task<'TResult>
Task(``function`` : Func<obj,'TResult>, state: obj, cancellationToken: Threading.CancellationToken, creationOptions: TaskCreationOptions) : Task<'TResult>
val async: AsyncBuilder
<summary>Builds an asynchronous workflow using computation expression syntax.</summary>
<example id="async-1"><code lang="fsharp"> let sleepExample() = async { printfn "sleeping" do! Async.Sleep 10 printfn "waking up" return 6 } sleepExample() |&gt; Async.RunSynchronously </code></example>
val transaction: Transactions.TransactionScope
namespace System.Transactions
Multiple items
type TransactionScope = interface IDisposable new: unit -> unit + 13 overloads member Complete: unit -> unit member Dispose: unit -> unit
<summary>Makes a code block transactional. This class cannot be inherited.</summary>

--------------------
Transactions.TransactionScope() : Transactions.TransactionScope
   (+0 other overloads)
Transactions.TransactionScope(transactionToUse: Transactions.Transaction) : Transactions.TransactionScope
   (+0 other overloads)
Transactions.TransactionScope(asyncFlowOption: Transactions.TransactionScopeAsyncFlowOption) : Transactions.TransactionScope
   (+0 other overloads)
Transactions.TransactionScope(scopeOption: Transactions.TransactionScopeOption) : Transactions.TransactionScope
   (+0 other overloads)
Transactions.TransactionScope(transactionToUse: Transactions.Transaction, scopeTimeout: TimeSpan) : Transactions.TransactionScope
   (+0 other overloads)
Transactions.TransactionScope(transactionToUse: Transactions.Transaction, asyncFlowOption: Transactions.TransactionScopeAsyncFlowOption) : Transactions.TransactionScope
   (+0 other overloads)
Transactions.TransactionScope(scopeOption: Transactions.TransactionScopeOption, scopeTimeout: TimeSpan) : Transactions.TransactionScope
   (+0 other overloads)
Transactions.TransactionScope(scopeOption: Transactions.TransactionScopeOption, transactionOptions: Transactions.TransactionOptions) : Transactions.TransactionScope
   (+0 other overloads)
Transactions.TransactionScope(scopeOption: Transactions.TransactionScopeOption, asyncFlowOption: Transactions.TransactionScopeAsyncFlowOption) : Transactions.TransactionScope
   (+0 other overloads)
Transactions.TransactionScope(transactionToUse: Transactions.Transaction, scopeTimeout: TimeSpan, interopOption: Transactions.EnterpriseServicesInteropOption) : Transactions.TransactionScope
   (+0 other overloads)
[<Struct>] type TransactionScopeAsyncFlowOption = | Suppress = 0 | Enabled = 1
<summary>Specifies whether transaction flow across thread continuations is enabled for <see cref="T:System.Transactions.TransactionScope" />.</summary>
field Transactions.TransactionScopeAsyncFlowOption.Enabled: Transactions.TransactionScopeAsyncFlowOption = 1
<summary>Specifies that transaction flow across thread continuations is enabled.</summary>
val context: SqlDataProvider<...>.dataContext
SqlDataProvider<...>.GetDataContext() : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary>
   (+0 other overloads)
SqlDataProvider<...>.GetDataContext(selectOperations: SelectOperations) : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary><param name='selectOperations'>Execute select-clause operations in SQL database rather than .NET-side.</param>
   (+0 other overloads)
SqlDataProvider<...>.GetDataContext(commandTimeout: int) : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary><param name='commandTimeout'>SQL command timeout. Maximum time for single SQL-command in seconds.</param>
   (+0 other overloads)
SqlDataProvider<...>.GetDataContext(transactionOptions: Transactions.TransactionOptions) : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary><param name='transactionOptions'>TransactionOptions for the transaction created on SubmitChanges.</param>
   (+0 other overloads)
SqlDataProvider<...>.GetDataContext(connectionString: string) : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary><param name='connectionString'>The database runtime connection string</param>
   (+0 other overloads)
SqlDataProvider<...>.GetDataContext(connectionString: string, selectOperations: SelectOperations) : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary><param name='connectionString'>The database runtime connection string</param><param name='selectOperations'>Execute select-clause operations in SQL database rather than .NET-side.</param>
   (+0 other overloads)
SqlDataProvider<...>.GetDataContext(transactionOptions: Transactions.TransactionOptions, commandTimeout: int) : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary><param name='transactionOptions'>TransactionOptions for the transaction created on SubmitChanges.</param><param name='commandTimeout'>SQL command timeout. Maximum time for single SQL-command in seconds.</param>
   (+0 other overloads)
SqlDataProvider<...>.GetDataContext(connectionString: string, commandTimeout: int) : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary><param name='connectionString'>The database runtime connection string</param><param name='commandTimeout'>SQL command timeout. Maximum time for single SQL-command in seconds.</param>
   (+0 other overloads)
SqlDataProvider<...>.GetDataContext(connectionString: string, transactionOptions: Transactions.TransactionOptions) : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary><param name='connectionString'>The database runtime connection string</param><param name='transactionOptions'>TransactionOptions for the transaction created on SubmitChanges.</param>
   (+0 other overloads)
SqlDataProvider<...>.GetDataContext(connectionString: string, resolutionPath: string) : SqlDataProvider<...>.dataContext
<summary>Returns an instance of the SQL Provider using the static parameters</summary><param name='connectionString'>The database runtime connection string</param><param name='resolutionPath'>The location to look for dynamically loaded assemblies containing database vendor specific connections and custom types</param>
   (+0 other overloads)
val fetched: seq<obj>
val query: Linq.QueryBuilder
<summary>Builds a query using query syntax and operators.</summary>
<example id="query-1"><code lang="fsharp"> let findEvensAndSortAndDouble(xs: System.Linq.IQueryable&lt;int&gt;) = query { for x in xs do where (x % 2 = 0) sortBy x select (x+x) } let data = [1; 2; 6; 7; 3; 6; 2; 1] findEvensAndSortAndDouble (data.AsQueryable()) |&gt; Seq.toList </code> Evaluates to <c>[4; 4; 12; 12]</c>. </example>
val t2: obj
custom operation: join var in collection on (outerKey = innerKey). Note that parentheses are required after 'on' Calls Linq.QueryBuilder.Join
<summary>A query operator that correlates two sets of selected values based on matching keys. Normal usage is 'join y in elements2 on (key1 = key2)'. </summary>
<example-tbd></example-tbd>
val t1: obj
custom operation: where (bool) Calls Linq.QueryBuilder.Where
<summary>A query operator that selects those elements based on a specified predicate. </summary>
<example-tbd></example-tbd>
custom operation: select ('Result) Calls Linq.QueryBuilder.Select
<summary>A query operator that projects each of the elements selected so far. </summary>
<example-tbd></example-tbd>
Multiple items
module Seq from FSharp.Data.Sql

--------------------
module Seq from Microsoft.FSharp.Collections
<summary>Contains operations for working with values of type <see cref="T:Microsoft.FSharp.Collections.seq`1" />.</summary>
val executeQueryAsync: (Linq.IQueryable<'a> -> Task<seq<'a>>)
<summary> Execute SQLProvider query and release the OS thread while query is being executed. </summary>
val iter: action: ('T -> unit) -> source: seq<'T> -> unit
<summary>Applies the given function to each element of the collection.</summary>
<param name="action">A function to apply to each element of the sequence.</param>
<param name="source">The input sequence.</param>
<exception cref="T:System.ArgumentNullException">Thrown when the input sequence is null.</exception>
<example id="iter-1"><code lang="fsharp"> ["a"; "b"; "c"] |&gt; Seq.iter (printfn "%s") </code> Evaluates to <c>unit</c> and prints <code> a b c </code> in the console. </example>
val entity: obj
Multiple items
[<Struct>] type DateTime = new: year: int * month: int * day: int -> unit + 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 ...
<summary>Represents an instant in time, typically expressed as a date and time of day.</summary>

--------------------
DateTime ()
   (+0 other overloads)
DateTime(ticks: int64) : DateTime
   (+0 other overloads)
DateTime(ticks: int64, kind: DateTimeKind) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int, calendar: Globalization.Calendar) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, kind: DateTimeKind) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, calendar: Globalization.Calendar) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, millisecond: int) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, millisecond: int, kind: DateTimeKind) : DateTime
   (+0 other overloads)
property DateTime.UtcNow: DateTime with get
<summary>Gets a <see cref="T:System.DateTime" /> object that is set to the current date and time on this computer, expressed as the Coordinated Universal Time (UTC).</summary>
<returns>An object whose value is the current UTC date and time.</returns>
val box: value: 'T -> obj
<summary>Boxes a strongly typed value.</summary>
<param name="value">The value to box.</param>
<returns>The boxed object.</returns>
<example id="box-example"><code lang="fsharp"> let x: int = 123 let obj1 = box x // obj1 is a generic object type unbox&lt;int&gt; obj1 // Evaluates to 123 (int) unbox&lt;double&gt; obj1 // Throws System.InvalidCastException </code></example>
SqlDataProvider<...>.dataContext.SubmitUpdatesAsync() : Task
<summary>Save changes to data-source. May throws errors: Use Async.Catch and to deal with non-saved items use GetUpdates() and ClearUpdates().</summary>
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<'T> -> Async<'T> + 1 overload 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 Choice: computations: seq<Async<'T option>> -> Async<'T option> static member FromBeginEnd: beginAction: (AsyncCallback * obj -> IAsyncResult) * endAction: (IAsyncResult -> 'T) * ?cancelAction: (unit -> unit) -> Async<'T> + 3 overloads static member FromContinuations: callback: (('T -> unit) * (exn -> unit) * (OperationCanceledException -> unit) -> unit) -> Async<'T> ...
<summary>Holds static members for creating and manipulating asynchronous computations.</summary>
<remarks> See also <a href="https://docs.microsoft.com/en-us/dotnet/fsharp/language-reference/asynchronous-workflows">F# Language Guide - Async Workflows</a>. </remarks>
<category index="1">Async Programming</category>


--------------------
type Async<'T>
<summary> An asynchronous computation, which, when run, will eventually produce a value of type T, or else raises an exception. </summary>
<remarks> This type has no members. Asynchronous computations are normally specified either by using an async expression or the static methods in the <see cref="T:Microsoft.FSharp.Control.FSharpAsync`1" /> type. See also <a href="https://docs.microsoft.com/en-us/dotnet/fsharp/language-reference/asynchronous-workflows">F# Language Guide - Async Workflows</a>. </remarks>
<namespacedoc><summary> Library functionality for asynchronous programming, events and agents. See also <a href="https://docs.microsoft.com/en-us/dotnet/fsharp/language-reference/asynchronous-workflows">Asynchronous Programming</a>, <a href="https://docs.microsoft.com/en-us/dotnet/fsharp/language-reference/members/events">Events</a> and <a href="https://docs.microsoft.com/en-us/dotnet/fsharp/language-reference/lazy-expressions">Lazy Expressions</a> in the F# Language Guide. </summary></namespacedoc>
<category index="1">Async Programming</category>
static member Async.StartAsTask: computation: Async<'T> * ?taskCreationOptions: TaskCreationOptions * ?cancellationToken: Threading.CancellationToken -> Task<'T>