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:

 

image

Unfortunately, when you try and pull down the entire state, you cause a server exception:

 

image

 

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.

SqlDataConnection

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:

image

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:

image

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.

SqlEntity

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:

image

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:

  1. type public Elevator ={
  2.         ID: int
  3.         County: string
  4.         StateId: string
  5.         Type: string
  6.         Operation: string
  7.         Owner: string
  8.         O_Address1: string
  9.         O_Address2: string
  10.         O_City: string
  11.         O_State: string
  12.         O_Zip: string
  13.         User: string
  14.         U_Address1: string
  15.         U_Address2: string
  16.         U_City: string
  17.         U_State: string
  18.         U_Zip: string
  19.         U_Lat: double
  20.         U_Long: double
  21.         Installed: DateTime
  22.         Complied: DateTime
  23.         Capacity: int
  24.         CertStatus: int
  25.         EquipType: string
  26.         Drive: string
  27.         Volts: string
  28.         Speed: int
  29.         FloorTo: string
  30.         FloorFrom: string
  31.         Landing: string
  32.         Entrances: string
  33.         Ropes: string
  34.         RopeSize: string
  35.     }
  36.  
  37. type public DataRepository() =
  38.     let connectionString = ConfigurationManager.ConnectionStrings.["azureData"].ConnectionString;
  39.  
  40.     member public this.GetElevators () =
  41.         SqlConnection.GetDataContext(connectionString).ElevatorData201402
  42.         |> Seq.map(fun x -> this.GetElevatorFromElevatorData(x))
  43.  
  44.     member public this.GetElevator (id: int) =
  45.         SqlConnection.GetDataContext(connectionString).ElevatorData201402
  46.         |> Seq.where(fun x -> x.ID = id)
  47.         |> Seq.map(fun x -> this.GetElevatorFromElevatorData(x))
  48.         |> Seq.head
  49.  
  50.     member internal this.GetElevatorFromElevatorData(elevatorData: SqlConnection.ServiceTypes.ElevatorData201402) =
  51.         let elevator = {ID= elevatorData.ID;
  52.             County=elevatorData.County;
  53.             StateId=elevatorData.StateID;
  54.             Type=elevatorData.Type;
  55.             Operation=elevatorData.Operation;
  56.             Owner=elevatorData.Owner;
  57.             O_Address1=elevatorData.O_Address1;
  58.             O_Address2=elevatorData.O_Address2;
  59.             O_City=elevatorData.O_City;
  60.             O_State=elevatorData.O_St;
  61.             O_Zip=elevatorData.O_Zip;
  62.             User=elevatorData.User;
  63.             U_Address1=elevatorData.U_Address1;
  64.             U_Address2=elevatorData.U_Address2;
  65.             U_City=elevatorData.U_City;
  66.             U_State=elevatorData.U_St;
  67.             U_Zip=elevatorData.U_Zip;
  68.             U_Lat=elevatorData.U_lat;
  69.             U_Long=elevatorData.U_long;
  70.             Installed=elevatorData.Installed.Value;
  71.             Complied=elevatorData.Complied.Value;
  72.             Capacity=elevatorData.Capacity.Value;
  73.             CertStatus=elevatorData.CertStatus.Value;
  74.             EquipType=elevatorData.EquipType;
  75.             Drive=elevatorData.Drive;
  76.             Volts=elevatorData.Volts;
  77.             Speed=int elevatorData.Speed;
  78.             FloorTo=elevatorData.FloorTo;
  79.             FloorFrom=elevatorData.FloorFrom;
  80.             Landing=elevatorData.Landing;
  81.             Entrances=elevatorData.Entrances;
  82.             Ropes=elevatorData.Ropes;
  83.             RopeSize=elevatorData.RopeSize
  84.         }
  85.         elevator

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:

 

  1. public class ElevatorController : ApiController
  2. {
  3.     // GET api/Elevator
  4.     public IEnumerable<Elevator> Get()
  5.     {
  6.         DataRepository repository = new DataRepository();
  7.         return repository.GetElevators();
  8.     }
  9.  
  10.     // GET api/Elevator/5
  11.     public Elevator Get(int id)
  12.     {
  13.         DataRepository repository = new DataRepository();
  14.         return repository.GetElevator(id);
  15.     }
  16.  
  17. }

 

