Creating and Consuming LightSwitch OData Services

NOTE: This information applies to LightSwitch in Visual Studio 2012.

In the next version of LightSwitch, we’ve added support for OData services, both consuming external services as well as producing services from the LightSwitch middle-tier. The Open Data Protocol (OData) standardizes the way we communicate with data services over the web. Many enterprises today use OData as a means to exchange data between systems, partners, as well as provide an easy access into their data stores. So it makes perfect sense that LightSwitch, which centers around data, also should work with data services via OData. Since OData is a standard protocol, it also means that other clients can access the data you create through LightSwitch.

In my last post on OData in LightSwitch I showed you how we could use external OData services to enhance our LightSwitch applications. In this post I’ll show you how to consume OData services that LightSwitch exposes.

Creating OData Services with LightSwitch

Creating OData services in LightSwitch doesn’t take any additional skills than you had before. In fact, you don’t have to do anything special to create these services. They are automatically created when you define your data and compile your application. Each data source you use in your LightSwitch application becomes a data service endpoint. Within those endpoints, each entity you define in LightSwitch is exposed automatically. Instead of the “black box” middle-tier we had in the first version of LightSwitch, we now have an open middle-tier that can be used to interface with other systems and clients.

 

What’s really compelling here is not only can you model data services easily with LightSwitch, but any business logic (and user permissions) that you have written on your entities will execute as well, no matter what client is accessing the services. And since OData is an open protocol there are a multitude of client libraries available for creating all sorts of applications on a variety of platforms, from web, phone, etc.

Let’s dig into the service endpoints a little more and see what they look like. For this example I’m going to use the Contso Construction sample.

Getting to Your LightSwitch OData Services

When you deploy your LightSwitch application in a three-tier configuration (either hosting the middle-tier in IIS or Windows Azure) then the service endpoints are exposed. The name of the services correspond to the name of your data sources. In the Contoso Construction sample, we have two service endpoints because we have two data sources. Within each of the services, we can navigate to all the entity sets we model in the data designer.

There are a couple ways you can get to the services when debugging. The easiest thing to do is to change the client application type in the project properties to “Web”. When you debug the app (F5) you will see the port number Visual Studio has assigned you in the address bar of your browser.

While debugging, open another tab in the browser and navigate to your OData service using the port number. You will see the list of entity sets available to query through the service.

The Open Data Protocol is a REST-ful protocol based on AtomPub and defines a set of query operations that can be performed on the data using a set of URI conventions. You query the service with an HTTP-GET request and the service will return to you a feed with the results in the response. To see the raw feed in IE, go to Tools –> Internet Options, Content tab. Under Feeds & Web Slices click “Settings” then uncheck “Turn on feed reading view”.

If we want to see all the customers in the system we can simply type the URL https://localhost:41155/ApplicationData.svc/Customers and you will get the following response from the service. Each entry in the feed is a customer entity which corresponds to a row in the database.

Keep in mind that the queries are case sensitive. Notice it’s Customers not customers in the URL. If you want to return a customer who’s ID = 1 then use:

https://localhost:41155/ApplicationData.svc/Customers(1)  

which would return only the first customer shown above. Similarly, the OData protocol defines a standard way of navigating relationships via navigation properties. If you want to get all the construction projects for a particular customer use:

https://localhost:41155/ApplicationData.svc/Customers(1)/Projects 

which would return just that customer’s projects. If you want to return only Customers who’s last name is “Massi” then use:

https://localhost:41155/ApplicationData.svc/Customers ?$filter=LastName eq 'Massi'

Of course there are a whole slew of other query operations supported like OrderBy, Top, Skip, Sort, etc. Take a look at the Open Data Protocol URI conventions for a full list. There are also operations defined in the protocol for performing updates, inserts and deletes using standard HTTP verbs. The LightSwitch client communicates with the middle-tier data services this way.

Another way to inspect the service requests and responses is to install a tool like Fiddler. Fiddler is a Web Debugging Proxy which logs all http(s) traffic between your computer and the Internet or localhost. This way you don’t have to change the client application type, you can leave it as a desktop application and still see the traffic. If you’re trying to build your own clients against OData services this is a must-have tool.

Now that you understand how OData services are exposed from the LightSwitch middle-tier, let’s move on and consume some of our data from another client outside the LightSwitch client. One of my favorite tools for analyzing data is Excel.

Consuming LightSwitch Services in Excel using PowerPivot

You don’t actually need to know anything about OData to consume these services in Excel. Excel has a free add-in aimed at power users called PowerPivot that you can install into Excel 2010 to get access to all sorts of data sources that Excel doesn’t support out of the box, including OData feeds. Download it at www.powerpivot.com. You can arm your power users with this add-in and point them to your LightSwitch data services to perform complex data analysis and create reports all in a familiar tool.

To connect Excel to an OData service, click on the PowerPivot tab and then click the “PowerPivot Window” button to launch PowerPivot. Click the “From Data Feeds” button and supply the Data Feed URL, then click next.

At this point you can select from a list of data sets.

Select the data you want and then click finish. PowerPivot will import the data you select into a set of spreadsheets. You can keep importing additional feeds or data sources into PowerPivot and then relate them together to create powerful data mashups. To create relationships between your data sets select the Design tab on the PowerPivot window and click Manage Relationships.

Once you set up the relationships you need, you can create pivot tables and charts by selecting the Home tab and dropping down the PivotTable button.

You will then see the PowerPivot Field List window that you can use to create charts like normal. Keep in mind that if you are not working against LightSwitch data services that are deployed but are instead trying this while debugging, you may need to update the port number to your data service. You can do this in the PowerPivot window by selecting the Design tab and then clicking “Existing Connections”. Then you can update the port number and refresh the data back on the Home tab.

The updated Contoso Construction sample contains a spreadsheet in the root folder called ContsoAnalysisPowerPivot.xlsx that has a variety of charts you can play with.

 Contoso Construction - LightSwitch Advanced Sample (Visual Studio 2012)

Wrap Up

I hope you are now starting to realize how powerful LightSwitch can be not only to create business applications, but also data services. Being able to consume as well as expose LightSwitch data sources as OData services opens the door for a wide variety of client applications accessing your data through your business rules. In the next few weeks the team and I will take you on some more adventures with OData and show you some of the numerous possibilities you now have. Of course, we’ll also drill into other new features as well. Stay tuned!

Enjoy!

Comments

• Anonymous
  March 10, 2012
  Thanks Beth, Excellent walkthrough, Keith

• Anonymous
  March 12, 2012
  Will the next version be available for V.S. 2010?

• Anonymous
  March 15, 2012
  Hi Phil C. No the next version of LightSwitch is included in Visual Studio 11 so it will be an upgrade from VS 2010. HTH, -Beth

• Anonymous
  March 22, 2012
  Are you saying here in the beginning that LightSwitch Applications are in fact Odata and does that mean if I create data sources with Lightswitch Version 1 they will later be able to be manipulated and queried similarly as in your last few posts on Odata. I was recently pondering over the diagrams in the Lightswitch help section("Working with Data-Related Objects in Code") of the data layers created by light switch wondering how to do some tasks by querying and using calculated (code analyzed) fields to summarize information from the most recent data from a data source. SO basically can I start creating my LightSwitch Data Sources while waiting for the next release of Lightswitch or will there be compatibility issues between things created now and in the future?

• Anonymous
  April 30, 2012
  Hi Reigh7, Yes, when you upgrade your LightSwitch V1 projects to V2, then the LightSwitch middle-tier will be upgraded to OData services under the covers. Any server code you write against your entities in the Data Designer will also be upgraded. We strived to make sure "it just works". I encourage you to download the Beta and test your particular scenarios and let us know how it goes in the LightSwitch Beta Forum: social.msdn.microsoft.com/.../threads Cheers, -Beth

• Anonymous
  May 10, 2012
  I got really excited about all this until I discovered forms authentication is not supported in Power Pivot. So as soon as you build an LS app that requires data protection you can't use Power Pivot. Gus

• Anonymous
  August 22, 2012
  @Gus, I also use forms auth and it is working. Simply go to "Advanced" when connecting to the feed then change SSPI to Basic and enter your credentials where asked.

• Anonymous
  October 09, 2012
  I truly love this feature. Thanks Beth :)

• Anonymous
  October 09, 2012
  For me, it does not support "?$format=json". is there any plan to support JSON output?

• Anonymous
  October 11, 2012
  Hi YJ -- it does support JSON. Under the 2.0 protocol spec you need to provide this in the request header: Accept: application/json;odata=verbose See www.odata.org/.../operations Cheers, -Beth

• Anonymous
  November 29, 2012
  Hi Beth, When will LightSwitch support more features ODATA offers? I need to use service operations that return scalar values, CLR types, etc. I need LightSwitch to expose them as Methods, Tables and Queries. For more information on Service Operations please read the following:  msdn.microsoft.com/.../cc668788.aspx Regards, Jason

• Anonymous
  December 09, 2012
  Hi Beth, These options are just great, and your explanations are so excellent, as usual. But I can"t get connected (Access denied). Could you publish some notes about connecting Lightswitch generated service to Excel when Form Authentification is used ? Thanks in advance. Georges

