F# SQLProvider: In Action

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