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;
}

Pros:

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

Cons:

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:

        [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:

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)

Cons:

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))
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 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")
        .Orderby("UserAlias")
    commonCall(query, qs, cont)
}

Pros:

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

Cons:

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"