SQLProvider


How to see the SQL-clause?

To display / debug your SQL-clauses you can add listener for your logging framework to SqlQueryEvent:

1: 
FSharp.Data.Sql.Common.QueryEvents.SqlQueryEvent |> Event.add (printfn "Executing SQL: %O")

The event has separate fields of Command and Parameters for you to store your clauses with a strongly typed logging system like Logary.

Querying

1: 
2: 
3: 
4: 
5: 
6: 
7: 
type sql  = SqlDataProvider<
                Common.DatabaseProviderTypes.SQLITE,
                connectionString,
                ResolutionPath = resolutionPath, 
                CaseSensitivityChange = Common.CaseSensitivityChange.ORIGINAL
            >
let ctx = sql.GetDataContext()

SQLProvider leverages F#'s query {} expression syntax to perform queries against the database. Though many are supported, not all LINQ expressions are.

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
let example =
    query {
        for order in ctx.Main.Orders do
        where (order.Freight > 0m)
        sortBy (order.ShipPostalCode)
        skip 3
        take 4
        select (order)
    }

let test = example |> Seq.toArray |> Array.map(fun i -> i.ColumnValues |> Map.ofSeq)

let item =
    query {
        for order in ctx.Main.Orders do
        where (order.Freight > 0m)
        head
    }

Or async versions:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
let exampleAsync =
    async {
        let! res =
            query {
                for order in ctx.Main.Orders do
                where (order.Freight > 0m)
                select (order)
            } |> Seq.executeQueryAsync
        return res
    } |> Async.StartAsTask

let itemAsync =
    async {
        let! item =
            query {
                for order in ctx.Main.Orders do
                where (order.Freight > 0m)
            } |> Seq.headAsync
        return item
    } |> Async.StartAsTask

If you consider using asynchronous queries, read more from the async documentation.

SELECT -clause operations

You can control the execution context of the select-operations by GetDataContext parameter selectOperations. The LINQ-query stays the same. You have two options: DotNetSide or DatabaseSide.

This might have a significant effect on the size of data transferred from the database.

SelectOperations.DotNetSide (Default)

Fetch the columns and run operations in .NET-side.

1: 
2: 
3: 
4: 
5: 
6: 
    let dc = sql.GetDataContext(SelectOperations.DotNetSide) // (same as without the parameter)
    query {
        for cust in dc.Main.Customers do
        select (if cust.Country = "UK" then (cust.City)
            else ("Outside UK"))
    } |> Seq.toArray
1: 
2: 
3: 
4: 
SELECT 
   [cust].[Country] as 'Country',
   [cust].[City] as 'City' 
FROM main.Customers as [cust]

SelectOperations.DatabaseSide

Execute the operations as part of SQL.

1: 
2: 
3: 
4: 
5: 
6: 
7: 
let dc = sql.GetDataContext(SelectOperations.DatabaseSide)
let qry = 
    query {
        for cust in dc.Main.Customers do
        select (if cust.Country = "UK" then (cust.City)
            else ("Outside UK"))
    } |> Seq.toArray
1: 
2: 
3: 
4: 
5: 
6: 
7: 
SELECT 
   CASE WHEN ([cust].[Country] = @param1) THEN 
   [cust].[City] 
   ELSE @param2 
END as [result] 
FROM main.Customers as [cust]
-- params @param1 - "UK"; @param2 - "Outside UK"

Supported Query Expression Keywords

Keyword

Supported

Notes

.Contains()

X

open System.Linq, in where, SQL IN-clause, nested query

.Concat()

X

open System.Linq, SQL UNION ALL-clause

.Union()

X

open System.Linq, SQL UNION-clause

all

X

averageBy

X

Single table (1)

averageByNullable

X

Single table (1)

contains

X

count

X

distinct

X

exactlyOne

X

exactlyOneOrDefault

X

exists

X

find

X

groupBy

x

Simple support (2)

groupJoin

groupValBy

head

X

headOrDefault

X

if

X

join

X

last

lastOrDefault