When I viewed the JSON from a handy browser, it looks like, well, junk:

image

So now I have to get rid of that random characters (x0040 suffix)– yet a 3rd POCO, this one in C#:

  1. public class ElevatorController : ApiController
  2. {
  3.     // GET api/Elevator
  4.     public IEnumerable<CS.Elevator> Get()
  5.     {
  6.         List<CS.Elevator> elevators = new List<CS.Elevator>();
  7.         FS.DataRepository repository = new FS.DataRepository();
  8.         var fsElevators = repository.GetElevators();
  9.         foreach (var fsElevator in fsElevators)
  10.         {
  11.             elevators.Add(GetElevatorFromFSharpElevator(fsElevator));
  12.         }
  13.         return elevators;
  14.     }
  15.  
  16.     // GET api/Elevator/5
  17.     public CS.Elevator Get(int id)
  18.     {
  19.         FS.DataRepository repository = new FS.DataRepository();
  20.         return GetElevatorFromFSharpElevator(repository.GetElevator(id));
  21.     }
  22.  
  23.     internal CS.Elevator GetElevatorFromFSharpElevator(FS.Elevator fsElevator)
  24.     {
  25.         CS.Elevator elevator = new CS.Elevator();
  26.         elevator.ID = fsElevator.ID;
  27.         elevator.County = fsElevator.County;
  28.         elevator.StateId = fsElevator.StateId;
  29.         elevator.Type = fsElevator.Type;
  30.         elevator.Operation = fsElevator.Operation;
  31.         elevator.Owner = fsElevator.Owner;
  32.         elevator.O_Address1 = fsElevator.O_Address1;
  33.         elevator.O_Address2 = fsElevator.O_Address2;
  34.         elevator.O_City = fsElevator.O_City;
  35.         elevator.O_State = fsElevator.O_State;
  36.         elevator.O_Zip = fsElevator.O_Zip;
  37.         elevator.User = fsElevator.User;
  38.         elevator.U_Address1 = fsElevator.U_Address1;
  39.         elevator.U_Address2 = fsElevator.U_Address2;
  40.         elevator.U_City = fsElevator.U_City;
  41.         elevator.U_State = fsElevator.U_State;
  42.         elevator.U_Zip = fsElevator.U_Zip;
  43.         elevator.Installed = fsElevator.Installed;
  44.         elevator.Complied = fsElevator.Complied;
  45.         elevator.Capacity = fsElevator.Capacity;
  46.         elevator.CertStatus = fsElevator.CertStatus;
  47.         elevator.EquipType = fsElevator.EquipType;
  48.         elevator.Drive = fsElevator.Drive;
  49.         elevator.Volts = fsElevator.Volts;
  50.         elevator.Speed = fsElevator.Speed;
  51.         elevator.FloorTo = fsElevator.FloorTo;
  52.         elevator.FloorFrom = fsElevator.FloorFrom;
  53.         elevator.Landing = fsElevator.Landing;
  54.         elevator.Entrances = fsElevator.Entrances;
  55.         elevator.Ropes = fsElevator.Ropes;
  56.         elevator.RopeSize = fsElevator.RopeSize;
  57.         return elevator;
  58.     }
  59.  
  60. }

 

So that gives me that I want…

image

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=4.3.1.0, 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…

Filed under F#, Open Data

2 Responses to Elevator App: Part 1 – Data Layer Using F#

  1. Pingback: F# Weekly #11, 2014 | Sergey Tihon's Blog

  2. Paul Schrum says:
    March 19, 2014 at 6:32 pm

    Re “I could not get SqlDataConnection to work at all”, I think that problem may be essentially the same as the one I was having with the cvs TP, which I get an anser to here:
    http://stackoverflow.com/questions/21963487/preserving-field-names-across-the-f-c-boundary

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s

%d bloggers like this: