Intro
There’s an F# library that provides an ORM experience that is arguably superior to Entity Framework. This library is the SqlProvider.
Establishing Connection
The following code establishes a connection to SQL Server:
namespace DevOps.DataGateway
open FSharp.Data.Sql
module SqlConnection =
//----------------------------------------------------------------------------------------------------
// UPDATE CONNECTION STRING !!!
//----------------------------------------------------------------------------------------------------
[<Literal>]
let string = "Data Source=MY_MACHINE_NAME\SqlExpress;Initial Catalog=DevOps;Integrated Security=True"
//----------------------------------------------------------------------------------------------------
type private sql = SqlDataProvider<Common.DatabaseProviderTypes.MSSQLSERVER,
string,
CaseSensitivityChange = Common.CaseSensitivityChange.ORIGINAL>
type Context = sql.dataContext
let connectToDatabase =
try Ok <| sql.GetDataContext(string)
with ex -> Error <| ex.GetBaseException().Message
Queries
A Query module can be established as a foundation that could enable clients to filter on.
The following is a Query module:
open SqlConnection
module Query =
let applicant () =
match connectToDatabase with
| Error msg -> failwith msg
| Ok ctx ->
query { for v in ctx.Dbo.Applicant do
select (v.ApplicantId,
v.SaasId ,
v.RoleId ,
v.Name ,
v.Location ,
v.Submitted)
}
The following code depicts a client query that applies filters on its foundational query:
query { for (applicantId, applicantSaasId, applicantRoleId, applicantName, orgin, created) in Query.applicant() do
join (roleName,roleId) in Query.roles() on (applicantRoleId = roleId)
join (sassName, saasId, sassDesc) in Query.saas() on (applicantSaasId = saasId)
where (roleName.ToLower() = request.Role.ToLower())
select (applicantId, sassName, applicantName, roleName, orgin, created)
} |> Seq.map toApplicant
The data conversion function for toApplicant is below:
let toApplicant (userId:int, saasName, userName, role, orgin, created) : Applicant =
{ ApplicantId = userId.ToString()
Role = role
ApplicantName = userName
SaasName = saasName
Orgin = orgin |> toCoordinate
Submitted = created
}
Appendix
The following code demonstrates F#’s SqlProvider.
Query
namespace DevOps.DataGateway
open SqlConnection
module Query =
let applicant () =
match connectToDatabase with
| Error msg -> failwith msg
| Ok ctx ->
query { for v in ctx.Dbo.Applicant do
select (v.ApplicantId,
v.SaasId ,
v.RoleId ,
v.Name ,
v.Location ,
v.Submitted)
}
let applicantStatus() =
match connectToDatabase with
| Error msg -> failwith msg
| Ok ctx ->
query { for v in ctx.Dbo.ApplicantStatus do
select (v.ApplicantStatusId,
v.ApplicantId ,
v.Status ,
v.Timestamp)
}
let user () =
match connectToDatabase with
| Error msg -> failwith msg
| Ok ctx ->
query { for v in ctx.Dbo.AuthorizedUser do
select (v.UserId,
v.RoleId,
v.Name ,
v.Created)
}
let features () =
match connectToDatabase with
| Error msg -> failwith msg
| Ok ctx ->
query { for v in ctx.Dbo.Feature do
select (v.Name,v.FeatureId)
}
let userEnabledFeatures () =
match connectToDatabase with
| Error msg -> failwith msg
| Ok ctx ->
query { for v in ctx.Dbo.FeatureEnabled do
select (v.FeatureEnabledId, v.UserId, v.FeatureId)
}
let serviceKeys () =
match connectToDatabase with
| Error msg -> failwith msg
| Ok ctx ->
query { for v in ctx.Dbo.ServiceKey do
select (v.ServiceKeyId, v.ServiceName, v.ApiKey)
}
let roles () =
match connectToDatabase with
| Error msg -> failwith msg
| Ok ctx ->
query { for v in ctx.Dbo.Role do
select (v.Name,v.RoleId)
}
let featureEnabled () =
match connectToDatabase with
| Error msg -> failwith msg
| Ok ctx ->
query { for v in ctx.Dbo.FeatureEnabled do
select (v.FeatureEnabledId,v.UserId,v.FeatureId)
}
let saas () =
match connectToDatabase with
| Error msg -> failwith msg
| Ok ctx ->
query { for v in ctx.Dbo.Saas do
select (v.Name,v.SaasId,v.Description)
}
Applicant
namespace DevOps.DataGateway
open System
open DevOps.DataTransfer
open DevOps.Authorized
open DevOps.Applicant
open SqlConnection
module Applicant =
let apply : Operations.Apply =
fun application ->
async {
try
match connectToDatabase with
| Error msg -> return Error msg
| Ok ctx ->
let location = application.Location
let roleMap =
query { for (featureName,featureId) in Query.roles() do
select (featureName,featureId)
} |> Map.ofSeq
let saasMap =
query { for (saasName,sassId,_) in Query.saas() do
select (saasName,sassId)
} |> Map.ofSeq
let applicant = ctx.Dbo.Applicant.Create()
applicant.SaasId <- saasMap.[application.Name]
applicant.RoleId <- roleMap.[application.Role]
applicant.Name <- application.Name
applicant.Location <- $"{location.Latitude},{location.Longitude}"
applicant.Submitted <- DateTime.UtcNow
ctx.SubmitUpdates()
let applicantStatus = ctx.Dbo.ApplicantStatus.Create()
applicantStatus.ApplicantId <- applicant.ApplicantId
applicantStatus.Status <- "pending"
applicantStatus.Timestamp <- applicant.Submitted
ctx.SubmitUpdates()
return Ok { Application= application; Timestamp= DateTime.UtcNow }
with ex -> return Error <| ex.GetBaseException().Message
} |> Async.StartAsTask