leftOuterJoin

let

x

...but not using tmp variables in where-clauses

maxBy

X

Single table (1)

maxByNullable

X

Single table (1)

minBy

X

Single table (1)

minByNullable

X

Single table (1)

nth

X

select

X

skip

X

skipWhile

sortBy

X

sortByDescending

X

sortByNullable

X

sortByNullableDescending

X

sumBy

X

Single table (1)

sumByNullable

X

Single table (1)

take

X

takeWhile

thenBy

X

thenByDescending

X

thenByNullable

X

thenByNullableDescending

X

where

x

Server side variables must either be plain without .NET operations or use the supported canonical functions.

Currently SQL-provider doesn't generate nested queries in from-clauses, the query is flattened to a single select. Nested in-clauses in where-clauses are supported. (1) Single table, if you want multiple tables, use corresponding Seq query or async aggregates, like Seq.sumQuery or Seq.sumAsync. (2) Very simple groupBy (and having) is supported: Single table, or max 3 table joins before groupBy, with direct aggregates like .Count() or direct parameter calls like .Sum(fun entity -> entity.UnitPrice), and max 7 key columns. No nested grouping.

Canonical Functions

Besides that, we support these .NET-functions to transfer the logics to SQL-clauses (starting from SQLProvider version 1.0.57). If you use these, remember to check your database indexes.

.NET String Functions (.NET)

.NET

MsSqlServer

PostgreSql

MySql

Oracle

SQLite

MSAccess

Odbc

Notes

.Substring(x)

SUBSTRING

SUBSTRING

MID

SUBSTR

SUBSTR

Mid

SUBSTRING

Start position may vary (0 or 1 based.)

.ToUpper()

UPPER

UPPER

UPPER

UPPER

UPPER

UCase

UCASE

.ToLower()

LOWER

LOWER

LOWER

LOWER

LOWER

LCase

LCASE

.Trim()

LTRIM(RTRIM)

TRIM(BOTH...)

TRIM

TRIM

TRIM

Trim

LTRIM(RTRIM)

.Length()

DATALENGTH

CHAR_LENGTH

CHAR_LENGTH

LENGTH

LENGTH

Len

CHARACTER_LENGTH

.Replace(a,b)

REPLACE

REPLACE

REPLACE

REPLACE

REPLACE

Replace

REPLACE

.IndexOf(x)

CHARINDEX

STRPOS

LOCATE

INSTR

INSTR

InStr

LOCATE

.IndexOf(x, i)

CHARINDEX

LOCATE

INSTR

InStr

LOCATE

(+)

+

||

CONCAT

||

||

&

CONCAT

In where-clauses you can also use .Contains("..."), .StartsWith("...") and .EndsWith("..."), which are translated to corresponding LIKE-clauses (e.g. StartsWith("abc") is LIKE ('asdf%').

Substring(startpos,length) is supported. IndexOf with length paramter is supported except PostgreSql and SQLite.

Operations do support parameters to be either constants or other SQL-columns (e.g. x.Substring(x.Length() - 1)).

.NET DateTime Functions

.NET

MsSqlServer

PostgreSql

MySql

Oracle

SQLite

MSAccess

Odbc

Notes

.Date

CAST(AS DATE)

DATE_TRUNC

DATE

TRUNC

STRFTIME

DateValue(Format)

CONVERT(SQL_DATE)

.Year

YEAR

DATE_PART

YEAR

EXTRACT

STRFTIME

Year

YEAR

.Month

MONTH

DATE_PART

MONTH

EXTRACT

STRFTIME

Month

MONTH

.Day

DAY

DATE_PART

DAY

EXTRACT

STRFTIME

Day

DAYOFMONTH

.Hour

DATEPART HOUR

DATE_PART

HOUR

EXTRACT

STRFTIME

Hour

HOUR

.Minute

DATEPART MINUTE

DATE_PART

MINUTE

EXTRACT

STRFTIME

Minute

MINUTE

.Second

DATEPART SECOND

DATE_PART

SECOND

EXTRACT

STRFTIME

Second

SECOND

.Subtract(y).Days

DATEDIFF

y-x

DATEDIFF

y-x

x-y

DateDiff

DATEDIFF

.Subtract(y).Seconds

TIMESTAMPDIFF

EXTRACT(EPOCH)

TIMESTAMPDIFF

y-x

x-y

DateDiff

DATEDIFF

.AddYears(i)

DATEADD YEAR

  • INTERVAL

DATE_ADD

  • INTERVAL

DATETIME

DateAdd

.AddMonths(i)

DATEADD MONTH

  • INTERVAL

DATE_ADD

  • INTERVAL

DATETIME

DateAdd

.AddDays(f)

DATEADD DAY

  • INTERVAL

DATE_ADD

  • INTERVAL

DATETIME

DateAdd

.AddHours(f)

DATEADD HOUR

  • INTERVAL

DATE_ADD

  • INTERVAL

DATETIME

DateAdd

.AddMinutes(f)

DATEADD MINUTE

  • INTERVAL

DATE_ADD

  • INTERVAL

DATETIME

DateAdd

.AddSeconds(f)

DATEADD SECOND

  • INTERVAL

DATE_ADD

  • INTERVAL

DATETIME

DateAdd

AddYears, AddDays and AddMinutes parameter can be either constant or other SQL-column, except in SQLite which supports only constant. AddMonths, AddHours and AddSeconds supports only constants for now. Odbc standard doesn't seem to have a date-add functionality. .NET has float parameters on some time-functions like AddDays, but SQL may ignore the decimal fraction.

Numerical Functions (e.g. Microsoft.FSharp.Core.Operators)

.NET

MsSqlServer

PostgreSql

MySql

Oracle

SQLite

MSAccess

Odbc

Notes

abs(i)

ABS

ABS

ABS

ABS

ABS

Abs

ABS

ceil(i)

CEILING

CEILING

CEILING

CEIL

CAST + 0.5

Fix+1

CEILING

floor(i)

FLOOR

FLOOR

FLOOR

FLOOR

CAST AS INT

Int

FLOOR

round(i)

ROUND

ROUND

ROUND

ROUND

ROUND

Round

ROUND

Math.Round(i,x)

ROUND

ROUND

ROUND

ROUND

ROUND

Round

ROUND

truncate(i)

TRUNCATE

TRUNC

TRUNCATE

TRUNC

Fix

TRUNCATE

sqrt(i)

SQRT

SQRT

SQRT

SQRT

SQRT

Sqr

SQRT

sin(i)

SIN

SIN

SIN

SIN

SIN

SIN

SIN

cos(i)

COS

COS

COS

COS

COS

COS

COS

tan(i)

TAN

TAN

TAN

TAN

TAN

TAN

TAN

asin(i)

ASIN

ASIN

ASIN

ASIN

ASIN

ASIN

acos(i)

ACOS

ACOS

ACOS

ACOS

ACOS

ACOS

atan(i)

ATAN

ATAN

ATAN

ATAN

ATAN

Atn

ATAN

Math.Max(x,y)

SELECT(MAX)

GREATEST

GREATEST

GREATEST

MAX

iif(x>y,x,y)

GREATEST

Math.Min(x,y)

SELECT(MIN)

LEAST

LEAST

LEAST

MIN

iif(x<y,x,y)

LEAST

Math.Pow(x,y)

POWER(x,y)

POWER(x,y)

POWER(x,y)

POWER(x,y)

x^y

POWER(x,y)

(+)

+

+

+

+

+

+

+

(-)

-

-

-

-

-

-

-

(*)

*

*

*

*

*

*

*

(/)

/

/

/

/

/

/

/

(%)

%

%

%

%

%

%

%

Microsoft SQL Server doesn't have Greatest and Least functions, so that will be done via nested SQL clause: (select max(v) from (values (x), (y)) as value(v)) It might also not be standard ODBC, but should work e.g. on Amazon Redshift.

Condition operations and others

.NET

MsSqlServer

PostgreSql

MySql

Oracle

SQLite

MSAccess

Odbc

Notes

.ToString()

CAST(NVARCHAR)

::varchar

CAST(CHAR)

CAST(VARCHAR)

CAST(TEXT)

CStr

CONVERT

if x then y else z

CASE WHEN

CASE WHEN

IF(x,y,z)

CASE WHEN

CASE WHEN

iif(x,y,z)

CASE WHEN

If the condition is not using SQL columns, it will be parsed before creation of SQL. If the condition is containing columns, it will be parsed into SQL.

If the condition is in the result of projection (the final select clause), it may be parsed after execution of the SQL, depending on parameter setting selectOperations.

Aggregate Functions

Also you can use these to return an aggregated value, or in a group-by clause:

.NET

MsSqlServer

PostgreSql

MySql

Oracle

SQLite

MSAccess

Odbc

Notes

count

COUNT

COUNT

COUNT

COUNT

COUNT

COUNT

COUNT

sum

SUM

SUM

SUM

SUM

SUM

SUM

SUM

min

MIN

MIN

MIN

MIN

MIN

MIN

MIN

max

MAX

MAX

MAX

MAX

MAX

MAX

MAX

average

AVG

AVG

AVG

AVG

AVG

AVG

AVG

StdDev

STDEV

STDDEV

STDDEV

STDDEV

STDEV

STDEV

Variance

VAR

VARIANCE

VARIANCE

VARIANCE

DVAR

VAR

StdDev, Variance are located in FSharp.Data.Sql.Operators namespace and also Seq.stdDevAsync and Seq.varianceAsync. Others can be used from List, Seq and Array modules, or Seq.countAsync, Seq.sumAsync, Seq.minAsync, Seq.maxAsync, Seq.averageAsync.

More details

By default query { ... } is IQueryable<T> which is lazy. To execute the query you have to do Seq.toList, Seq.toArray, or some corresponding operation. If you don't do that but just continue inside another query { ... } or use System.Linq .Where(...) etc, that will still be combined to the same SQL-query.

There are some limitation of complexity of your queries, but for example this is still ok and will give you very simple select-clause:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
let randomBoolean = 
    let r = System.Random()
    fun () -> r.NextDouble() > 0.5
let c1 = randomBoolean()
let c2 = randomBoolean()
let c3 = randomBoolean()

let sample =
    query {
        for order in ctx.Main.Orders do
        where ((c1 || order.Freight > 0m) && c2)
        let x = "Region: " + order.ShipAddress
        select (x, if c3 then order.ShipCountry else order.ShipRegion)
    } |> Seq.toArray

It can be for example (but it can also leave [Freight]-condition away and select ShipRegion instead of ShipAddress, depending on your randon values):

1: 
2: 
3: 
4: 
5: 
    SELECT 
        [_arg2].[ShipAddress] as 'ShipAddress',
        [_arg2].[ShipCountry] as 'ShipCountry' 
    FROM main.Orders as [_arg2] 
    WHERE (([_arg2].[Freight]> @param1)) 

Expressions

These operators perform no specific function in the code itself, rather they are placeholders replaced by their database-specific server-side operations. Their utility is in forcing the compiler to check against the correct types.

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

Operators

You can find some custom operators using FSharp.Data.Sql:

  • |=| (In set)
  • |<>| (Not in set)
  • =% (Like)
  • <>% (Not like)
  • !! (Left join)

Best practices working with queries

When using Option types, check IsSome in where-clauses.

You may want to use F# Option to represent database null, with SQLProvider static constructor parameter UseOptionTypes = true. Database null-checking is done with x IS NULL. With option types, easiest way to do that is to check IsSome and IsNone:

1: 
2: 
3: 
4: 
5: 
6: 
7: 
8: 
let result =
    query {
        for order in ctx.Main.Orders do
        where (
            order.ShippedDate.IsSome && 
            order.ShippedDate.Value.Year = 2015)
        select (order.OrderId, order.Freight)
    } |> Array.executeQueryAsync

Using booleans and simple variables (from outside a scope) in where-clauses

This is how you make your code easier to read when you have multiple code paths. SQLProvider will optimize the SQL-clause before sending it to the database, so it will still be simple.

Consider how clean is this source-code compared to other with similar logic:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
open System.Linq
let getOrders(futureOrders:bool, shipYears:int list) =

    let today = DateTime.UtcNow.Date
    let pastOrders = not futureOrders
    let noYearFilter = shipYears.IsEmpty

    let result =
        query {
            for order in ctx.Main.Orders do
            where ( 
                (noYearFilter || shipYears.Contains(order.ShippedDate.Year))
                &&
                ((futureOrders && order.OrderDate > today) ||
                 (pastOrders   && order.OrderDate <= today))
            ) 
            select (order.OrderId, order.Freight)
        } |> Array.executeQueryAsync
    result

Don't select all the fields if you don't need them

In general you should select only columns you need and not a whole object if you don't update its fields.

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
// Select all the fields from a table, basically:
// SELECT TOP 1 Address, City, CompanyName, 
//      ContactName, ContactTitle, Country, 
//      CustomerID, Fax, Phone, PostalCode, 
//      Region FROM main.Customers
let selectFullObject =
    query {
        for customer in ctx.Main.Customers do
        select customer
    } |> Seq.tryHeadAsync

// Select only two fields, basically:
// SELECT TOP 1 Address, City FROM main.Customers
let selectSmallObject =
    query {
        for customer in ctx.Main.Customers do
        select (customer.Address, customer.City)
    } |> Seq.tryHeadAsync

If you still want the whole objects and return those to a client as untyped records, you can use ColumnValues |> Map.ofSeq:

1: 
2: 
3: 
4: 
5: 
6: 
7: 
8: 
let someQuery =
    query {
        for customer in ctx.Main.Customers do
        //where(...)
        select customer
    } |> Seq.toArray

someQuery |> Array.map(fun c -> c.ColumnValues |> Map.ofSeq)

F# Map values are accessed like this: myItem.["City"]

Using code logic in select-clause

Don't be scared to insert non-Sql syntax to select-clauses. They will be parsed business-logic side!

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
let fetchOrders customerZone =
    let currentDate = DateTime.UtcNow.Date
    query {
        for order in ctx.Main.Orders do
        // where(...)
        select {
            OrderId = order.OrderId
            Timezone = 
                parseTimezoneFunction(order.ShipRegion, order.ShippedDate, customerZone);
            Status = 
                if order.ShippedDate > currentDate then "Shipped"
                elif order.OrderDate > currentDate then "Ordered"
                elif order.RequiredDate > currentDate then "Late"
                else "Waiting"
            OrderRows = [||];
        }
    } |> Seq.toArray

You can't have a let inside a select, but you can have custom function calls like parseTimezoneFunction here. Just be careful, they are executed for each result item separately. So if you want also SQL to execute there, it's rather better to do a separate function taking a collection as parameter. See below.

Using one sub-query to populate items

Sometimes you want to fetch efficiently sub-items, like "Give me all orders with their order-rows"

In the previous example we fetched OrderRows as empty array. Now we populate those with one query in immutable way:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
26: 
let orders = fetchOrders 123

let orderIds = 
    orders 
    |> Array.map(fun o -> o.OrderId) 
    |> Array.distinct
    
// Fetch all rows with one query
let subItems =
    query {
        for row in ctx.Main.OrderDetails do
        where (orderIds.Contains(row.OrderId))
        select (row.OrderId, row.ProductId, row.Quantity)
    } |> Seq.toArray
    
let ordersWithDetails =
    orders 
    |> Array.map(fun order ->
        {order with 
            // Match the corresponding sub items
            // to a parent item's colleciton:
            OrderRows = 
                subItems 
                |> Array.filter(fun (orderId,_,_) -> 
                    order.OrderId = orderId)
                })

How to deal with large IN-queries?

The pervious query had orderIds.Contains(row.OrderId). Which is fine if your collection has 50 items but what if there are 5000 orderIds? SQL-IN will fail. You have two easy options to deal with that.

Chunk your collection:

F# has built-in chunkBySize function!

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
let chunked = orderIds |> Array.chunkBySize 100

for chunk in chunked do
    let all =
        query {
            for row in ctx.Main.OrderDetails do
            where (chunk.Contains(row.OrderId))
            select (row)
        } |> Seq.toArray

    all |> Array.iter(fun row -> row.Discount <- 0.1)
    ctx.SubmitUpdates()

Creating a nested query

By leaving the last |> Seq.toArray away from your main query you create a lazy IQueryable<...>-query. Which means your IN-objects are not fetched from the database, but is actually a nested query.

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
26: 
27: 
28: 
29: 
30: 
31: 
32: 
33: 
34: 
let nestedOrders =
    query {
        for order in ctx.Main.Orders do
        // where(...)
        select (order.OrderId)
    } 

let subItemsAll =
    query {
        for row in ctx.Main.OrderDetails do
        where (nestedOrders.Contains(row.OrderId))
        select (row.OrderId, row.ProductId, row.Quantity)
    } |> Seq.toArray

// similar as previous fetchOrders
let fetchOrders2 customerZone =
    let currentDate = DateTime.UtcNow.Date
    query {
        for order in ctx.Main.Orders do
        // where(...)
        select {
            OrderId = order.OrderId
            Timezone = 
                parseTimezoneFunction(order.ShipRegion, order.ShippedDate, customerZone);
            Status = 
                if order.ShippedDate > currentDate then "Shipped"
                elif order.OrderDate > currentDate then "Ordered"
                elif order.RequiredDate > currentDate then "Late"
                else "Waiting"
            OrderRows = 
                subItemsAll |> (Array.filter(fun (orderId,_,_) -> 
                    order.OrderId = orderId));
        }
    } |> Seq.toArray

That way order hit count doesn't matter as the database is taking care of it.

Group-by and more complex query scenarios

One problem with SQLProvider is that monitorin the SQL-clause performance hitting to database indexes is hard to track. So the best way to handle complex SQL is to create a database view and query that from SQLProvider.

Still, if you want to use LINQ groupBy, this is how it's done:

1: 
2: 
3: 
4: 
5: 
6: 
7: 
let freightsByCity =
    query {
        for o in ctx.Main.Orders do
        //where (...)
        groupBy o.ShipCity into cites
        select (cites.Key, cites.Sum(fun order -> order.Freight))
    } |> Array.executeQueryAsync

Group-by is support is limited, mostly for single tables only. F# Linq query syntax doesnt support doing select count(1), sum(UnitPrice) from Products but you can group by a constant to get that:

1: 
2: 
3: 
4: 
5: 
6: 
let qry = 
	query {
		for p in dc.Main.Products do
		groupBy 1 into g
		select (g.Count(), g.Sum(fun p -> p.UnitPrice))
	} |> Seq.head

For more info see:

Multiple items
type LiteralAttribute =
  inherit Attribute
  new : unit -> LiteralAttribute

--------------------
new : unit -> LiteralAttribute
val connectionString : string
val resolutionPath : string
Multiple items
namespace FSharp

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

--------------------
namespace Microsoft.FSharp.Data
namespace FSharp.Data.Sql
namespace System
type CustomOrder =
  { OrderId: int64
    Status: string
    OrderRows: (int64 * int64 * int16) []
    Timezone: string }
CustomOrder.OrderId: int64
Multiple items
val int64 : value:'T -> int64 (requires member op_Explicit)

--------------------
type int64 = Int64

--------------------
type int64<'Measure> = int64
CustomOrder.Status: string
Multiple items
val string : value:'T -> string

--------------------
type string = String
CustomOrder.OrderRows: (int64 * int64 * int16) []
Multiple items
val int16 : value:'T -> int16 (requires member op_Explicit)

--------------------
type int16 = Int16

--------------------
type int16<'Measure> = int16
CustomOrder.Timezone: string
val parseTimezoneFunction : region:string * sdate:DateTime * customer:int -> string
val region : string
val sdate : DateTime
Multiple items
type DateTime =
  struct
    new : ticks:int64 -> DateTime + 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
    ...
  end

--------------------
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)
val customer : int
Multiple items
val int : value:'T -> int (requires member op_Explicit)

