SQLProvider

A general .NET/Mono SQL database type provider. Current features:

• LINQ queries
• Lazy schema exploration
• Automatic constraint navigation
• Individuals
• Transactional CRUD operations with identity support
• Stored Procedures
• Functions
• Packages (Oracle)
• Composable Query integration
• Optional option types
• Mapping to record types
• Custom Operators
• Supports Asynchronous Operations
• Supports .NET Standard / .NET Core
• Supports saving DB schema offline, and SQL-Server *.dacpac files

The provider currently has explicit implementations for the following database vendors:

• SQL Server
• SQL Server SSDT
• SQLite
• PostgreSQL
• Oracle
• MySQL
• MsAccess
• Firebird
• DuckDB

There is also an ODBC provider that will let you connect to any ODBC source with limited features.

All database vendors except SQL Server and MS Access will require 3rd party ADO.NET connector objects to function. These are dynamically loaded at runtime so that the SQL provider project is not dependent on them. You must supply the location of the assemblies with the "ResolutionPath" static parameter.

SQLite is based on the .NET drivers found here. You will need the correct version for your specific architecture and setup.

PostgreSQL is based on the Npgsql .NET drivers found here. The type provider will make frequent calls to the database. Npgsql provides a set of performance related connection strings parameters for tweaking its performance

MySQL is based on the .NET drivers found here. You will need the correct version for your specific architecture and setup. You also need to specify ResolutionPath, which points to the folder containing the dll files for the MySQL driver.

Oracle is based on the current release (12.1.0.1.2) of the managed ODP.NET driver found here. However, although the managed version is recommended, it should also work with previous versions of the native driver.

SQL Server SSDT is based on the current release (160.20216.14) found here.

PM> Install-Package SQLProvider

Depending on the used provider, the namespace of the SqlDataProvider type class varies as above. The general version of SQLProvider is the most popular because it is the oldest one. It often uses reflection, so the database-specific ones may be better in performance. It also requires manual configuration with a reference assembly path to seek the reference database drivers.

Example

This example demonstrates the use of the SQL type provider:

// reference the type provider dll

#r "../../bin/lib/netstandard2.0/FSharp.Data.SqlProvider.Common.dll"
#r "../../bin/lib/netstandard2.0/FSharp.Data.SqlProvider.dll"

open FSharp.Data.Sql

let [<Literal>] resolutionPath = __SOURCE_DIRECTORY__ + @"/../files/sqlite"
let [<Literal>] connectionString = "Data Source=" + __SOURCE_DIRECTORY__ + @"\northwindEF.db;Version=3;Read Only=false;FailIfMissing=True;"
// create a type alias with the connection string and database vendor settings
type sql = SqlDataProvider<
              ConnectionString = connectionString,
              DatabaseVendor = Common.DatabaseProviderTypes.SQLITE,
              SQLiteLibrary=Common.SQLiteLibrary.SystemDataSQLite,
              ResolutionPath = resolutionPath,
              IndividualsAmount = 1000,
              UseOptionTypes = Common.NullableColumnType.OPTION
              >
let ctx = sql.GetDataContext()

// To use dynamic runtime connectionString, you could use:
// let ctx = sql.GetDataContext connectionString2

// pick individual entities from the database
let christina = ctx.Main.Customers.Individuals.``As ContactName``.``BERGS, Christina Berglund``

// directly enumerate an entity's relationships,
// this creates and triggers the relevant query in the background
let christinasOrders = christina.``main.Orders by CustomerID`` |> Seq.toArray

let mattisOrderDetails =
    query { for c in ctx.Main.Customers do
            // you can directly enumerate relationships with no join information
            for o in c.``main.Orders by CustomerID`` do
            // or you can explicitly join on the fields you choose
            join od in ctx.Main.OrderDetails on (o.OrderId = od.OrderId)
            //  the (!!) operator will perform an outer join on a relationship
            for prod in (!!) od.``main.Products by ProductID`` do
            // nullable columns can be represented as option types; the following generates IS NOT NULL
            where o.ShipCountry.IsSome
            // standard operators will work as expected; the following shows the like operator and IN operator
            where (c.ContactName =% ("Matti%") && c.CompanyName |=| [|"Squirrelcomapny";"DaveCompant"|] )
            sortBy o.ShipName
            // arbitrarily complex projections are supported
            select (c.ContactName,o.ShipAddress,o.ShipCountry,prod.ProductName,prod.UnitPrice) }
    |> Seq.toArray

Samples & documentation

The library comes with comprehensive documentation.

• General a high level view on the type providers' abilities and limitations
• Static Parameters available static parameters
• Querying information on supported LINQ keywords and custom operators with examples
• Relationships how to use automatic constraint navigation in your queries
• CRUD usage and limitations of transactional create - update - delete support
• Programmability usage and limitations of stored procedures and functions
• Individuals usage and limitations of this unique feature
• Composable Query information on integrating this project with the SQL provider
• Mapping to record types
• Unit-testing your SQL-query logics without a database.
• API Reference contains automatically generated documentation for all types, modules and functions in the library.

Database vendor specific issues and considerations are documented on their separate pages. Please see the menu on the right.

Contributing and copyright

The project is hosted on GitHub where you can report issues, fork the project and submit pull requests. If you're adding new public API, please also consider adding samples that can be turned into a documentation. You might also want to read library design notes to understand how it works. Our tests have more samples. Learn more tech tech details.

The library is available under Public Domain license, which allows modification and redistribution for both commercial and non-commercial purposes. For more information see the License file in the GitHub repository.