SQLProvider


SQL Provider Basics

The SQL provider is an erasing type provider which enables you to instantly connect to a variety of database sources in the IDE and explore them in a type-safe manner, without the inconvenience of a code-generation step.

SQL Provider supports the following database types:

After you have installed the nuget package or built the type provider assembly from source, you should reference the assembly either as a project reference or by using an F# interactive script file.

1: 
2: 
3: 
4: 
5: 
// when using the SQLProvider in a script file (.fsx), the file needs to be referenced
// using F#'s `#r` command:
#r "../../packages/SQLProvider.1.0.1/lib/net40/FSharp.Data.SqlProvider.dll"
// whether referencing in a script, or added to project as an assembly
// reference, the library needs to be opened
1: 
open FSharp.Data.Sql

To use the type provider you must first create a type alias.

In this declaration you are able to pass various pieces of information known as static parameters to initialize properties such as the connection string and database vendor type that you are connecting to.

In the following examples a SQLite database will be used. You can read in more detail about the available static parameters in other areas of the documentation.

1: 
2: 
3: 
4: 
type sql = SqlDataProvider<Common.DatabaseProviderTypes.SQLITE,
                           connectionString,
                           ResolutionPath = resolutionPath,
                           CaseSensitivityChange = Common.CaseSensitivityChange.ORIGINAL>

Now we have a type sql that represents the SQLite database provided in the connectionString parameter. In order to start exploring the database's schema and reading its data, you create a DataContext value.

1: 
let ctx = sql.GetDataContext()

If you want to use non-literal connectionString at runtime (e.g. crypted production passwords), you can pass your runtime connectionString parameter to GetDataContext:

1: 
let ctx2 = sql.GetDataContext connectionString2

When you press . on ctx, intellisense will display a list of properties representing the available tables and views within the database.

In the simplest case, you can treat these properties as sequences that can be enumerated.

1: 
let customers = ctx.Main.Customers |> Seq.toArray

This is the equivalent of executing a query that selects all rows and columns from the [main].[customers] table.

Notice the resulting type is an array of [Main].[Customers]Entity. These entities will contain properties relating to each column name from the table.

1: 
2: 
let firstCustomer = customers.[0]
let name = firstCustomer.ContactName

Each property is correctly typed depending on the database column definitions. In this example, firstCustomer.ContactName is a string.

Constraints and Relationships

A typical relational database will have many connected tables and views through foreign key constraints. The SQL provider is able to show you these constraints on entities. They appear as properties named the same as the constraint in the database.

You can gain access to these child or parent entities by simply enumerating the property in question.

1: 
let orders = firstCustomer.``main.Orders by CustomerID`` |> Seq.toArray

orders now contains all the orders belonging to firstCustomer. You will see the orders type is an array of [Main].[Orders]Entity indicating the resulting entities are from the [main].[Orders] table in the database. If you hover over FK_Orders_0_0 intellisense will display information about the constraint in question including the names of the tables involved and the key names.

Behind the scenes the SQL provider has automatically constructed and executed a relevant query using the entity's primary key.

Basic Querying

The SQL provider supports LINQ queries using F#'s query expression syntax.

1: 
2: 
3: 
4: 
5: 
6: 
let customersQuery = 
    query { 
        for customer in ctx.Main.Customers do
            select customer
    }
    |> Seq.toArray

Support also async queries

1: 
2: 
3: 
4: 
5: 
6: 
let customersQueryAsync = 
    query { 
        for customer in ctx.Main.Customers do
            select customer
    }
    |> Seq.executeQueryAsync |> Async.StartAsTask

The above example is identical to the query that was executed when ctx.[main].[Customers] |> Seq.toArray was evaluated.

You can extend this basic query include to filter criteria by introducing one or more where clauses

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
let filteredQuery = 
    query { 
        for customer in ctx.Main.Customers do
            where (customer.ContactName = "John Smith")
            select customer
    }
    |> Seq.toArray

let multipleFilteredQuery = 
    query { 
        for customer in ctx.Main.Customers do
            where ((customer.ContactName = "John Smith" && customer.Country = "England") || customer.ContactName = "Joe Bloggs")
            select customer
    }
    |> Seq.toArray

The SQL provider will accept any level of nested complex conditional logic in the where clause.

To access related data, you can either enumerate directly over the constraint property of an entity, or you can perform an explicit join.

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
let automaticJoinQuery = 
    query { 
        for customer in ctx.Main.Customers do
            for order in customer.``main.Orders by CustomerID`` do
                where (customer.ContactName = "John Smith")
                select (customer, order)
    }
    |> Seq.toArray

let explicitJoinQuery = 
    query { 
        for customer in ctx.Main.Customers do
            join order in ctx.Main.Orders on (customer.CustomerId = order.CustomerId)
            where (customer.ContactName = "John Smith")
            select (customer, order)
    }
    |> Seq.toArray

