Adding a Mapper using dataContext to use generated types from db
Typically F# is about writing business logic and not about OR-mapping. Consider using your database types as is. But sometimes you want to map objects to different, for example to interact with other languages like C# domain.
This mapper will ensure that you always sync your types with those you receive from your DB.
First, add a 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 parameterless 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"])
type LiteralAttribute = inherit Attribute new: unit -> LiteralAttribute
--------------------
new: unit -> LiteralAttribute
namespace FSharp
--------------------
namespace Microsoft.FSharp
namespace FSharp.Data
--------------------
namespace Microsoft.FSharp.Data
<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>
<summary> .NET Framework default </summary>
val int64: value: 'T -> int64 (requires member op_Explicit)
--------------------
type int64 = Int64
--------------------
type int64<'Measure> = int64
val string: value: 'T -> string
--------------------
type string = String
[<Struct>] type DateTime = new: date: DateOnly * time: TimeOnly -> unit + 16 overloads member Add: value: TimeSpan -> DateTime member AddDays: value: float -> DateTime member AddHours: value: float -> DateTime member AddMicroseconds: 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 ...
<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(date: DateOnly, time: TimeOnly) : DateTime
(+0 other overloads)
DateTime(ticks: int64, kind: DateTimeKind) : DateTime
(+0 other overloads)
DateTime(date: DateOnly, time: TimeOnly, 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)
Use dataContext to explore database schema and querying data. It will carry database-connection and possible modifications within transaction, that you can commit via SubmitUpdates.
<summary>EmployeeID: integer</summary>
<summary>FirstName: nvarchar(10)</summary>
<summary>LastName: nvarchar(20)</summary>
<summary>HireDate: datetime</summary>
<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)
<summary> The table Orders belonging to schema main</summary>
<summary> The table Employees belonging to schema main</summary>
module Seq from FSharp.Data.Sql
--------------------
module Seq from Microsoft.FSharp.Collections
type MappedColumnAttribute = inherit Attribute new: name: string -> MappedColumnAttribute member Name: string
--------------------
new: name: string -> MappedColumnAttribute
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>