Elevator App: Part 1 – Data Layer Using F#
March 11, 2014 2 Comments
At Open Data Day, fellow TRINUGER Elaine Cahill told me about a website where you can get all of the elevator inspection data for the state. It is found here. She went ahead and put the Wake County data onto Socrata. I wanted to look at the entire state so I went to the report page like so:
Unfortunately, when you try and pull down the entire state, you cause a server exception:
So I split the download in half. I then Imported it into Access and then SSISed it into Azure Sql. I then created a project to server the data and I decided to use F# type providers as a replacement for Entity Framework for my ORM. I could either use the SqlEntity TP or the SqlDataConnection TP to access the Sql Database on Azure. Both do not work out of the box.
I could not get SqlDataConnection to work at all. When I hooked it up to a standard connection string in the config file, I got:
So when I copy and paste the connection string into the TP directly, it does make the connection to Azure, but then it comes back with this exception:
Without looking at the source. my guess is that the TP has hard-coded a reference to ‘syscomments’ and alas, Azure does not have that table.
I then headed over to the SlqEntityTP to see if I could have better luck. Fortunately, the SqlEntity does work with both an Azure connection string in the .config file and can make a connection to an Azure database.
The problem I ran into was when I wanted to expose the SqlConnection the the WebAPI project that I wrote in C#. You can not mark SqlEntityTPs as public:
Note that the SqlDataConnection can be marked as public. <sigh>. I marked the SqlEntityTP as internal and then created a POCO to map between the SqlEntity type and a type that can be consumed by the outside world:
I am not happy about writing any of this code. I have 84 lines of code for a single class. I might have well used the code code gen of EF. I could have taken the performance hit and used System.Reflection to map field of the same names (I have done that on other projects) , but that also feels like a hack. In any event, I then added a reference to my F# project in my C# WebAPI project. I did have to add a reference to FSharp.Core in the C# project (which further vexed me), but then I created a couple of GET methods to expose the data:
When I viewed the JSON from a handy browser, it looks like, well, junk:
So now I have to get rid of that random characters (x0040 suffix)– yet a 3rd POCO, this one in C#:
So that gives me that I want…
As a side note, I learned the hard way that the only way to force the SqlEntityTP to update based on a schema change in the DB is to change the connection string in the .config file.
Finally, when I published the WebAPI project to Azure, I got an exception.
<Error><Message>An error has occurred.</Message><ExceptionMessage>Could not load file or assembly 'FSharp.Core, Version=184.108.40.206, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.</ExceptionMessage><ExceptionType>System.IO.FileNotFoundException</ExceptionType><StackTrace> at System.Web.Http.ApiController.<InvokeActionWithExceptionFilters>d__1.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__0.MoveNext()</StackTrace
Turns out you need to not only add a reference to the F# project and FSharp.Core, you have to deploy the .dlls to Azure also. Thanks to hocho on SO for that one.
In conclusion, I love the promise of TPs. I want nothing more than to throw away all of the EF code-gen, .tt files, seeding for code-first nonsense, etc… and replace it with a single line TP. I have done this on a local project, but when I did it with an Azure, things were harder than they should be. Since it is easier to throw hand grenades than catch them, I made a list of the things I want to help the open source FSharp.Data project accomplish in the coming months:
1) SqlDatabaseConnection working with Azure Sql Storage
2) MSAccessConnection needed
3) ActiveDirectoryConnection needed
4) Json and WsdlService ability to handle proxies
5) SqlEntityConnection exposing classes publicly
Regardless of what the open-source community does, MSFT will still have to make a better commitment to F# on Azure, IMHO…