Expose SQL Server data as an OData Endpoint with WCF Data Services

Printer-friendly version

WCF Data Services has been around for some time. It’s the .NET programming framework for building OData services. I’ve used OData a bit with Dynamics CRM in the past, but it wasn’t until recently when I leverage WCF Data Services that I really appreciated its ease of use.

OData is all about exchanging data over the web and WCF Data Services allows you to expose data as a REST-based service that follows the OData conventions and protocols. You can expose an Entity Data Model, Linq2SQL entities, .NET Objects or use a custom provider for your own data source.

In my case, I had an existing SQL Server database and wanted to expose a couple tables as an OData service, so I used the Entity Data Model, which is what this post will walk you through: building a simple OData Service using WCF Data Services.

To expose the data as an OData service, we’ll create an ASP.NET Web Application and use ADO.NET Entity Data Model and WCF data service. Using Visual Studio 2013, create a new ASP.NET Web Application project and choose the Empty template with No Authentication and uncheck Host in the cloud. You can add those options if you need to, but it’s not needed for this example (see Figure 1).

Figure 1. New ASP.NET Project, select Template

Add a new ADO.NET Entity Data Model and give it a name, ChinookModel in this case as we are using the Chinook sample database (see Figure 2).

Figure 2. Add New Item dialog

The Entity Data Model Wizard will prompt you to select a model (see Figure 3). Select EF Designer from database since we are using an existing database for our model and click Next. You will then be asked to choose your data connection.

Figure 3. Entity Data Model Wizard

Click the New Connection button to display the Connection Properties dialog.

Figure 4. Entity Data Model Wizard: Connection Properties

Enter the server name, choose the database on your server and click Ok. When you return to the Data Connection page, enter ChinookContext as the name for the model and click Next (see Figure 5). 

Figure 5. Entity Data Wizard: Data Connection

From the Choose Your Version dialog, accept the default selection of Entity Framework 6.x and click Next

Figure 6. Entity Data Model Wizard: Choose Version

In the final page of the Entity Data Model Wizard, select the checkbox next to the Tables you want to include, uncheck Pluralize or singularize generated object names checkbox (you can keep the default Pluralize option if you like) and specify ChinookModel as the Model Namespace (Figure 7). Click Finish to complete the wizard and generate the Entity Model code.

Figure 7. Enity Data Model Wizard: Choose Database Objects

The Wizard will generate the entity data model, data context and related classes for the selected tables (see Figure 8).

Figure 8. Entity Data Model, Context and Classes

Since we are referencing the latest version of the Entity Framework, along with a WCF Data Service, we need to install the WCF Data Services Entity Framework Provider. Right-click the ChinookService project and choose Manage NuGet Packages. From the Manage NuGet Packages dialog, make sure Include Prerelease is selected in the top dropdown box (see Figure 9). Search for “WCF Data Services Entity” and click the Install button next to the entry for WCF Data Services EntityFramework Provider. Click OK to install the package into the ChinookService. You should also be prompted to accept the license agreement. When you are done, click Close to return to your Visual Studio project.

Figure 9. Manage NuGet Packages

The Entity Framework Provider gives us an API for responding to ODATA queries, but we need to add a WCF Data Service to expose the data on the Web as OData. Right-click the ChinookService project in Solution Explorer and click Add | New Item. Select the Web node and choose the WCF Data Service 5.6 template (see Figure 10).

Figure 10. Add New Item: WCF Data Service Template

Name the service ChinookODataService.svc and click Add. This will generate a service with a generic DataService of <T> base class and the comments will indicate where you need to specific the type of T. The service will also indicate what lines should be uncommented to expose the entities and operations from data service you specified (see Figure 11).

Figure 11. Initial ChinookODataService Class

The generated service is locked down by default, nothing is exposed. As you see from the comments, you have make a few changes to the service to expose the desired entities and operations. The first TODO item is adding the data source class/context name, which in this case is ChinookContext (see Figure 5). You will also need to change the base type for the class from DataService to EntityFrameworkDataService and then add a using reference to the System.Data.Services.Providers namespace.

The next TODO item is uncommenting the line that sets the SetEntitySetAccessRule method on the DataService. You need to replace “MyEntityset” with a “*” to indicate that you want to expose all the entities in my OData service as read only. Since we only have 2 entities in our model, using “*” is fine, but if you had several entities and only wanted to expose a few, you would create an EntitySetAccessRule for each and specify the entity to expose specifically like this: config.SetEntitySetAccessRule("Customer", EntitySetRights.AllRead);

Finally, add the ServiceBehavior attribute to the service class definition to enable server logs to be generated. This helps to diagnose issues by providing the exception details in error messages you receive. When all code changes are complete, your class should look like the following:

Figure 12. Updated ChinookODataService Class

Verify everything is working by building the project, then right-click the ChinookODataService.svc file in Solution Explorer and select View in Browser (see Figure 14).

Figure 13. View in Browser Option

A browser window will launch and display an instance of the data service on your localhost machine. If successful, a XML service document is displayed showing the resources, Customer and Invoice, which looks like the following:

Figure 14. ChinookODataService Document

You can now display the Customers, for example, by simply adding the collection href of Customer to the end service URL and all the customers are return as Atom format XML (see Figure 15).

Figure 15. All Customers

Or you can return a specific customer by specifying its id/primary key.

Figure 16. Customer 51

Now that our data is exposed, we can use the OData System Query Options as needed to retrieve and/or filter the data. For example, I can query the Invoice collection and return only those invoices where the BillingCity is Boston by adding a filter (see Figure 17).

Figure 17. Invoices with BillingCity = Boston

You can use different clients to call the service and returned the data in Json format as well. For example, I using Postman to do some filtering and paging of Invoices and returning the result as Json in Figure 18.

Figure 18. Using Postman to call the service and return Json data

As you can see, by building a WCF Data Service and giving it our DataContext/Resources, it takes the various OData conventions and protocols and applies them to our resources, making it easy to expose and query our data over the web. Service operations/methods can be exposed as well with the WebGet or WebInvoke attributes. You can also use the Web API 2 with its support for OData to create OData services as well, but WCF Data Services does a lot of the ‘heavy’ lifting for you with very little code.

About the Author:

TopLine Strategies delivers the complete integration and development of sales, marketing and customer service technologies that enable corporate clientele to improve revenue streams and strengthen customer interactions. Our project management and consulting is designed to achieve timely delivery, 100 percent user adoption of the technologies we implement and deliver measurable returns on investments for our clients.

Comments (0)

Related Blogs

TheReact Native Open Source roadmap was announced in Q4 2018 after they decided to invest more in the React Native open source community.

October is not just about pumpkins, fall foliage, and cooler temps anymore. October 2018 also means the exciting introduction of Microsoft Dynamics 365 for Customer Engagement.

Back in 2016, Microsoft introduced its intentions to refresh its CRM and ERP strategy with Dynamics 365. At the heart of its services was the Common Data Model (CDM).