Sharing the goodness…
Beth Massi is a Senior Program Manager on the Visual Studio team at Microsoft and a community champion for business application developers. Learn more about Beth.
More videos »
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 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.
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 http://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:
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:
which would return just that customer’s projects. If you want to return only Customers who’s last name is “Massi” then use:
http://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.
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)
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!
Thanks Beth, Excellent walkthrough, Keith
Will the next version be available for V.S. 2010?
Hi Phil C.
No the next version of LightSwitch is included in Visual Studio 11 so it will be an upgrade from VS 2010.
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?
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
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, 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.
I truly love this feature. Thanks Beth :)
For me, it does not support "?$format=json". is there any plan to support JSON output?
Hi YJ -- it does support JSON. Under the 2.0 protocol spec you need to provide this in the request header:
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
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.
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).
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
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?