--------------------
type int = int32

--------------------
type int<'Measure> = int
namespace FSharp.Data.Sql.Common
module QueryEvents

from FSharp.Data.Sql.Common
val SqlQueryEvent : IEvent<Common.QueryEvents.SqlEventData>
Multiple items
module Event

from Microsoft.FSharp.Control

--------------------
type Event<'T> =
  new : unit -> Event<'T>
  member Trigger : arg:'T -> unit
  member Publish : IEvent<'T>

--------------------
type Event<'Delegate,'Args (requires delegate and 'Delegate :> Delegate)> =
  new : unit -> Event<'Delegate,'Args>
  member Trigger : sender:obj * args:'Args -> unit
  member Publish : IEvent<'Delegate,'Args>

--------------------
new : unit -> Event<'T>

--------------------
new : unit -> Event<'Delegate,'Args>
val add : callback:('T -> unit) -> sourceEvent:IEvent<'Del,'T> -> unit (requires delegate and 'Del :> Delegate)
val printfn : format:Printf.TextWriterFormat<'T> -> 'T
type sql = obj
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 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'>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>
                    
type DatabaseProviderTypes =
  | MSSQLSERVER = 0
  | SQLITE = 1
  | POSTGRESQL = 2
  | MYSQL = 3
  | ORACLE = 4
  | MSACCESS = 5
  | ODBC = 6
  | FIREBIRD = 7
  | MSSQLSERVER_DYNAMIC = 8
Common.DatabaseProviderTypes.SQLITE: Common.DatabaseProviderTypes = 1
type CaseSensitivityChange =
  | ORIGINAL = 0
  | TOUPPER = 1
  | TOLOWER = 2
Common.CaseSensitivityChange.ORIGINAL: Common.CaseSensitivityChange = 0
val ctx : obj
val example : Linq.IQueryable<obj>
val query : Linq.QueryBuilder
val order : obj
custom operation: where (bool)

Calls Linq.QueryBuilder.Where
custom operation: sortBy ('Key)

Calls Linq.QueryBuilder.SortBy
custom operation: skip (int)

Calls Linq.QueryBuilder.Skip
custom operation: take (int)

Calls Linq.QueryBuilder.Take
custom operation: select ('Result)

Calls Linq.QueryBuilder.Select
val test : Map<IComparable,obj> []
Multiple items
module Seq

from FSharp.Data.Sql

--------------------
module Seq

from Microsoft.FSharp.Collections
val toArray : source:seq<'T> -> 'T []
type Array =
  member Clone : unit -> obj
  member CopyTo : array:Array * index:int -> unit + 1 overload
  member GetEnumerator : unit -> IEnumerator
  member GetLength : dimension:int -> int
  member GetLongLength : dimension:int -> int64
  member GetLowerBound : dimension:int -> int
  member GetUpperBound : dimension:int -> int
  member GetValue : [<ParamArray>] indices:int[] -> obj + 7 overloads
  member Initialize : unit -> unit
  member IsFixedSize : bool
  ...
val map : mapping:('T -> 'U) -> array:'T [] -> 'U []
val i : obj
Multiple items
module Map

from Microsoft.FSharp.Collections

--------------------
type Map<'Key,'Value (requires comparison)> =
  interface IReadOnlyDictionary<'Key,'Value>
  interface IReadOnlyCollection<KeyValuePair<'Key,'Value>>
  interface IEnumerable
  interface IComparable
  interface IEnumerable<KeyValuePair<'Key,'Value>>
  interface ICollection<KeyValuePair<'Key,'Value>>
  interface IDictionary<'Key,'Value>
  new : elements:seq<'Key * 'Value> -> Map<'Key,'Value>
  member Add : key:'Key * value:'Value -> Map<'Key,'Value>
  member Change : key:'Key * f:('Value option -> 'Value option) -> Map<'Key,'Value>
  ...

--------------------
new : elements:seq<'Key * 'Value> -> Map<'Key,'Value>
val ofSeq : elements:seq<'Key * 'T> -> Map<'Key,'T> (requires comparison)
val item : Linq.IQueryable<obj>
val order : Linq.QuerySource<obj,Linq.IQueryable>
custom operation: head

Calls Linq.QueryBuilder.Head
val exampleAsync : Threading.Tasks.Task<seq<obj>>
val async : AsyncBuilder
val res : seq<obj>
val executeQueryAsync : (Linq.IQueryable<'a> -> Async<seq<'a>>)
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 Choice : computations:seq<Async<'T option>> -> Async<'T option>
  static member FromBeginEnd : beginAction:(AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
  ...

--------------------
type Async<'T> =
static member Async.StartAsTask : computation:Async<'T> * ?taskCreationOptions:Threading.Tasks.TaskCreationOptions * ?cancellationToken:Threading.CancellationToken -> Threading.Tasks.Task<'T>
val itemAsync : Threading.Tasks.Task<obj>
val item : obj
val headAsync : (Linq.IQueryable<'a> -> Async<'a>)
val dc : obj
val cust : obj
module Seq

from Microsoft.FSharp.Collections
val randomBoolean : (unit -> bool)
val r : Random
Multiple items
type Random =
  new : unit -> Random + 1 overload
  member Next : unit -> int + 2 overloads
  member NextBytes : buffer:byte[] -> unit + 1 overload
  member NextDouble : unit -> float

--------------------
Random() : Random
Random(Seed: int) : Random
val c1 : bool
val c2 : bool
val c3 : bool
val sample : (string * obj) []
val x : string
val bergs : obj
module Array

from Microsoft.FSharp.Collections
namespace System.Linq
val getOrders : futureOrders:bool * shipYears:int list -> Async<(int64 * 'a) []>
val futureOrders : bool
type bool = Boolean
val shipYears : int list
type 'T list = List<'T>
val today : DateTime
property DateTime.UtcNow: DateTime with get
val pastOrders : bool
val not : value:bool -> bool
val noYearFilter : bool
val result : Async<(int64 * 'a) []>
val order : CustomOrder
val executeQueryAsync : query:IQueryable<'a> -> Async<'a []>
val selectFullObject : Async<obj option>
val customer : obj
val tryHeadAsync : (IQueryable<'a> -> Async<'a option>)
val selectSmallObject : Async<(obj * obj) option>
val someQuery : obj []
val c : obj
val fetchOrders : customerZone:int -> CustomOrder []
val customerZone : int
val currentDate : DateTime
val orders : CustomOrder []
val orderIds : int64 []
val o : CustomOrder
val distinct : array:'T [] -> 'T [] (requires equality)
val subItems : (int64 * int64 * int16) []
val row : CustomOrder
val ordersWithDetails : CustomOrder []
val filter : predicate:('T -> bool) -> array:'T [] -> 'T []
val orderId : int64
val chunked : int64 [] []
val chunkBySize : chunkSize:int -> array:'T [] -> 'T [] []
val chunk : int64 []
val all : CustomOrder []
val iter : action:('T -> unit) -> array:'T [] -> unit
val nestedOrders : IQueryable<int64>
val subItemsAll : (int64 * int64 * int16) []
val fetchOrders2 : customerZone:int -> CustomOrder []
val freightsByCity : Async<(obj * obj) []>
val o : obj
custom operation: groupBy ('Key)

Calls Linq.QueryBuilder.GroupBy
val cites : IGrouping<obj,obj>
val qry : int * obj
val p : obj
val g : IGrouping<int,obj>
val head : source:seq<'T> -> 'T
Fork me on GitHub