Both of these queries have identical results, the only difference is that one requires explicit knowledge of which tables join where and how, and the other doesn't. You might have noticed the select expression has now changed to (customer, order). As you may expect, this will return an array of tuples where the first item is a [Main].[Customers]Entity and the second a [Main].[Orders]Entity. Often you will not be interested in selecting entire entities from the database. Changing the select expression to use the entities' properties will cause the SQL provider to select only the columns you have asked for, which is an important optimization.

1: 
2: 
3: 
4: 
5: 
6: 
7: 
8: 
let ordersQuery = 
    query { 
        for customer in ctx.Main.Customers do
            for order in customer.``main.Orders by CustomerID`` do
                where (customer.ContactName = "John Smith")
                select (customer.ContactName, order.OrderDate, order.ShipAddress)
    }
    |> Seq.toArray

The results of this query will return the name, order date and ship address only. By doing this you no longer have access to entity types. The SQL provider supports various other query keywords and features that you can read about elsewhere in this documentation.

Individuals

The SQL provider has the ability via intellisense to navigate the actual data held within a table or view. You can then bind that data as an entity to a value.

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

Every table and view has an Individuals property. When you press dot on this property, intellisense will display a list of the data in that table, using whatever the primary key is as the text for each one. In this case, the primary key for [main].[Customers] is a string, and I have selected one named BERGS. You will see the resulting type is [main].[Customers]Entity.

The primary key is not usually very useful for identifying data however, so in addition to this you will see a series of properties named "As X" where X is the name of a column in the table. When you press . on one of these properties, the data is re-projected to you using both the primary key and the text of the column you have selected.

1: 
let christina = ctx.Main.Customers.Individuals.``As ContactName``.``BERGS, Christina Berglund``
Multiple items
type LiteralAttribute =
  inherit Attribute
  new : unit -> LiteralAttribute

Full name: Microsoft.FSharp.Core.LiteralAttribute

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

Full name: General.connectionString
val connectionString2 : string

Full name: General.connectionString2
val resolutionPath : string

Full name: General.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: General.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: General.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 ctx2 : SqlDataProvider<...>.dataContext

Full name: General.ctx2
val customers : SqlDataProvider<...>.dataContext.main.CustomersEntity []

Full name: General.customers
property SqlDataProvider<...>.dataContext.Main: SqlDataProvider<...>.dataContext.mainSchema
property SqlDataProvider<...>.dataContext.mainSchema.Customers: SqlDataProvider<...>.dataContext.mainSchema.main.Customers


<summary>The table Customers belonging to schema main</summary>
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
val firstCustomer : SqlDataProvider<...>.dataContext.main.CustomersEntity

Full name: General.firstCustomer
val name : string

Full name: General.name
property SqlDataProvider<...>.dataContext.main.CustomersEntity.ContactName: string
val orders : SqlDataProvider<...>.dataContext.main.OrdersEntity []

Full name: General.orders
val customersQuery : SqlDataProvider<...>.dataContext.main.CustomersEntity []

Full name: General.customersQuery
val query : Linq.QueryBuilder

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.query
val customer : SqlDataProvider<...>.dataContext.main.CustomersEntity
custom operation: select ('Result)

Calls Linq.QueryBuilder.Select
val customersQueryAsync : System.Threading.Tasks.Task<seq<SqlDataProvider<...>.dataContext.main.CustomersEntity>>

Full name: General.customersQueryAsync
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 filteredQuery : SqlDataProvider<...>.dataContext.main.CustomersEntity []

Full name: General.filteredQuery
custom operation: where (bool)

Calls Linq.QueryBuilder.Where
val multipleFilteredQuery : SqlDataProvider<...>.dataContext.main.CustomersEntity []

Full name: General.multipleFilteredQuery
property SqlDataProvider<...>.dataContext.main.CustomersEntity.Country: string
val automaticJoinQuery : (SqlDataProvider<...>.dataContext.main.CustomersEntity * SqlDataProvider<...>.dataContext.main.OrdersEntity) []

Full name: General.automaticJoinQuery
val order : SqlDataProvider<...>.dataContext.main.OrdersEntity
val explicitJoinQuery : (SqlDataProvider<...>.dataContext.main.CustomersEntity * SqlDataProvider<...>.dataContext.main.OrdersEntity) []

Full name: General.explicitJoinQuery
custom operation: join var in collection on (outerKey = innerKey). Note that parentheses are required after 'on'

Calls Linq.QueryBuilder.Join
property SqlDataProvider<...>.dataContext.mainSchema.Orders: SqlDataProvider<...>.dataContext.mainSchema.main.Orders


<summary>The table Orders belonging to schema main</summary>
property SqlDataProvider<...>.dataContext.main.OrdersEntity.CustomerId: string
val ordersQuery : (string * System.DateTime * string) []

Full name: General.ordersQuery
property SqlDataProvider<...>.dataContext.main.OrdersEntity.OrderDate: System.DateTime
property SqlDataProvider<...>.dataContext.main.OrdersEntity.ShipAddress: string
val BERGS : SqlDataProvider<...>.dataContext.main.CustomersEntity

Full name: General.BERGS
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
val christina : SqlDataProvider<...>.dataContext.main.CustomersEntity

Full name: General.christina
Fork me on GitHub