Oh, Data!

One of the cooler features that comes with LightSwitch in Visual Studio 11 Beta is it’s support for attaching to OData Services and exposing it’s own data as an OData Service.

OData stands for Open Data Protocol.  It’s an open protocol that defines how to expose your data for querying and updating.  This enables consumers (like LightSwitch) to connect to an OData service like it would with other data sources, for example SQL.

There’s more and more places out there exposing their data through an OData service (see some at http://odata.org/producers).

One of the slickest ones that I have found is the Public Transit Data Community (PTDC) service.  This OData feed is combining all the different public transit data feeds from around the United States and exposing them through one well defined OData feed.

The PTDC has made some nice progress so far and is exposing transit information for over a dozen different agencies.  I’ve got a favorite agency on this list and that would be the Washington DC Metro agency or ‘WMATA’ for short. 

If you’ve ridden the DC metro (or any subway system) more than a couple times you’ve probably found yourself wondering a few things like:

1) Where is my metro stop?

 2) When is my train arriving?

3) How the heck do I get there?

4) My personal favorite – how many escalators will be broken today?

       
       


We can figure all this out by making a LightSwitch app that consumes this OData Service.

We’ll keep it simple for this blog post, and then expand on some features over a couple more blog posts.  Here’s the rundown of what we are going to accomplish in this blog post (we’ll add some more functionality in the next post):

    • Consume a popular real-time public OData feed

    • Create custom queries to limit the data to just DC Metro

    • Create computed properties that query a cached entity set

  • Use the new Web Address data type

Attach To It

For those of you familiar with LightSwitch V1, you’ll find that consuming OData with LightSwitch is just as easy as if you were consuming a SQL database.

