SQLProvider


Composable Queries

Definition of Composable Queries

Basicly composable methods are those that you can chain together to build the desired functionality out of smaller parts. By passing functions as parameters you are able to generate higher-order query operations. Therefore composable query means that you can do logics to compose just one database-SQL-query from multiple queryables. By using composable queries you can shorten the Database transactions and keep the connection open a minimum of time.

One common anti-pattern is a trial to solve all the problems in the world by a single code. So when you use this kind of features to achieve "common logics", keep in mind the software mainainability. One solution is to add a database view and query that from SQLProvider.

Generate composable queries by using Linq IQueryable

With composable queries you can combine two queries in multiple ways, and one query can be used as the building block for the other query. To see how this works, let’s look at a simple query:

let query1 =
    query {
      for customers  in ctx.Main.Customers do
      where (customers.ContactTitle = "USA")
      select (customers)}

The variable that is returned from the query is sometimes called a computation. If you write evaluate (e.g. a foreach loop or |> Seq.toList) and display the address field from the customers returned by this computation, you see the following output:

GREAL|Great Lakes Food Market|Howard Snyder|Marketing Manager|2732 Baker Blvd.|Eugene|OR|97403|USA|(503) 555-7555|
HUNGC|Hungry Coyote Import Store|Yoshi Latimer|Sales Representative|City Center Plaza 516 Main St.|Elgin|OR|97827|USA|(503) 555-6874|(503) 555-2376
...
TRAIH|Trail's Head Gourmet Provisioners|Helvetius Nagy|Sales Associate|722 DaVinci Blvd.|Kirkland|WA|98034|USA|(206) 555-8257|(206) 555-2174
WHITC|White Clover Markets|Karl Jablonski|Owner|305 - 14th Ave. S. Suite 3B|Seattle|WA|98128|USA|(206) 555-4112|(206) 555-4115

You can now write a second query against the result of this query:

let query2 =
    query {
      for customers in query1 do
      where (customers.CompanyName = "The Big Cheese")
      select customers}
    |> Seq.toArray

Notice that the last word in the first line of this query is the computation returned from the previous query. This second query produces the following output:

THEBI|The Big Cheese|Liz Nixon|Marketing Manager|89 Jefferson Way Suite 2|Portland|OR|97201|USA|(503) 555-3612|

SQLProvider to Objects queries are composable because they operate on and usually return variables of type IQueryable<T>. In other words, SQLProvider queries typically follow this pattern:

