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

SQL Server Express backend
WFC Data Services middle tier in C# (autogenerated REST service from a table, you can query from the JS code)
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;
}

Pros: 1. Back-end autogenerated 1. I’ve learned JS pretty well

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

2nd version, SOAP like web services

SQL Server Express backend
SOAP like web services middle tier in C# (coded by hand), still using the WCF Data Services infrastructure
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:

        [WebGet]
        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 };
                else
                    vs.Sessions += 1;
            }
            return hash.Values;
        }

Pros:

Maintainable business logic code (C# instead of JS)
Somehow faster
Easier to customize the back-end (just add another web service with the signature you want)

Cons:

Still plenty of manipulation code (at least now on the back-end), not very RESTy
Feels that the webservice are very ad-hoc for this particular presentation (i.e. a ViewModel, not a model)
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

Moved the whole app to a dedicated VM
SQL Server backend with a significant layer of Views and inline table-value functions
Proper REST web services created through ASP.NET Web API and F# type providers
Little Javascript in the browser exclusively for presentation, calling back to the REST web services through Ajax
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))
RETURNS TABLE
AS
RETURN
(
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")
        .Orderby("UserAlias")
    commonCall(query, qs, cont)
}