Let’s get started walking through the creation of our simple app to simplify our life on the DC Metro.

      1. Open up Visual Studio 11 Beta

      1. Create a new project and select LightSwitch Application (Visual C#)

      1. Call the app “MyDCApp”

      1. We should see a screen something like this now:
      2. image
      3. Click on “Attach to external Data Source” and you will see this:
      4. image
      5. Click “Next”

      1. Use the url: http://transit.cloudapp.net/DevTransitODataService.svc

      1. Select “None” for the authorization method

      1. Click “Next”

      1. Import the following entities for now: Agencies, Arrivals, Entrances, Incidents, Regions, Routes, Stops, and Vehicles

      1. Select “Finish”

      1. We should have something like this now (minus the screens which we add later):
      2. image
      3. Open up the Routes entity designer

      1. Change the KML propery from a “String” type to a “Web Address” type.

        1. This is a new type in LightSwitch and since the KML property is really a web address it makes sense to use it here

      1. Change the “Summary Property” value to RouteName

      1. Open up the Stops entity

    1. Change the “Summary Property” value to Name

Create Some Queries

We need to limit the data that we get back to be specific to the DC Metro for the purpose of our app. To do that we can create some queries based off the entities to limit it to just the DC Metro agency.

    1. Right click on the Arrivals entity and select “Add Query”

    1. Call the query MetroByArrivalTime

    1. Add two filters on the query, both filters will compare against a “Literal”:

      1. RouteType=Metro

      1. AgencyAbbreviation=WMATA

    1. Add a sort: ArrivalTime=Ascending

    1. Add a query on Routes entity, call it RoutesDCMetro.

    1. Add two filters on the query, both filters will compare against a “Literal”:

      1. RouteType=Metro

      1. AgencyAbbreviation=WMATA

    1. Add a query on Stops entity, call it StopsDCMetro

    1. Add one filter on the query, the filter will compare against a “Literal”:

      1. Type=Metro

You should have three queries now, one each under entities: Arrivals, Routes, and Stops.

Each query makes sure that we only get data for the DC Metro system. Additionally the Arrivals query makes sure that we get the data returned to us in order of their arrival time with the “arriving” trains coming back first.

 

Add Computed Properties

The Routes entity has a FinalStop0Id and a FinalStop1Id.  These two properties represent the two different ending “Stops” of the Metro route.  These aren’t very intuitive when viewed on a screen and we need to clean these up a little bit to give them a proper readable name.

We’ll add some computed properties now to accomplish this.  Add two properties to this entity called FinalStop0Name and FinalStop1Name.  After this is done, right click the table entity “Routes” and select View Table Code.

Code Snippet
  1. private static Dictionary<string, string> cachedFinalStops;
  2.  
  3. public static Dictionary<string, string> GetCachedFinalStops(DataWorkspace myDataWorkspace)
  4. {
  5.     if (cachedFinalStops == null)
  6.     {
  7.         cachedFinalStops = new Dictionary<string, string>();
  8.         IEnumerable<c_Stop> myStops = myDataWorkspace.TransitDataData.StopsDCMetro().Execute();
  9.         
  10.         foreach (c_Stop myStop in myStops)
  11.         {
  12.             cachedFinalStops.Add(myStop.StopId, myStop.Name);
  13.         }
  14.     }
  15.  
  16.     return cachedFinalStops;
  17. }
  18.  
  19. partial void FinalStop1Name_Compute(ref string result)
  20. {
  21.     Dictionary<string, string> cachedStops = GetCachedFinalStops(this.DataWorkspace);
  22.     result = cachedStops[this.FinalStop1Id];
  23. }
  24.  
  25. partial void FinalStop0Name_Compute(ref string result)
  26. {
  27.     Dictionary<string, string> cachedStops = GetCachedFinalStops(this.DataWorkspace);
  28.     result = cachedStops[this.FinalStop0Id];
  29. }

There’s a couple things going on here.  The FinalStopName_Compute methods find the Route’s two different stop’s name instead of just using the Id.  They do this by using a LINQ query which says: For some variable q, find me all the stops on the Stops entity that have the same Id value as this Route’s stop.

The only trick here is that we cached all of the stops on the Stops entity the first time that we launched our screen.  We do this because Metro Stops don’t ever change their names. So we can safely cache this information and then our Compute methods can query against the cached list of Stops which will speed things up a bit.

I want to stress here though that this method of caching is really ONLY appropriate for "static" data.  That is data that we know won't be changing.  I felt comfortable caching the names of the Metro Stops as I said, because that really is data that won't change.  But you obviously would NOT want to cache, for example, the "Arrivals" entity this way because that data is definitely not static and using this method would result in you having old data in your cache.

You should have a Routes entity that looks something like this:

image

 

Create Some Screens

Let’s create some screens around the queries that we made now.
These screens will be read only, because the OData service we are querying against is read only.  So we will want to remove our insert, edit, and delete buttons.  Also, we’ll clean up our screens a bit to remove some useless data.

  1. Create a List and Details screen for the MetroByArrivalTime query.
  2. Change the “Summary” List to be a Columns Layout with the following fields:
    1. Arrival Time, Status, Route Name, and Stop Name
  3. Delete the Add, Edit, and Delete buttons under the command bar since we won’t need them
  4. You can customize the “Details” portion of the screen however you like, but below is what mine ended up looking like:
  5. image
  6. Create a List and Details screen for the RoutesDCMetro query and for the StopsDCMetro query
  7. Delete the Add, Edit, and Delete buttons under the command bar since we won’t need them

  1. You can customize the screens however you like, my RoutesDCMetro and StopsDCMetro screens are displayed below respectively:
  2. image - Note how the “Routes” screen has the Kml property set to “Web Link” for the data type”- RoutesDCMetro
  3. image - StopsDCMetro

Run It

Hit F5 now in Visual Studio to run it and check out some of the features of the application and what LightSwitch provides.

The Metro By Arrival Time screen shows us the arriving metro trains, how many cars are arriving, what the status is (BRD for Boarding or ARR for Arriving), and what the destination stop is.

image

The Routes screen has the stop information for each route, as well as a clickable map of the route. If you click on the Kml link it will open up in an application like Google Earth and provide an overview of the route (see below).

image

KML Link:

image

And lastly the Stops DC Metro screen displays the location and name of each stop.

Concluding Part 1

To summarize what we’ve covered up to this point - we gave a basic example of how Visual Studio Light Switch can attach to a public OData feed. We showed the new web address data type.  We utilized computed fields with caching to improve our performance.  And we used queries to narrow down our data.

We still have a few problems we need to get to in the next blog post.  We will customize the Stops screen so that it can provide us with a map of the Stops location instead of just latitude and longitude coordinates.  The Incidents data contains all the information we want regarding broken escalators, so we will need to hook that up too so that we can retrieve that information for each given stop.  Also, since this data is done in “real time” it’d be nice to add some automatic refresh to our application so that our data we are viewing doesn’t get too stale.

 

I’ll be looking to update the blog again sometime next week, until then feel free to leave any comments or questions and I’ll do my best to get back to them.

 

Thanks a lot, Cheers - Martini glass


CODE IS UPLOADED NOW FOR VB.NET Digg This