let (qry:IQueryable<'T>) =
    query {
        //for is like C# foreach
        for x in (xs:IQueryable<'T>) do
        select x
    }

//

This is a simple mechanism to understand, but it yields powerful results. It allows you to take complex problems, break them into manageable pieces, and solve them with code that is easy to understand and easy to maintain.

Generate composable queries by using .NET LINQ functions with IQueryable.

The difference between IEnumerable and IQueryable is basically that IEnumerable is executed in the IL while IQueryable can be translated as en expression tree to some other context (like a database query). They are both lazy by nature, meaning they aren’t evaluated until you enumerate over the results.

Here an example:

First you have to add .NET LINQ:

open System.Linq

Then you can define a composable query outside the main query

type CustomersEntity = sql.dataContext.``main.CustomersEntity``

let companyNameFilter inUse =

    let myFilter2 : IQueryable<CustomersEntity> -> IQueryable<CustomersEntity> = fun x -> x.Where(fun i -> i.CustomerId = "ALFKI")

    let queryable:(IQueryable<CustomersEntity> -> IQueryable<CustomersEntity>) =
        match inUse with
        |true ->
            (fun iq -> iq.Where(fun (c:CustomersEntity) -> c.CompanyName = "The Big Cheese"))
        |false -> 
            myFilter2
    queryable

(Let's asume that your inUse some complex data: E.g. Your sub-queries would come from other functions. Basic booleans you can just include to your where-clause)

Then you can create the main query

let query1 =
    query {
        for customers  in ctx.Main.Customers do
        where (customers.ContactTitle = "USA")
        select (customers)}

and now call you are able to call the second query like this

let res = companyNameFilter true query1 |> Seq.toArray

Generate composable queries by using FSharp.Linq.ComposableQuery

The SQLProvider also supports composable queries by integrating following library FSharpLinqComposableQuery. You can read more about that library here: FSharp.Linq.ComposableQuery

Because it is implemented in the SQLProvider you dont need to add FSharpComposableQuery in your script.

Example for using FSharpComposableQuery

let qry1 =
    query { for u in dbContext.Users do
            select (u.Id, u.Name, u.Email)
    }

let qry2 =
    query { for c in dbContext.Cars do
            select (c.UserId, c.Brand, c.Year)
    }

query { for (i,n,e) in qry1 do
        join (u,b,y) in qry2 on (i = u)
        where (y > 2015)
        select (i,n,e,u,b,y)
    } |> Seq.toArray

Nested Select Where Queries

You can create a query like SELECT * FROM xs WHERE xs.x IN (SELECT y FROM ys)) with either LINQ Contains or custom operators: in |=| and not-in |<>| This is done by not saying |> Seq.toArray to the first query:

open System.Linq

let nestedQueryTest =
    let qry1 = query {
        for emp in ctx.Hr.Employees do
        where (emp.FirstName.StartsWith("S"))
        select (emp.FirstName)
    }
    query {
        for emp in ctx.Hr.Employees do
        where (qry1.Contains(emp.FirstName))
        select (emp.FirstName, emp.LastName)
    } |> Seq.toArray

Using non-strongly-typed __.GetColumn "name"

All the entities inherit from SqlEntity which has GetColumn-method. So you can use non-strongly-typed columns like this:

let qry = 
    query {
        for x in query1 do
        where ((x.GetColumn<string> "CustomerId") = "ALFKI")
        select (x.GetColumn<string> "CustomerId")
    } |> Seq.head

However this is not recommended as one of the SQLProvider's key benefits is strong typing.

Generate composable queries from quotations

You can also construct composable queries using the F# quotation mechanism. For example, if you need to select a filter function at runtime, you could write the filters as quotations, and then include them into query like that:

let johnFilter = <@ fun (employee : sql.dataContext.``main.EmployeesEntity``) -> employee.FirstName = "John" @>
let pamFilter = <@ fun (employee : sql.dataContext.``main.EmployeesEntity``) -> employee.FirstName = "Pam" @>

let runtimeSelectedFilter = if 1 = 1 then johnFilter else pamFilter
let employees =
    query {
        for emp in ctx.Main.Employees do
        where ((%runtimeSelectedFilter) emp) 
        select emp
    } |> Seq.toArray

Quotations are AST representations which the Linq-to-SQL translator can use.

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;"
namespace System
namespace System.Linq
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 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 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 query1: IQueryable<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&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 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>
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>
property SqlDataProvider<...>.dataContext.main.CustomersEntity.ContactTitle: string with get, set
<summary>ContactTitle: nvarchar(30)</summary>
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 query2: SqlDataProvider<...>.dataContext.main.CustomersEntity[]
property SqlDataProvider<...>.dataContext.main.CustomersEntity.CompanyName: string with get, set
<summary>CompanyName: nvarchar(40)</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 |&gt; Seq.toArray </code> Evaluates to <c>[| 1; 2; 5 |]</c>. </example>
val qry: IQueryable<obj>
Multiple items
type IQueryable = inherit IEnumerable member ElementType: Type member Expression: Expression member Provider: IQueryProvider
<summary>Provides functionality to evaluate queries against a specific data source wherein the type of the data is not specified.</summary>

--------------------
type IQueryable<'T> = inherit IEnumerable<'T> inherit IEnumerable inherit IQueryable
<summary>Provides functionality to evaluate queries against a specific data source wherein the type of the data is known.</summary>
<typeparam name="T">The type of the data in the data source.</typeparam>
val x: obj
type CustomersEntity = SqlDataProvider<...>.dataContext.main.CustomersEntity
type dataContext = member ClearUpdates: unit -> List<SqlEntity> member CreateConnection: unit -> IDbConnection member GetUpdates: unit -> List<SqlEntity> member SaveContextSchema: unit -> SaveContextResponse member SubmitUpdates: unit -> Unit member SubmitUpdatesAsync: unit -> Task member ``Design Time Commands`` : DesignTimeCommands member Main: mainSchema member Pragma: Pragma nested type DesignTimeCommands ...
val companyNameFilter: inUse: bool -> (IQueryable<CustomersEntity> -> IQueryable<CustomersEntity>)
val inUse: bool
val myFilter2: (IQueryable<CustomersEntity> -> IQueryable<CustomersEntity>)
val x: IQueryable<CustomersEntity>
(extension) System.Collections.Generic.IEnumerable.Where<'TSource>(predicate: System.Func<'TSource,bool>) : System.Collections.Generic.IEnumerable<'TSource>
(extension) System.Collections.Generic.IEnumerable.Where<'TSource>(predicate: System.Func<'TSource,int,bool>) : System.Collections.Generic.IEnumerable<'TSource>
(extension) IQueryable.Where<'TSource>(predicate: Expressions.Expression<System.Func<'TSource,bool>>) : IQueryable<'TSource>
(extension) IQueryable.Where<'TSource>(predicate: Expressions.Expression<System.Func<'TSource,int,bool>>) : IQueryable<'TSource>
val i: CustomersEntity
property SqlDataProvider<...>.dataContext.main.CustomersEntity.CustomerId: string with get, set
<summary>CustomerID: nchar(5)</summary>
val queryable: (IQueryable<CustomersEntity> -> IQueryable<CustomersEntity>)
val iq: IQueryable<CustomersEntity>
val c: CustomersEntity
val res: CustomersEntity[]
val qry1: IQueryable<obj * obj * obj>
val u: obj
val qry2: IQueryable<obj * obj * int>
val c: obj
val i: obj
val n: obj
val e: 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 b: obj
val y: int
val nestedQueryTest: (obj * obj)[]
val qry1: IQueryable<obj>
val emp: obj
(extension) System.Collections.Generic.IEnumerable.Contains<'TSource>(value: 'TSource) : bool
(extension) IQueryable.Contains<'TSource>(item: 'TSource) : bool
(extension) System.Collections.Generic.IEnumerable.Contains<'TSource>(value: 'TSource, comparer: System.Collections.Generic.IEqualityComparer<'TSource>) : bool
(extension) IQueryable.Contains<'TSource>(item: 'TSource, comparer: System.Collections.Generic.IEqualityComparer<'TSource>) : bool
val qry: string
val x: SqlDataProvider<...>.dataContext.main.CustomersEntity
member Common.SqlEntity.GetColumn: key: string -> 'T
Multiple items
val string: value: 'T -> string
<summary>Converts the argument to a string using <c>ToString</c>.</summary>
<remarks>For standard integer and floating point values the and any type that implements <c>IFormattable</c><c>ToString</c> conversion uses <c>CultureInfo.InvariantCulture</c>. </remarks>
<param name="value">The input value.</param>
<returns>The converted string.</returns>
<example id="string-example"><code lang="fsharp"></code></example>


--------------------
type string = System.String
<summary>An abbreviation for the CLI type <see cref="T:System.String" />.</summary>
<category>Basic Types</category>
val head: source: seq<'T> -> 'T
<summary>Returns the first element of the sequence.</summary>
<param name="source">The input sequence.</param>
<returns>The first element of the sequence.</returns>
<exception cref="T:System.ArgumentNullException">Thrown when the input sequence is null.</exception>
<exception cref="T:System.ArgumentException">Thrown when the input does not have any elements.</exception>
<example id="head-1"><code lang="fsharp"> let inputs = ["banana"; "pear"] inputs |&gt; Seq.head </code> Evaluates to <c>banana</c></example>
<example id="head-2"><code lang="fsharp"> [] |&gt; Seq.head </code> Throws <c>ArgumentException</c></example>
val johnFilter: Quotations.Expr<(SqlDataProvider<...>.dataContext.main.EmployeesEntity -> bool)>
val employee: SqlDataProvider<...>.dataContext.main.EmployeesEntity
property SqlDataProvider<...>.dataContext.main.EmployeesEntity.FirstName: string with get, set
<summary>FirstName: nvarchar(10)</summary>
val pamFilter: Quotations.Expr<(SqlDataProvider<...>.dataContext.main.EmployeesEntity -> bool)>
val runtimeSelectedFilter: Quotations.Expr<(SqlDataProvider<...>.dataContext.main.EmployeesEntity -> bool)>
val employees: SqlDataProvider<...>.dataContext.main.EmployeesEntity[]
val emp: SqlDataProvider<...>.dataContext.main.EmployeesEntity
property SqlDataProvider<...>.dataContext.mainSchema.Employees: SqlDataProvider<...>.dataContext.mainSchema.main.Employees with get
<summary> The table Employees belonging to schema main</summary>