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:

·         Back-end autogenerated

·         I’ve learned JS pretty well

Cons:

·         Plenty of autogenerated code (crap) to debug into when things go wrong

·         Difficult to customize the back-end

·         Somehow slow, even if today’s JS engines are pretty good

·         More time consuming to create and maintain JS code (compared to F# or C#)

·         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)

 

}

 

Pros:

·         Purely functional business logic code (SQL), very easy to debug problems by just running the query/view

·         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 …”

·         Very easy to customize the back-end, just write F# code to implement GET/POST/PUT/DELETE etc…

·         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.

·         No autogenerated code anywhere in the architecture

·         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

Cons:

·       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). 

·        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"