Home > Articles > Home & Office Computing > Microsoft Windows Vista & Home Server

Your Data Everywhere, Part 2: Exposing LightSwitch Data as OData Services to Other Clients

Continuing his three-part series on LightSwitch support for Open Data Protocol (OData) in Visual Studio's 2012, Alessandro Del Sole, author of Microsoft Visual Studio LightSwitch Unleashed, demonstrates the power you gain by being able to expose data sources to OData services in a wide array of clients.
Like this article? We recommend

In part 1 of this series, I explained how to consume Open Data Protocol (OData) services in your LightSwitch applications. Not only can LightSwitch in Visual Studio 2012 connect to existing OData services; it also can expose data sources as OData services to potentially an infinite number of clients. In this article, I'll show how LightSwitch exposes a data source as an OData service that can be consumed by other applications, using an example based on Microsoft PowerPivot for Excel. Part 3 will conclude this series by examining how to create a Windows Phone app that is capable of working with OData feeds produced by LightSwitch.


In part 1 of this series, you learned that the next version of Visual Studio LightSwitch, which you can currently test as part of the Visual Studio 2012 Release Candidate, allows for consuming existing OData services. Actually, the OData support isn't limited to connecting to external data sources, since the LightSwitch infrastructure also automatically exposes all the data sources in the application as OData feeds that can be consumed by other clients—not only LightSwitch clients, but any client that supports OData, such as mobile applications. The really good news is that you don't have to do anything to create OData services from LightSwitch data sources; they're automatically created and exposed by the middle tier.

As you might know, an OData service is represented by a WCF service reachable by pointing to its .Svc file. What LightSwitch basically does is create a public WCF service for each data source, exposing the endpoint of each .Svc file. The intrinsic database, external databases (including SQL Azure), and RIA Services are all data sources that are exposed automatically in the form of OData services. (SharePoint 2010 already comes with an OData endpoint.) Then, when you deploy the application to a three-tier infrastructure such as Internet Information Services or Windows Azure, clients will reference the OData services pointing to the .Svc endpoint.

For example, the OData representation of the intrinsic database can be reached with something like this:


Figure 1 shows how LightSwitch generates OData services from the various data sources, and how they can be consumed from both LightSwitch and other clients. It's an incredible benefit that developers can have OData services from LightSwitch data sources with absolutely no effort.

Figure 1 The middle tier exposes OData services endpoints.

Creating a Sample Project

As we go along in this article, you'll see how both the intrinsic database and an external database are exposed as OData services. The external data source I used for this example is the Northwind sample database for SQL Server, but the same concepts apply to SQL Azure databases and WCF RIA Services.

I assume that you already have some experience with creating a LightSwitch project and some entities and tables. To get started with this example, create a new LightSwitch application in Visual Studio 2012. Regardless of the programming language you use, call your project ExposingOData. When ready, create two new entities called Customer (Figure 2 shows the design you should use) and Order (as shown in Figure 3).

Figure 2 Design of the Customer entity.

Figure 3 Design of the Order entity.

Both entities offer a simplified representation of business data. We don't need complex entities and tables for this example, since our focus is on how the data source is exposed as an OData service. After creating both entities, establish a one-to-many relationship between the Customers and Orders tables that LightSwitch generated based on the new entities, as shown in Figure 4.

Figure 4 Specifying a one-to-many relationship between Customers and Orders.

The next part of the process is connecting to the Northwind database. Follow these steps:

  1. In Solution Explorer, right-click Data Sources and select Add Data Source.
  2. When the Add Data Source Wizard starts, select Database.
  3. Select the Northwind database via the Connection Properties dialog.
  4. Select the Shippers table and complete the wizard.

Visual Studio 2012 gives LightSwitch developers the opportunity of publishing either the data with a client application, or only the services that it generates. If you're using LightSwitch just to create and expose data sources as OData, you're not obliged to add the user interface. In this particular example, it's a good idea to add some screens to provide a clearer demonstration of how multiple clients (LightSwitch client and other clients) can consume OData feeds. At this point, you have two data sources in your application. Feel free to add screens to the application as you like; I suggest adding just one screen so that it's easier to demonstrate how the LightSwitch client is actually connecting to data.

Before testing how OData services have been implemented, you need to switch to a web application type in the project's properties. This is required so that you'll have the opportunity to type the service URL in the browser's address bar, but you can certainly create a desktop client that's capable of exposing OData services, if you prefer. If you run the application from Visual Studio 2012, notice that the URL contains the server name followed by the port number and then the application's main page, plus a value indicating that the application has been launched from Visual Studio. The URL takes the following form:


where PortNumber represents the port on the development web server used for debugging. Notice that the AuthenticationType value varies depending on whether you chose Windows or Forms authentication, rather than the anonymous None. Now, replace the URL with the following:


Of course the port number must be left unchanged. At this point, your web browser should show the OData representation of the intrinsic database, as demonstrated in Figure 5.

Figure 5 Testing the OData service representation of the intrinsic database.

You now have a fully-featured OData service that supports all the REST-ful APIs that you can use for querying and updating data. For example, you can query the full list of items from a given table by adding the name of the table to the URL like this:


Figure 6 shows a sample query result.

Figure 6 Querying a table from the OData service.

By now, you're probably beginning to understand how powerful this feature is. Any external client can consume your LightSwitch data—desktop clients, web clients, and even mobile clients. You can also test the OData service for the Northwind database, reachable via the following URL:


Keep these facts in mind:

  • [lb] LightSwitch generates the name of the service based on the name of the data source, which is visible in Solution Explorer, followed by the .Svc extension. For the intrinsic database, the name of the service is always ApplicationData.svc.
  • [lb] The service name is case sensitive, so you pay attention to typing the service URL correctly. ApplicationData.svc will work, Applicationdata.svc won't.
  • [lb] Case sensitivity also applies to table names, so CUSTOMERS is not the same as Customers.

So far, we've seen LightSwitch generate one OData service for each data source, both intrinsic and external. However, we've only looked at how to reach services at debugging time, using the local development server. This is certainly important, but more likely you'll want to expose such services through the network from a web server. To do this, you need to deploy the LightSwitch application to a three-tier environment. Let's look at that option next.

Deploying to a Three-Tier Web Server

Deploying LightSwitch OData services is incredibly easy, since you simply have to publish the application in the usual way, and the setup procedure automatically deploys services and exposes endpoints for you. Also, Visual Studio 2012 provides an option that allows you to publish the application as if it were a web service, with no client. This is useful if you only want to expose data sources without the need for a LightSwitch client.

We've looked at how to deploy LightSwitch services to the local web server on the development machine. This is a good simulation, since you know that there's basically no difference between that and publishing to a production web server, except that such a server must be configured with the LightSwitch server prerequisites and must be capable of accepting incoming connections from Visual Studio. Before publishing, change the configuration from Debug to Release, and then select Build > Publish. When the LightSwitch Publish Application Wizard starts, follow these steps:

  1. On the Client Configuration screen, select the Web option. As I mentioned earlier, we want to publish a LightSwitch client as well in this example, so this is the appropriate option. Select Service Only if you want to deploy only the OData services, without a LightSwitch client application.
  2. On the Application Server Configuration screen, make sure that IIS is selected.
  3. On the Publish Output screen, select the option "Remotely publish to a server now" and specify the server's URL. Make sure that the name of the application has been entered correctly in the Site/Application text box. Figure 7 shows an example of how the screen should look.
  4. Figure 7 Setting the publish output options.

  5. On the Database Connections screen, in the first text box specify the SQL credentials that will be used for reading and writing data. In the second text box, specify any administrator credentials that are required for publishing the database schema and generating the database on the server. Figure 8 shows an example.
  6. Make sure that appropriate credentials are specified in the Other Connections screen for the Northwind database.
  7. When you're ready, click the Publish button.
  8. Figure 8 Setting SQL Server credentials for creating and accessing the database.

To make sure that both the application and the OData services have been published correctly, type this to launch the application (or specify the server name instead of localhost):


Then type this:


At this point, we can test services with a different client: Microsoft PowerPivot for Excel.

Consuming LightSwitch OData Services from Different Clients

A good example for understanding the benefits of exposing OData services from LightSwitch is using PowerPivot, a famous add-in from Microsoft for Excel 2010. PowerPivot is basically an add-in that allows for consuming data sources that Excel doesn't support out of the box, including OData feeds, to create reports and perform data analysis inside Excel.

  1. Start Excel 2010 and select the PowerPivot tab.
  2. Click the PowerPivot Window button to launch the add-in.
  3. When the window is active, click the From Data Feeds button. This feature allows you to specify an OData service as the data source for the new report.
  4. Specify the service URL and a friendly name for the connection in the Table Import Wizard (see Figure 9). Click Next.
  5. In the next dialog, specify the table(s) to be imported from the data service. Figure 10 shows the selection of both tables from the OData service that exposes the LightSwitch intrinsic database.
  6. Once the import is completed, the PowerPivot window shows a sheet for each table, and lists data if the table contains any. You can use the PivotTable drop-down on the Ribbon to generate charts that will be sent to Excel and proceed to data analysis. You can also manage relationships between tables that PowerPivot can discover from the OData feed. (This is why we added a relationship between Customers and Orders earlier in this article.) Figure 11 shows some imported tables and the options available on the PivotTable drop-down.
  7. Figure 9 Specifying the connection information for the OData service.

    Figure 10 Selecting tables from the data service.

    Figure 11 Tables have been imported, and users can now interact with Excel.

Working with PowerPivot is just one example of how an external client can interact with LightSwitch data through OData services, but it should open up your mind to other exciting possibilities. In part 3 of this series, we'll consider another interesting (and more specific) example based on a mobile client, which is a very important scenario these days.

Authentication Hints

OData services support authentication, so that only authenticated users will be able to access data feeds. In LightSwitch, you can secure your application by specifying Windows authentication and Forms authentication, in addition to using anonymous authentication (as in the previous example). When you publish a LightSwitch application, the selection that you made in the LightSwitch Access Control will also affect the generated data services; for example, if you decide to publish a LightSwitch application requiring Forms authentication, accessing the generated OData services will also require supplying a username and password. You can test this scenario by deploying the previous example with Forms authentication enabled and then by attempting to view the ApplicationData.svc file in the web browser; at this point, you'll be prompted to enter your credentials. Any client you use to access OData feeds exposed by LightSwitch under Access Control will need to enter the required credentials.


Full support for OData in the latest version of LightSwitch offers an incredible number of new business opportunities, because external applications different from one another can all take advantage of what you've developed inside LightSwitch. Consuming existing OData feeds and exporting LightSwitch data sources as OData services is available out of the box, and no effort is needed from the developer. Even still in beta, the Visual Studio LightSwitch 2012 looks very promising for making it easier to interact with other applications and data sets.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.


Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.


If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.


This site is not directed to children under the age of 13.


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020