Setting up an OData Service on WebAPI2 to be used by F# Type Providers

I am prepping for the F#/Data Analytics workshop on January 8th and wanted to get the data that I used for the Road Alert application beck to better shape.  By better, I mean out of that crusty WCF SOAP that I have had it in for the last 2 years.  To that end, I jumped over to Mike Wasson’s Creating an OData tutorial.  All in all, it is a good step by step guide, but I had to make some changes to get it working for me.

Change #1 is that I am not using a local database, I am using a database located on WinHost.  Therefore, I had to swap out the EF connection string.


One of the things I can appreciate about the template is the comments to get the routing set up (I guess there is not attribute-based routing for O-Data?)

  1.         /*
  2. To add a route for this controller, merge these statements into the Register method of the WebApiConfig class. Note that OData URLs are case sensitive.
  4. using System.Web.Http.OData.Builder;
  5. using ChkickenSoftware.RoadAlertServices.Models;
  6. ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
  7. builder.EntitySet<TrafficStop>("TrafficStop");
  8. config.Routes.MapODataRoute("odata", "odata", builder.GetEdmModel());
  9. */

Things were looking good when I took a departure from the tutorial and added in a couple of unit tests (Change #2).  The 1st one was fairly benign:

  1. [TestClass]
  2. public class TrafficStopControllerIntegrationTests
  3. {
  4.     [TestMethod]
  5.     public void GetTrafficStopUsingKey_ReturnsExpected()
  6.     {
  7.         TrafficStopController controller = new TrafficStopController();
  8.         var trafficStop = controller.GetTrafficStop(1);
  9.         Assert.IsNotNull(trafficStop);
  10.     }
  11. }

Note that I had to add an app.config to the test project b/c this is an integration test and I am making a real database call – a unit test would using a mocking framework.  In any event, when I went to run the test, I got a compile error – I needed to add a reference to System.Web.Http.OData to resolve the return value from the controller.  Not big thing, though I wish I could install packages from Nuget via their .dll name and not just their package name:


In any event, I then ran the test and I got this exception:


So this is another reason why EF drives me nuts.  I have to add a reference to Entity Framework (and throw some crap in the .config file)

  1. <entityFramework>
  2.   <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
  3.   <providers>
  4.     <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
  5.   </providers>
  6. </entityFramework>

– even thought the calling application has nothing to do with EF.  In 2014, we have such dependency drip?  Really?  In any event, once I added a reference to EF and updated the .config file, my unit/integration test ran green so I was on the right track.

I then went to fiddler and tried to call the controller:


Yikes, it looks like my model has to match the EF exactly

The database:


And the model:

  1. public class TrafficStop
  2. {
  3.     public Int32 Id { get; set; }
  4.     public double CadCallId { get; set; }
  5.     public DateTime StopDateTime { get; set; }
  6.     public Int32 DispositionId { get; set; }
  7.     public String DispositionDesc { get; set; }
  8.     public double Latitude { get; set; }
  9.     public double Longitude { get; set; }
  10. }


– I assume that I should be able to override this behavior – another thing to research.

So after matching up field names, I ran fiddler and sure enough:


So that was pretty painless to get an OData Service up and running.  I then removed everything but the read methods and I added an auth header (you can see the value in the screen shot above), feel free to hit up the service now that it is deployed to WinHost:

    One of the coolest things about OData is that it has a .WSDL type discovery:$metadata

I was really missing that when we went from SOAP Services to REST

Note that I had to do a couple of more things in Tsql (remember that?) to the original data to get it ready for general consumption (and analytics).  I had to create a real date/time from the 2 varchar fields:

Update [XXXX].[dbo].[TrafficStops]
Set StopDateTime = Convert(DateTime, right (left([Date],6),2) + ‘/’ + right([Date],2) + ‘/’ + left([Date],4) + ‘ ‘ + left(Time,2) + ‘:’ + Right(left(Time,4),2) + ‘:’ + Right(left(Time,6),2))


I also had to add an integral value for when we do statistical analysis:

Update [XXXXX].[dbo].[TrafficStops]
Set dispositionId =
     WHEN dispositionDesc = ‘UNABLE TO LOCATE’ THEN 2
     WHEN dispositionDesc = ‘FALSE ALARM’ THEN 3
     WHEN dispositionDesc = ‘WRITTEN WARNING’ THEN 4
     WHEN dispositionDesc = ‘OTHER    SEE NOTES’ THEN 5
     WHEN dispositionDesc = ‘REFERRED TO PROPER AGENCY’ THEN 6
     WHEN dispositionDesc = ‘VERBAL WARNING’ THEN 7
     WHEN dispositionDesc = ‘NULL’ THEN 8
     WHEN dispositionDesc = ‘ARREST’ THEN 9
     WHEN dispositionDesc = ‘CIVIL PROBLEM’ THEN 11
     WHEN dispositionDesc = ‘COMPLETED AS REQUESTED’ THEN 12
     WHEN dispositionDesc = ‘INCIDENT REPORT’ THEN 13
     WHEN dispositionDesc = ‘UNFOUNDED’ THEN 14
     WHEN dispositionDesc = ‘CITATION’ THEN 15
     WHEN dispositionDesc = ‘FIELD CONTACT’ THEN 16
     WHEN dispositionDesc = ‘BACK UP UNIT’ THEN 17
     WHEN dispositionDesc = ‘CITY ORDINANCE VIOLATION’ THEN 18

So now I am ready to roll with doing the analytics.

So when I say “ready to roll”, I really meant to say “ready to flail.” When we last left the show, I was ready to start consuming the data from OData using the F# type providers.   Using Fiddler, I can see the data coming out of the OData service


The problem started when I went to consume the data using the F# OData Type Provider as documented here.  I got the red squiggly line of approbation when I went to create the type:


with the following message:

Error    1    The type provider ‘Microsoft.FSharp.Data.TypeProviders.DesignTime.DataProviders’ reported an error: error 7001: The element ‘DataService’ has an attribute ‘DataServiceVersion’ with an unrecognized version ‘3.0’.   


I went over to the F#-open source Google group to seek help and Isaac Abraham had this response:

WebAPI 2 now pushes out OData 3 endpoints by default, which are actually not even backwards compatible with the OData 2 standard. OData 3 was (AFAIK) released some time after the OData Type Provider was written, so I suspect it doesn’t support OData 3.

So I am stuck.  I really want to use type providers but they are behind.  I thought about if I could downgrade my WebAPI2 OData to go to OData2 standard (whatever that is).

My 1st thought was to trick out the client by removing the DataServiceVersion header like so:

  1. public class HeadersHandler : DelegatingHandler
  2. {
  3.     async protected override Task<HttpResponseMessage> SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
  4.     {
  5.         HttpResponseMessage response = await base.SendAsync(request, cancellationToken);
  7.         response.Content.Headers.Remove("DataServiceVersion");
  8.         return response;
  9.     }
  11. }

The header was removed, but alas, the RSLA is still with me with the same message.  I then thought, perhaps I can go back to the old version of Json so I modified the header like so:

  1. public class HeadersHandler : DelegatingHandler
  2. {
  3.     async protected override Task<HttpResponseMessage> SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
  4.     {
  5.         request.Headers.Add("Accept", "application/json;odata=verbose");
  7.         HttpResponseMessage response = await base.SendAsync(request, cancellationToken);
  9.         response.Content.Headers.Remove("DataServiceVersion");
  10.         return response;
  11.     }
  13. }

So the Json is now the “old” version, but I am still getting the RSLA.  I then ran fiddler when creating the type provider and I see this:


Crap.  I need to have Entity Framework use a lower version (I am using EF 6.0).  I guess?  My 1st thought was to remove EF from the situation entirely, which is always a good idea.  My next, and more time-efficient, thought was to ask Stack Overflow – which is what I did here.  While I wait for Stack Overflow to come to the rescue. I decided to press on.  I just exposed the data via a normal controller like so:

  1. public class TrafficStopSearchController : ApiController
  2. {
  3.     public List<TrafficStop> Get()
  4.     {
  5.         DataContext context = new DataContext();
  6.         return context.TrafficStops.ToList<TrafficStop>();
  7.     }
  8.     public TrafficStop Get(int id)
  9.     {
  10.         DataContext context = new DataContext();
  11.         return context.TrafficStops.Where(ts => ts.Id == id).FirstOrDefault();
  12.     }
  14.     [HttpGet]
  15.     [Route("api/TrafficStopSearch/Sample/")]
  16.     public List<TrafficStop> Sample()
  17.     {
  18.         DataContext context = new DataContext();
  19.         return context.TrafficStops.Where(ts => ts.Id < 100).ToList();
  20.     }
  21. }

The reason I threw in the Sample method is that the F#  JSON type provider uses a sample to infer types and I didn’t want to send the entire set of data across the wire for that.  Once that was done, the traffic stop data was consumable in my F# application like so:

  1. type roadAlert = JsonProvider<";>
  2. type AnalysisEngine =
  3.     static member RoadAlertDoc = roadAlert.Load(";)

Once/if I get the OData set up, I will swap this out but this is good enough for now – after all the interesting piece is not getting the data – but doing something with it!


Handling images in WebApi

I am digging into WebApi (v1) and one of the issues I was facing was images – specifically how to handle images.  I spent a fair amount of time researching the possibilities over the last couple of days and this is what I think I think.

1) You can include your image in your JSON payload like this:

  1. public class Person
  2. {
  3.     public Int32 PersonId { get; set; }
  4.     public String FirstName { get; set; }
  5.     public byte[] Image { get; set; }
  6. }


or you can include the imageUri in your JSON payload like this:

  1. public class Person
  2. {
  3.     public Int32 PersonId { get; set; }
  4.     public String FirstName { get; set; }
  5.     public String ImageUri { get; set; }
  6. }


The advantage of the former is that you can make 1 POST or GET call and get all of the data.  The disadvantages are that the image gets serialized into a large size and your service can get bogged down processing images when the rest of the payload is very small.

The advantage of the later is that you can separate the image into its own location – in fact not even use a Controller, and the processing is snappy-pappy.  The downside is that you need to make 2 GETs and POSTs for any given person (in this example).  For the POST, there is the additional factor that you have to wait for the 1st post to complete so you can get some kind of association key returned so that both POSTs can be associated together  I was talking to fellow TRINUG member Ian Hoppes about problem and he came up with a great idea – have the client generate a GUID and then push both POSTs at the same time.

In any event, if you separate the payload into two POSTS, you need a way of handling the image alone.  In WebApi V1, there seems to be 3 different ways you can do this:

  • A Controller
  • A Handler
  • A Formatter

I wasn’t sure what was the <right> answer so I posted this question to stack overflow – and no one answered it (at the time of this writing).  Left to my own devices, I decided to use an image controller so that the API has consistent implementation.

I newed up an Image Controller and added 3 methods: GET, POST, and DELETE.  The GET was pretty straight foreward (I used a Int32 on the parameter because I haven’t implemented the GUID yet):

  1. public HttpResponseMessage Get(int id)
  2. {
  3.     var result = new HttpResponseMessage(HttpStatusCode.OK);
  4.     String filePath = HostingEnvironment.MapPath("~/Images/HT.jpg");
  5.     FileStream fileStream = new FileStream(filePath, FileMode.Open);
  6.     Image image = Image.FromStream(fileStream);
  7.     MemoryStream memoryStream = new MemoryStream();
  8.     image.Save(memoryStream, ImageFormat.Jpeg);
  9.     result.Content = new ByteArrayContent(memoryStream.ToArray());
  10.     result.Content.Headers.ContentType = new MediaTypeHeaderValue("image/jpeg");
  12.     return result;
  13. }


The DELETE was event easier:


  1. public void Delete(int id)
  2. {
  3.     String filePath = HostingEnvironment.MapPath("~/Images/HT.jpg");
  4.     File.Delete(filePath);
  5. }



The POST, not so easy.  My first attempt was to read the steam and push it into an Image:

  1. public void Post()
  2. {
  3.     var result = new HttpResponseMessage(HttpStatusCode.OK);
  4.     if (Request.Content.IsMimeMultipartContent())
  5.     {
  6.         StreamContent content = (StreamContent)Request.Content;
  7.         Task<Stream> task = content.ReadAsStreamAsync();
  8.         Stream readOnlyStream = task.Result;
  9.         Byte[] buffer = new Byte[readOnlyStream.Length];
  10.         readOnlyStream.Read(buffer, 0, buffer.Length);
  11.         MemoryStream memoryStream = new MemoryStream(buffer);
  12.         Image image = Image.FromStream(memoryStream);
  13.     }
  14.     else
  15.     {
  16.         throw new HttpResponseException(Request.CreateResponse(HttpStatusCode.NotAcceptable, "This request is not properly formatted"));
  17.     }
  18. }


No dice, because the stream includes things other than the image binrary.  My second attempt was to use the MutlipartFormDataStreamProvider (which is the most popular way apparently) like so:

  1. public Task<HttpResponseMessage> Post(int id)
  2. {
  3.     if (!Request.Content.IsMimeMultipartContent())
  4.     {
  5.         throw new HttpResponseException(HttpStatusCode.UnsupportedMediaType);
  6.     }
  8.     string root = HttpContext.Current.Server.MapPath("~/Images");
  9.     var provider = new MultipartFormDataStreamProvider(root);
  11.     var task = Request.Content.ReadAsMultipartAsync(provider).
  12.         ContinueWith<HttpResponseMessage>(t =>
  13.         {
  14.             if (t.IsFaulted || t.IsCanceled)
  15.             {
  16.                 Request.CreateErrorResponse(HttpStatusCode.InternalServerError, t.Exception);
  17.             }
  19.             foreach (MultipartFileData file in provider.FileData)
  20.             {
  21.                 //Trace.WriteLine(file.Headers.ContentDisposition.FileName);
  22.                 //Trace.WriteLine("Server file path: " + file.LocalFileName);
  23.             }
  24.             return Request.CreateResponse(HttpStatusCode.OK);
  25.         });
  27.     return task;
  28. }


I don’t like this because I am writing to disk first – and I don’t need to use the file system – in fact, it might be locked down in some scenarios.  So finally, I cobbled together some posts and found a way to read the parsed stream and create an image without using disk:

  1. public HttpResponseMessage Post()
  2. {
  3.     var result = new HttpResponseMessage(HttpStatusCode.OK);
  4.     if (Request.Content.IsMimeMultipartContent())
  5.     {
  6.         Request.Content.ReadAsMultipartAsync<MultipartMemoryStreamProvider>(new MultipartMemoryStreamProvider()).ContinueWith((task) =>
  7.         {
  8.             MultipartMemoryStreamProvider provider = task.Result;
  9.             foreach (HttpContent content in provider.Contents)
  10.             {
  11.                 Stream stream = content.ReadAsStreamAsync().Result;
  12.                 Image image = Image.FromStream(stream);
  13.                 var testName = content.Headers.ContentDisposition.Name;
  14.                 String filePath = HostingEnvironment.MapPath("~/Images/");
  15.                 String[] headerValues = (String[])Request.Headers.GetValues("UniqueId");
  16.                 String fileName = headerValues[0] + ".jpg";
  17.                 String fullPath = Path.Combine(filePath, fileName);
  18.                 image.Save(fullPath);
  19.             }
  20.         });
  21.         return result;
  22.     }
  23.     else
  24.     {
  25.         throw new HttpResponseException(Request.CreateResponse(HttpStatusCode.NotAcceptable, "This request is not properly formatted"));
  26.     }
  29. }


Note that I am using a disk right now on the POST and GET but I am going to swap it out with Azure Blob Storage.  That way I can just create the image and push it over to Azure without my local disk being used at all.

Sure enough, this works like a champ. The GET


and the POST (using Fiddler – you have to use the most recent Fiddler so you get the [Upload File] link):



Another note is that I am pushing the UniqueID of the image in the request header – not in the content’s header.

This works:

  1. (String[])Request.Headers.GetValues("UniqueId");

This does not:

  1. var testName = content.Headers.GetValues("UniqueId");