Retrieving SQL Server data with type providers and exposing it with ASP.NET Web APIs in F# - Luca Bolognese

Retrieving SQL Server data with type providers and exposing it with ASP.NET Web APIs in F#

Luca -

☕ 6 min. read

For a good in­tro­duc­tion on how to use Web APIs in F#, read here. The start­ing point for type providers is here. This post is about how I solved a prac­ti­cal prob­lem us­ing these tech­nolo­gies.

First, let’s dis­cuss the sce­nario. In my com­pany, we needed to log us­age in­for­ma­tion for our var­i­ous ap­pli­ca­tions to a cen­tral repos­i­tory and build a web site to ac­cess such in­for­ma­tion. I went through three dif­fer­ent ar­chi­tec­tures for such a re­quire­ment, end­ing with the set of tech­nolo­gies de­scribed in the ti­tle.

I found the in­ter­sec­tion of the SQL type provider with the ASP.NET Web Api to be very sweet. Personally, I think this mix is much bet­ter than us­ing the WFC Data Services, be­cause the code you have to write and the in­fra­struc­ture to main­tain is sig­nif­i­cantly less.

I sus­pect that the F# team and ASP.Net team did­n’t talk to each other. It all hap­pens be­cause of a well de­fined in­ter­face (IQueryable) that both teams hap­pen to work against.

1st ver­sion, heavy Javascript

  1. SQL Server Express back­end
  2. WFC Data Services mid­dle tier in C# (autogenerated REST ser­vice from a table, you can query from the JS code)
  3. Plenty of Javascript in the browser, call­ing back to the REST web ser­vices through Ajax

Here is an ex­am­ple of the kind of ma­nip­u­la­tion done in the JS layer:

