~~ Offline ~~ theme Menu

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

For a good introduction on how to use Web APIs in F#, read here. The starting point for type providers is here. This post is about how I solved a practical problem using these technologies.

First, let’s discuss the scenario. In my company, we needed to log usage information for our various applications to a central repository and build a web site to access such information. I went through three different architectures for such a requirement, ending with the set of technologies described in the title.

I found the intersection of the SQL type provider with the ASP.NET Web Api to be very sweet. Personally, I think this mix is much better than using the WFC Data Services, because the code you have to write and the infrastructure to maintain is significantly less.

I suspect that the F# team and ASP.Net team didn’t talk to each other. It all happens because of a well defined interface (IQueryable) that both teams happen to work against.

1st version, heavy Javascript

  1.  SQL Server Express backend
  2.  WFC Data Services middle tier in C# (autogenerated REST service from a table, you can query from the JS code)
  3.  Plenty of Javascript in the browser, calling back to the REST web services through Ajax

Here is an example of the kind of manipulation 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 autogenerated
  2.          I’ve learned JS pretty well


  1.          Plenty of autogenerated code (crap) to debug into when things go wrong
  2.          Difficult to customize the back-end
  3.          Somehow slow, even if today’s JS engines are pretty good
  4.          More time consuming to create and maintain JS code (compared to F# or C#)
  5.          Aesthetically unsatisfying, as you really would like to do most manipulations on the server


2nd version, SOAP like web services

  1.          SQL Server Express backend
  2.          SOAP like web services middle tier in C# (coded by hand), still using the WCF Data Services infrastructure
  3.          Little Javascript in the browser exclusively for presentation, calling back to the REST web services through Ajax

Here is how one of the web services 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 business logic code (C# instead of JS)
  2.          Somehow faster
  3.          Easier to customize the back-end (just add another web service with the signature you want)


  1.          Still plenty of manipulation code (at least now on the back-end), not very RESTy
  2.          Feels that the webservice are very ad-hoc for this particular presentation (i.e. a ViewModel, not a model)
  3.          Still not trivial to customize the back-end logic, mostly because I was using WCF Data Services, which are opaque


3rd version, using SQL Views and F# type providers

  1.          Moved the whole app to a dedicated VM
  2.          SQL Server backend with a significant layer of Views and inline table-value functions
  3.          Proper REST web services created through ASP.NET Web API and F# type providers
  4.          Little Javascript in the browser exclusively for presentation, calling back to the REST web services through Ajax
  5.          Moved all external ‘reference file’ (i.e. people in QS) to the DB. Before I was keeping business info in the database and config info in files.


Here is an example of an inline table value function:


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 example of a type provider based web service. 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 represented in JS (the whole query gets passed through to SQL, both the JS part and the F# part, IQueryable magick):


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 functional business logic code (SQL), very easy to debug problems by just running the query/view
  2.          Maximum usage of SQL Server optimizer. As they say in the SQL Engine Team: “we’ve spent decades optimizing sql query engines. You are unlikely to do better with your ‘for’ loops …”
  3.          Very easy to customize the back-end, just write F# code to implement GET/POST/PUT/DELETE etc…
  4.          Moving all the state for the app (reference files included) in the DB, makes it much easier to integrate it into the business logic. It all becomes a big query for the optimizer to sort out.
  5.          No autogenerated code anywhere in the architecture
  6.          More separation between model and viewmodel. Tables are model, Views are ViewModel, F# is just a way to expose such view model to the world at large


  1.        Routing algorithm for ASP.NET Web API is mysterious. It’s the only black box piece in the architecture (aka that I don’t understand it). 
  2.         Sometimes either SQL is not powerful enough (I abhor Stored procedures on religious grounds) or something doesn’t map well to a REST metaphor. In such cases I have diluted the metaphor as in the code below. The good thing is that, being 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"