• Anonymous
  February 06, 2013
  Just wondering if anyone has the odata feed into powerpivot working with forms authentication other than using the localhost out of a debug as in this example.  I can get linqpad to connect to my odata with forms authentication on a published website but cannot get the excel 2010 powerpivot to connect (changing to basic auth with username and password under advanced).

• Anonymous
  March 01, 2013
  Hi Beth, nice post, I've been using it for my app. I-m coming back b/c I have a "big" problem when I try to deploy my app, my SVC is not accesible from server code. I mean I created a custom method in server to access data using SVC, but when I deploy it to IIS it says: HTTP Error 401.0 - Unauthorized You do not have permission to view this directory or page. Please, any help it this will be really appreciated. Thanks in advance

• Anonymous
  October 22, 2013
  Hello Beth, I want to access my oData service (done with LS - VS2012) using PowerPivot, but I need to provide a userID/password (form authentication).  I enter the credentials in the Advanced setting tab of the powerpivot, but I still get an authentication error. Do you know if PowerPivot works with secure oData?

• Anonymous
  November 19, 2013
  Hello Beth, I worked with lightswitch for about 2 years now, since the beta. I am trying to consume some OData from a dbpedia.org query : example: navigate : http://dbpedia.org/fct/ search for "medicinal plants", select the first result, and choose to display it as OData (bottom of the page) Then I try to add an OData datasource in lightswitch pointing to the obtained URL, I get a lightswitch error saying "This service does not contain any entities queryable by LightSwitch." As I understand, Lightswitch supports OData natively. But at the same time the exposed service is OData. Where is the issue ? How would I manage to consume a service like this ? (do I need to make a workaround with WCF RIA?)

---

Also I am interested in semantic web, RDF, SPARQL, RDFa / microdata optional compliance, ontologies, do you think there will be lightswitch support these features in later versions ? I am working on a proof of concept to obtain a hybrid lightswitch application using these technologies (brightStarDB), but It doesn't integrate very naturally.

• Anonymous
  November 20, 2013
  Hi Gaby, Not all OData feeds support the operations needed in LightSwitch. Here's a post from one of the LightSwitch team members on what capabilities we can provide depending on the feed. blogs.msdn.com/.../odata-consumption-validation-in-visual-studio-lightswitch-2012.aspx HTH, -Beth

• Anonymous
  December 05, 2013
  Hello Beth, As always your post are amazing! I have a little problem. I recently update my VSL2011 project to VS2012, and exposed Odata entities (just publishing, I didn't do any additional configuration and the svc files where created). I'm trying to consume the odata service from an FireMonkey app (Delphi XE5) and it gave me an error saying "Error 415 Unsupported Media Type", for what I can tell the error seem to be the way that the Odata is exposed (is not in Json format). Do you know how I can manage the way that the data is exposed inside the VS2012 Lightswitch app? Regards!

• Anonymous
  December 10, 2013
  @Gabriel - if you want an Odata service to return JSON to a custom client, then when the client makes the call to the service you need to specify Accept: application/json in the Request Header. See: msdn.microsoft.com/.../jj190799.aspx for an example. HTH, -Beth

• Anonymous
  December 11, 2013
  @Beth, Thanks for your answer!. I have other issue and maybe you can help me or tell me where i can found some info about it. My lightswitch app is using RIA Services, so I have three Data Sources, ApplicationData, DbEmpresas and DbEmpleados. VS2012 is exposing the OData automatically and I can access to all the information from the client. The application is intented to have personal information so one user must not see the information of other user. So: 1.- ¿Is there any way that I can manage that from VS2012? In other words, ¿Is there any way to apply some sort of security to the data exposed from VS2012? 2.- ¿Is there a way to say to VS2012 "I Don't want to expose OData"? Thank you in advance, Gabriel

• Anonymous
  April 18, 2014
  Hi I am publishing a Lightswitch app to Office365 SharePoint, and I want to access the OData feeds. If I fire up the app, I can do this via the browser, but I need to be able to access the OData feeds externally, but the URL is https://{GUID}.O365apps.net, which doesn't work. any ideas?

• Anonymous
  April 21, 2014
  @Mark Stacy - Once you log into your O365 tenant then you should be able to access the oData feed like normal. Make sure you specify the data service you want to hit. i.e. /ApplicationData.svc HTH, -Beth

• Anonymous
  October 21, 2014
  Hello Beth,   http://www.odata.org/  main website is down.  I believe Microsoft maintains the website.  

• Eric

• Anonymous
  January 11, 2016
  The comment has been removed
• Anonymous
  April 30, 2017
  Hi Beth,I'm trying to consume the data from a LS app that is hosted in Azure. I followed the steps above but keep getting prompted for "Discovery Credential". I've tried using an existing admin that exists in the app and my Azure creds, but to no avail.Any suggestions?Thanks,Michael