SQLProvider


Adding a Mapper using dataContext to use generated types from db

This mapper will get sure that you always sync your types with types you receive from your db.

First add an Domain Model

open System

type Employee = {
    EmployeeId : int64
    FirstName : string
    LastName : string
    HireDate : DateTime
}

Then you can create the mapper using dataContext to use generated types from db

let mapEmployee (dbRecord:sql.dataContext.``main.EmployeesEntity``) : Employee =
    { EmployeeId = dbRecord.EmployeeId
      FirstName = dbRecord.FirstName
      LastName = dbRecord.LastName
      HireDate = dbRecord.HireDate }

SqlProvider also has a .MapTo<'T> convenience method:

let ctx = sql.GetDataContext()

let orders = ctx.Main.Orders
let employees = ctx.Main.Employees

type Employee2 = {
    FirstName:string
    LastName:string
    }

let qry = query { for row in employees do
                  select row} |> Seq.map (fun x -> x.MapTo<Employee2>())

The target type can be a record (as in the example) or a class type with properties named as the source columns and with a paremeterless setter. The target field name can also be different than the column name; in this case it must be decorated with the MappedColumnAttribute custom attribute:

open FSharp.Data.Sql.Common

type Employee3 = {
    [<MappedColumn("FirstName")>] GivenName:string
    [<MappedColumn("LastName")>] FamilyName:string
    }

let qry2 = 
          query { 
                for row in employees do
                select row} |> Seq.map (fun x -> x.MapTo<Employee3>())

Or alternatively the ColumnValues from SQLEntity can be used to create a map, with the column as a key:

let rows = 
        query { 
            for row in employees do
            select row} |> Seq.toArray

let employees2map = rows |> Seq.map(fun i -> i.ColumnValues |> Map.ofSeq)
let firstNames = employees2map |> Seq.map (fun x -> x.["FirstName"])
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 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
namespace System
type Employee = { EmployeeId: int64 FirstName: string LastName: string HireDate: DateTime }
Employee.EmployeeId: int64
Multiple items
val int64: value: 'T -> int64 (requires member op_Explicit)
<summary>Converts the argument to signed 64-bit integer. This is a direct conversion for all primitive numeric types. For strings, the input is converted using <c>Int64.Parse()</c> with InvariantCulture settings. Otherwise the operation requires an appropriate static conversion method on the input type.</summary>
<param name="value">The input value.</param>
<returns>The converted int64</returns>
<example id="int64-example"><code lang="fsharp"></code></example>


--------------------
[<Struct>] type int64 = Int64
<summary>An abbreviation for the CLI type <see cref="T:System.Int64" />.</summary>
<category>Basic Types</category>


--------------------
type int64<'Measure> = int64
<summary>The type of 64-bit signed integer numbers, annotated with a unit of measure. The unit of measure is erased in compiled code and when values of this type are analyzed using reflection. The type is representationally equivalent to <see cref="T:System.Int64" />.</summary>
<category>Basic Types with Units of Measure</category>
Employee.FirstName: string
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 = String
<summary>An abbreviation for the CLI type <see cref="T:System.String" />.</summary>
<category>Basic Types</category>
Employee.LastName: string
Employee.HireDate: DateTime
Multiple items
[<Struct>] type DateTime = new: year: int * month: int * day: int -> unit + 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 ...
<summary>Represents an instant in time, typically expressed as a date and time of day.</summary>