function extractVersions(sessions) {
var versions = {}
$.each(sessions, function (i, s) {
if (typeof versions[s.Version] == 'undefined') {
versions[s.Version] = 0
} else {
versions[s.Version] = versions[s.Version] + 1

var vs = []
for (var prop in versions) {
if (versions.hasOwnProperty(prop))
vs.push({ Version: prop, Sessions: versions[prop] })
return vs;


  1. Back-end au­to­gen­er­ated
  2. I’ve learned JS pretty well


  1. Plenty of au­to­gen­er­ated code (crap) to de­bug into when things go wrong
  2. Difficult to cus­tomize the back-end
  3. Somehow slow, even if to­day’s JS en­gines are pretty good
  4. More time con­sum­ing to cre­ate and main­tain JS code (compared to F# or C#)
  5. Aesthetically un­sat­is­fy­ing, as you re­ally would like to do most ma­nip­u­la­tions on the server

2nd ver­sion, SOAP like web ser­vices

  1. SQL Server Express back­end
  2. SOAP like web ser­vices mid­dle tier in C# (coded by hand), still us­ing the WCF Data Services in­fra­struc­ture
  3. Little Javascript in the browser ex­clu­sively for pre­sen­ta­tion, call­ing back to the REST web ser­vices through Ajax

Here is how one of the web ser­vices would look like:

public IEnumerable<VersionSessions> GetVersionsForMonth(string application,
bool isQS, bool isAD)
var sessions = GetSessionsForMonth(application, isQS, isAD);
var hash = new Dictionary<string, VersionSessions>();

foreach (var s in sessions)
VersionSessions vs;
var found = hash.TryGetValue(s.Version, out vs);

if (!found)
hash[s.Version] = new VersionSessions { Version = s.Version,
Sessions = 0 };
vs.Sessions += 1;
return hash.Values;


  1. Maintainable busi­ness logic code (C# in­stead of JS)
  2. Somehow faster
  3. Easier to cus­tomize the back-end (just add an­other web ser­vice with the sig­na­ture you want)


  1. Still plenty of ma­nip­u­la­tion code (at least now on the back-end), not very RESTy
  2. Feels that the web­ser­vice are very ad-hoc for this par­tic­u­lar pre­sen­ta­tion (i.e. a ViewModel, not a model)
  3. Still not triv­ial to cus­tomize the back-end logic, mostly be­cause I was us­ing WCF Data Services, which are opaque

3rd ver­sion, us­ing SQL Views and F# type providers

  1. Moved the whole app to a ded­i­cated VM
  2. SQL Server back­end with a sig­nif­i­cant layer of Views and in­line table-value func­tions
  3. Proper REST web ser­vices cre­ated through ASP.NET Web API and F# type providers
  4. Little Javascript in the browser ex­clu­sively for pre­sen­ta­tion, call­ing back to the REST web ser­vices through Ajax
  5. Moved all ex­ter­nal reference file’ (i.e. peo­ple in QS) to the DB. Before I was keep­ing busi­ness info in the data­base and con­fig info in files.

Here is an ex­am­ple of an in­line table value func­tion:

ALTER FUNCTION [dbo].[FUsers] (@App nvarchar(40))
SELECT UserAlias, MAX(Application) AS Application, MAX(Start) AS LastSessionStart, MAX(Finish) AS LastSessionFinish, DATEDIFF(MINUTE, MAX(Start), MAX(Finish)) AS LastSessionTotalTime, COUNT(*) AS Sessions, MAX(Machine) AS Machine, MAX(Version) AS Version, MAX(Qs) AS Qs
FROM dbo.VTotalSessions AS s
WHERE (TotalTime > 30) and (Application = @App)
GROUP BY UserAlias)

And here is an ex­am­ple of a type provider based web ser­vice. Most of my code works like this:

type usersController() =
inherit ApiController()

let db = dbSchema.GetDataContext()

member x.Get() =
query {
for u in db.VUsers do
select u

member x.Get(app) =
query {
for u in db.FUsers(app) do
select u

member x.Post (value:string) = ()
member x.Put (id:int) (value:string) = ()
member x.Delete (id:int) = ()

Here is how a query is rep­re­sented in JS (the whole query gets passed through to SQL, both the JS part and the F# part, IQueryable mag­ick):

function loadOpenSessions(app, qs,  cont) {

var query = new oQuery()
.From("/users/" + app)
.Let("lastMonth", lastMonth)
.Let("twoMinutesAgo", twoMinutesAgo)
.Where("item => item.LastSessionFinish > $twoMinutesAgo")
commonCall(query, qs, cont)


  1. Purely func­tional busi­ness logic code (SQL), very easy to de­bug prob­lems by just run­ning the query/​view
  2. Maximum us­age of SQL Server op­ti­mizer. As they say in the SQL Engine Team: we’ve spent decades op­ti­miz­ing sql query en­gines. You are un­likely to do bet­ter with your for’ loops …”
  3. Very easy to cus­tomize the back-end, just write F# code to im­ple­ment GET/POST/PUT/DELETE etc…
  4. Moving all the state for the app (reference files in­cluded) in the DB, makes it much eas­ier to in­te­grate it into the busi­ness logic. It all be­comes a big query for the op­ti­mizer to sort out.
  5. No au­to­gen­er­ated code any­where in the ar­chi­tec­ture
  6. More sep­a­ra­tion be­tween model and view­model. Tables are model, Views are ViewModel, F# is just a way to ex­pose such view model to the world at large


  1. Routing al­go­rithm for ASP.NET Web API is mys­te­ri­ous. It’s the only black box piece in the ar­chi­tec­ture (aka that I don’t un­der­stand it).
  2. Sometimes ei­ther SQL is not pow­er­ful enough (I ab­hor Stored pro­ce­dures on re­li­gious grounds) or some­thing does­n’t map well to a REST metaphor. In such cases I have di­luted the metaphor as in the code be­low. The good thing is that, be­ing just code, I can do that. I’m not blocked.
type statsController() =

inherit ApiController()

let db = dbSchema.GetDataContext()

member x.Get(app:string, isQs:string, call:string) =
match call with
| "WeeklyUsers" ->
let sessions = query {
for s in db.VTotalSessionsAndWeeks do
where (s.Application = app && s.Qs = Nullable (Int32.Parse(isQs)))
select s
weeklyUsers sessions
| "WeeklyTime" ->
let sessions = query {
for s in db.FTotalTime(app, Nullable (Int32.Parse(isQs))) do
select s
weeklyTime sessions
| "HourlyUsers"->
let users = query {
for u in db.FHourlyUsers(app, Nullable (Int32.Parse(isQs))) do
select u }
hourlyUsers users

| _ -> failwith "No call with that name"


Hi. You could do even better with WebSharper ;) I recommend it from my own experience - it is pure F# - no need to know ASP.NET Web API, REST...

I'm sure it is lovely. I am a bit scared by the amount of magic involved, but I'm sure I'll get over it.

0 Webmentions

These are webmentions via the IndieWeb and