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

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 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 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 autogenerated
2. I’ve learned JS pretty well

Cons:

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

Cons:

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

Cons:

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"

Tags

• FSHARP

2 Comments

Comments

tomas.k

2012-09-04T16:09:41Z

Hi. You could do even bet­ter with WebSharper ;) I rec­om­mend it from my own ex­pe­ri­ence - it is pure F# - no need to know ASP.NET Web API, REST…
Cheers,
Tomas

lucabol

2012-09-04T16:59:16Z

I’m sure it is lovely. I am a bit scared by the amount of magic in­volved, but I’m sure I’ll get over it.