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

January 7, 2014 1 Comment

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.

image

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.
  3.  
  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:

image

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

image

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:

image

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

The database:

 image

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:

image

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:

http://chickensoftware.com/RoadAlert/odata/$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 =
CASE 
     WHEN dispositionDesc = ‘FURTHER ACTION NECESSARY’ THEN 1
     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 = ‘NO FURTHER ACTION NECESSARY’ THEN 10
     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
END

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

image_thumb1

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:

image_thumb3

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);
  6.        
  7.         response.Content.Headers.Remove("DataServiceVersion");
  8.         return response;
  9.     }
  10.  
  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");
  6.  
  7.         HttpResponseMessage response = await base.SendAsync(request, cancellationToken);
  8.        
  9.         response.Content.Headers.Remove("DataServiceVersion");
  10.         return response;
  11.     }
  12.  
  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:

image_thumb7

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.     }
  13.  
  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<"http://chickensoftware.com/roadalert/api/trafficstopsearch/Sample&quot;>
  2. type AnalysisEngine =
  3.     static member RoadAlertDoc = roadAlert.Load("http://chickensoftware.com/roadalert/api/trafficstopsearch&quot;)

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!

 

Filed under F#, OData, WebApi

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

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

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: