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.
type sql = SqlDataProvider<
Common.DatabaseProviderTypes.SQLITE,
connectionString,
SQLiteLibrary=Common.SQLiteLibrary.SystemDataSQLite,
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.
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:
let connectionString2 = "(insert runtime connection here)"
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.
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.
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.
Most of the databases support some kind of comments/descriptions/remarks to
tables and columns for documentation purposes. These descriptions are fetched
to tooltips for the tables and columns.
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.
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.
The SQL provider supports LINQ queries using F#'s query expression syntax.
let customersQuery =
query {
for customer in ctx.Main.Customers do
select customer
}
|> Seq.toArray
Support also async queries
let customersQueryAsync =
query {
for customer in ctx.Main.Customers do
select customer
}
|> Seq.executeQueryAsync
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
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.
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.
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.
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.
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.
let christina = ctx.Main.Customers.Individuals.``As ContactName``.``BERGS, Christina Berglund``
You should create and use one data context as long as it has the parameters you need.
An example of when to use multiple data contexts is when you need to pass different
connection strings to connect to different instances of the same database,
e.g. to copy data between them.
The connection itself is not stored and reused with an instance of the data context.
The data context creates a connection when you execute a query or when you call
SubmitUpdates()
. In terms of transactions, the data context object tracks (full)
entities that were retrieved using it via queries or Individuals
and manages their
states. Upon calling SubmitUpdates()
, all entities modified/created that belong to
that data context are wrapped in a single transaction scope, and then a connection
is created and thus enlisted into the transaction.
*#Important*:
The database schema (SQLProvider's understanding of the structure of tables, columns, names, types, etc of your database
- a "snapshot" if you will) is cached lazily while you use it.
What does that entail?
A. Once SQLProvider gets a "mental model" of your database (the schema),
that is what is used for any intellisense/completion suggestions for the rest of your IDE session.
This is a fantastic feature, because it means that you're not assaulting your database with a
new "What are you like?" query on EVERY SINGLE KEYSTROKE.
But what if the database changes? SQLProvider will NOT see your change because it's source of truth is
that locally cached schema snapshot it took right when it started, and that snapshot will persist until
one of 2 things happens:
1. A restart of your Editor/IDE.
The database is queried right when SQLProvider starts up, so you
could certainly force a refresh by restarting.
2. Forced clearing of the local database schema cache.
If SQLProvider is currently able to communicate with the database,
you can force the local cache to clear, to be invalidated and refreshed by
by using what are called `Design Time Commands`, specifically the
`ClearDatabaseSchemaCache` method.
You're probably thinking: "Ok, fine, that sounds good! How do I do that though?"
Just as SQLProvider can interact at compile time with the structure of data in your
database through your editor's completion tooling
(intellisense, language server protocol completion suggestions, etc),
you can also interact with SQLProvider at compile time the exact same way.
SQLProvider provides methods under the DataContext you get from your type alias,
and they actually show up as ``Design Time Commands`` in the completion.
Select that, and then "dot into" it afterwards, then under that is ClearDatabaseSchemaCache.
Then after that typing in a "." will actualy RUN the command, thereby clearing the cache.
B. LAZY evaluation means that where you save the database schema in your code matters.
Do not call the "Design Time Command" SaveContextSchema at the top of your code. FSharp is evaluated
top to bottom, and so if you call SaveContextSchema at the top, before you ask for specific columns in your code,
you will not get a schema that reflects your needs.
sql.GetDataContext(cs).``Design Time Commands``.SaveContextSchema // put a "." at the end to call the command at compile time.
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;"
Multiple items
namespace FSharp
--------------------
namespace Microsoft.FSharp
Multiple items
namespace FSharp.Data
--------------------
namespace Microsoft.FSharp.Data
namespace FSharp.Data.Sql
type sql = 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 1000.</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 SQLiteLibrary =
| SystemDataSQLite = 0
| MonoDataSQLite = 1
| AutoSelect = 2
| MicrosoftDataSqlite = 3
Common.SQLiteLibrary.SystemDataSQLite: Common.SQLiteLibrary = 0
[<Struct>]
type CaseSensitivityChange =
| ORIGINAL = 0
| TOUPPER = 1
| TOLOWER = 2
Common.CaseSensitivityChange.ORIGINAL: Common.CaseSensitivityChange = 0
val ctx: 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 connectionString2: string
val ctx2: SqlDataProvider<...>.dataContext
val customers: SqlDataProvider<...>.dataContext.main.CustomersEntity[]
property SqlDataProvider<...>.dataContext.Main: SqlDataProvider<...>.dataContext.mainSchema with get
property SqlDataProvider<...>.dataContext.mainSchema.Customers: SqlDataProvider<...>.dataContext.mainSchema.main.Customers with get
<summary> The table Customers belonging to schema main</summary>
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 toArray: source: seq<'T> -> 'T[]
<summary>Builds an array from the given collection.</summary>
<param name="source">The input sequence.</param>
<returns>The result array.</returns>
<exception cref="T:System.ArgumentNullException">Thrown when the input sequence is null.</exception>
<example id="toarray-1"><code lang="fsharp">
let inputs = seq { 1; 2; 5 }
inputs |> Seq.toArray
</code>
Evaluates to <c>[| 1; 2; 5 |]</c>.
</example>
val firstCustomer: SqlDataProvider<...>.dataContext.main.CustomersEntity
val name: string
property SqlDataProvider<...>.dataContext.main.CustomersEntity.ContactName: string with get, set
<summary>ContactName: nvarchar(30)</summary>
val orders: SqlDataProvider<...>.dataContext.main.OrdersEntity[]
val customersQuery: SqlDataProvider<...>.dataContext.main.CustomersEntity[]
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<int>) =
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()) |> Seq.toList
</code>
Evaluates to <c>[4; 4; 12; 12]</c>.
</example>
val customer: SqlDataProvider<...>.dataContext.main.CustomersEntity
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>
val customersQueryAsync: System.Threading.Tasks.Task<seq<SqlDataProvider<...>.dataContext.main.CustomersEntity>>
val executeQueryAsync: (System.Linq.IQueryable<'a> -> System.Threading.Tasks.Task<seq<'a>>)
<summary>
Execute SQLProvider query and release the OS thread while query is being executed.
</summary>
val filteredQuery: SqlDataProvider<...>.dataContext.main.CustomersEntity[]
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>
val multipleFilteredQuery: SqlDataProvider<...>.dataContext.main.CustomersEntity[]
property SqlDataProvider<...>.dataContext.main.CustomersEntity.Country: string with get, set
<summary>Country: nvarchar(15)</summary>
val automaticJoinQuery: (SqlDataProvider<...>.dataContext.main.CustomersEntity * SqlDataProvider<...>.dataContext.main.OrdersEntity)[]
val order: SqlDataProvider<...>.dataContext.main.OrdersEntity
val explicitJoinQuery: (SqlDataProvider<...>.dataContext.main.CustomersEntity * SqlDataProvider<...>.dataContext.main.OrdersEntity)[]
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>
property SqlDataProvider<...>.dataContext.mainSchema.Orders: SqlDataProvider<...>.dataContext.mainSchema.main.Orders with get
<summary> The table Orders belonging to schema main</summary>
property SqlDataProvider<...>.dataContext.main.OrdersEntity.CustomerId: string with get, set
<summary>CustomerID: nchar(5)</summary>
val ordersQuery: (string * System.DateTime * string)[]
property SqlDataProvider<...>.dataContext.main.OrdersEntity.OrderDate: System.DateTime with get, set
<summary>OrderDate: datetime</summary>
property SqlDataProvider<...>.dataContext.main.OrdersEntity.ShipAddress: string with get, set
<summary>ShipAddress: nvarchar(60)</summary>
val BERGS: SqlDataProvider<...>.dataContext.main.CustomersEntity
property SqlDataProvider<...>.dataContext.mainSchema.main.Customers.Individuals: SqlDataProvider<...>.dataContext.main.Customers.Individuals with get
<summary>Get individual items from the table. Requires single primary key.</summary>
property SqlDataProvider<...>.dataContext.main.Customers.Individuals.BERGS: SqlDataProvider<...>.dataContext.main.CustomersEntity with get
val christina: SqlDataProvider<...>.dataContext.main.CustomersEntity
val using: resource: 'T -> action: ('T -> 'U) -> 'U (requires 'T :> System.IDisposable)
<summary>Clean up resources associated with the input object after the completion of the given function.
Cleanup occurs even when an exception is raised by the protected
code. </summary>
<param name="resource">The resource to be disposed after action is called.</param>
<param name="action">The action that accepts the resource.</param>
<returns>The resulting value.</returns>
<example id="using-example">
The following code appends 10 lines to test.txt, then closes the StreamWriter when finished.
<code lang="fsharp">
open System.IO
using (File.AppendText "test.txt") (fun writer ->
for i in 1 .. 10 do
writer.WriteLine("Hello World {0}", i))
</code></example>