--------------------
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 mapEmployee: dbRecord: SqlDataProvider<...>.dataContext.main.EmployeesEntity -> Employee
val dbRecord: SqlDataProvider<...>.dataContext.main.EmployeesEntity
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 ...
property SqlDataProvider<...>.dataContext.main.EmployeesEntity.EmployeeId: int64 with get, set
<summary>EmployeeID: integer</summary>
property SqlDataProvider<...>.dataContext.main.EmployeesEntity.FirstName: string with get, set
<summary>FirstName: nvarchar(10)</summary>
property SqlDataProvider<...>.dataContext.main.EmployeesEntity.LastName: string with get, set
<summary>LastName: nvarchar(20)</summary>
property SqlDataProvider<...>.dataContext.main.EmployeesEntity.HireDate: DateTime with get, set
<summary>HireDate: datetime</summary>
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 orders: SqlDataProvider<...>.dataContext.mainSchema.main.Orders
property SqlDataProvider<...>.dataContext.Main: SqlDataProvider<...>.dataContext.mainSchema with get
property SqlDataProvider<...>.dataContext.mainSchema.Orders: SqlDataProvider<...>.dataContext.mainSchema.main.Orders with get
<summary> The table Orders belonging to schema main</summary>
val employees: SqlDataProvider<...>.dataContext.mainSchema.main.Employees
property SqlDataProvider<...>.dataContext.mainSchema.Employees: SqlDataProvider<...>.dataContext.mainSchema.main.Employees with get
<summary> The table Employees belonging to schema main</summary>
type Employee2 = { FirstName: string LastName: string }
Employee2.FirstName: string
Employee2.LastName: string
val qry: seq<Employee2>
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 row: SqlDataProvider<...>.dataContext.main.EmployeesEntity
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>
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 map: mapping: ('T -> 'U) -> source: seq<'T> -> seq<'U>
<summary>Builds a new collection whose elements are the results of applying the given function to each of the elements of the collection. The given function will be applied as elements are demanded using the <c>MoveNext</c> method on enumerators retrieved from the object.</summary>
<remarks>The returned sequence may be passed between threads safely. However, individual IEnumerator values generated from the returned sequence should not be accessed concurrently.</remarks>
<param name="mapping">A function to transform items from the input sequence.</param>
<param name="source">The input sequence.</param>
<returns>The result sequence.</returns>
<exception cref="T:System.ArgumentNullException">Thrown when the input sequence is null.</exception>
<example id="item-1"><code lang="fsharp"> let inputs = ["a"; "bbb"; "cc"] inputs |&gt; Seq.map (fun x -&gt; x.Length) </code> Evaluates to a sequence yielding the same results as <c>seq { 1; 3; 2 }</c></example>
val x: SqlDataProvider<...>.dataContext.main.EmployeesEntity
member Common.SqlEntity.MapTo: ?propertyTypeMapping: (string * obj -> obj) -> 'a
type Employee3 = { GivenName: string FamilyName: string }
Multiple items
type MappedColumnAttribute = inherit Attribute new: name: string -> MappedColumnAttribute member Name: string

--------------------
new: name: string -> MappedColumnAttribute
Employee3.GivenName: string
Employee3.FamilyName: string
val qry2: seq<Employee3>
member SqlEntity.MapTo: ?propertyTypeMapping: (string * obj -> obj) -> 'a
val rows: SqlDataProvider<...>.dataContext.main.EmployeesEntity[]
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 employees2map: seq<Map<string,obj>>
val i: SqlDataProvider<...>.dataContext.main.EmployeesEntity
property SqlEntity.ColumnValues: seq<string * obj> with get
Multiple items
module Map from Microsoft.FSharp.Collections
<summary>Contains operations for working with values of type <see cref="T:Microsoft.FSharp.Collections.FSharpMap`2" />.</summary>

--------------------
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> ...
<summary>Immutable maps based on binary trees, where keys are ordered by F# generic comparison. By default comparison is the F# structural comparison function or uses implementations of the IComparable interface on key values.</summary>
<remarks>See the <see cref="T:Microsoft.FSharp.Collections.MapModule" /> module for further operations on maps. All members of this class are thread-safe and may be used concurrently from multiple threads.</remarks>


--------------------
new: elements: seq<'Key * 'Value> -> Map<'Key,'Value>
val ofSeq: elements: seq<'Key * 'T> -> Map<'Key,'T> (requires comparison)
<summary>Returns a new map made from the given bindings.</summary>
<param name="elements">The input sequence of key/value pairs.</param>
<returns>The resulting map.</returns>
<example id="ofseq-1"><code lang="fsharp"> let input = seq { (1, "a"); (2, "b") } input |&gt; Map.ofSeq // evaluates to map [(1, "a"); (2, "b")] </code></example>
val firstNames: seq<obj>
val x: Map<string,obj>