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

--------------------
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. Types used in desing-time: If no better clue, prefer .NET Standard 2.0 versions. Semicolon to separate multiple.</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 }
Multiple items
val int64: value: 'T -> int64 (requires member op_Explicit)

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

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

--------------------
type string = String
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. Types used in desing-time: If no better clue, prefer .NET Standard 2.0 versions. Semicolon to separate multiple.</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 }
val qry: Employee2 seq
val query: Linq.QueryBuilder
val row: SqlDataProvider<...>.dataContext.main.EmployeesEntity
custom operation: select ('Result) Calls Linq.QueryBuilder.Select
Multiple items
module Seq from FSharp.Data.Sql

--------------------
module Seq from Microsoft.FSharp.Collections
val map: mapping: ('T -> 'U) -> source: 'T seq -> 'U seq
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
val qry2: Employee3 seq
member SqlEntity.MapTo: ?propertyTypeMapping: (string * obj -> obj) -> 'a
val rows: SqlDataProvider<...>.dataContext.main.EmployeesEntity array
val toArray: source: 'T seq -> 'T array
val employees2map: Map<string,obj> seq
val i: SqlDataProvider<...>.dataContext.main.EmployeesEntity
property SqlEntity.ColumnValues: (string * obj) seq with get
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 IStructuralEquatable interface IComparable interface IEnumerable<KeyValuePair<'Key,'Value>> interface ICollection<KeyValuePair<'Key,'Value>> interface IDictionary<'Key,'Value> new: elements: ('Key * 'Value) seq -> Map<'Key,'Value> member Add: key: 'Key * value: 'Value -> Map<'Key,'Value> ...

--------------------
new: elements: ('Key * 'Value) seq -> Map<'Key,'Value>
val ofSeq: elements: ('Key * 'T) seq -> Map<'Key,'T> (requires comparison)
val firstNames: obj seq
val x: Map<string,obj>

Type something to start searching.