Sam Tran ( 陈 偉明)

One of the great things about the clouds is that it makes existing good technology even better and in the case of the Azure stack, also much more simpler. One example that came to mind this week was Entity Framework after reading that it now works with CosmosDB, albeit still in beta and for the Core version only. For those not familiar, “Entity Framework (EF) is an object-relational mapper that enables .NET developers to work with relational data using domain-specific objects. It eliminates the need for most of the data-access code that developers usually need to write.” Put another way, it saves us developers heaps of time and a must have skill if you code in .Net and work with data.

Before diving straight into EF with CosmosDB, I thought it would be an idea to refresh my memory on EF using relational since it’s been years since I’ve touched EF. As I was reminded just how powerful yet simple this was, I also decided to write a short blog about this. To make it more interesting I also decided to build a web service using Windows Communication Foundations (WCF) specifically for SQL Server. While SQL is the native platform, EF and WCF also works with Oracle and Postgres. You simply need to install the necessary drivers.

So without further ado, lets get started. First off, create a new ASP.Net Web Application in Visual Studio.

For the type of web application, select Empty and ensure to leave all the checkbox unticked to avoid reconfiguring later on. Note especially I’m keeping the default ‘No Authentication’ settings to keep things simple. Authentication is a complex and technical area that would require several books to cover.

Click OK.

In the object explorer, right click the project and select Add New.

The next screenshot assumes you already have EF installed, Select ADO NET Entity Data Model and give your model a meaningful name. As the source database in this case will be the AdventureWorks DW database, I’m just naming it that. If you don’t already have EF installed, you can download this through Nuget

Select EF Designer from Database as we’re working off an existing database.

Provide the connection details. Note I’m providing connections to an Azure SQL database as I plan to deploy the app/web services to Azure in the cloud later and this makes things easier. You could well select an on-prem database though that will require configuring a gateway.

As this is just a demo and we want to keep it simple, just select ‘Yes’ in the checkbox below.

Select the objects you want exposed in the API. Here I randomly picked a few tables and two stored procs I wrote specifically for this demo. One of the procs simply returns a table while the other has a parameter to filer out the results which will be used to illustrate passing parameters.

Once you hit finish above, your model and corresponding mapping will be created as seen below.

Next up, you need to create an OData Service which is part of the Windows Communications Foundations. To add OData, simply right click your project in the Solutions Explorer and select “Add New Item” as you did to add Entity Framework earlier only this time, pick WCF Data Services as seen below.  If you can’t see this here, you need to install it via NuGet. Give your service a meaningful name here as this will form part of the base URL for your web services.

As with EF which automatically created a whole lot of code for you earlier, WCF OData will also create the basic scaffolding needed. For the really basic web services we’re about to create, all that needs to be done now is to update one line of code within your class. As seen below, you need to replace the ‘Todo” line in blue with your data source class.

For us, it is simply “AdventureWorksDWEntities” as seen below. If you’re new to EF, just start typing the word “entities..” and intellisense will automatically bring up the available values. You’ll also notice I’ve uncommented and updated the two lines for configuration. This is to give our services more permissions.

With the codes updated, we’re all ready. Just  hit debug and our web services is ready for use and testing (locally).

This will launch a web browser like the one below. The contents of this will depend on the objects you selected earlier (tables, views). These are the objects already available in our web services. You’ll notice our procs are not shown. This is expected but they’re also not available just yet and require some minor coding to expose them (for security reasons). All the other tables and views are ready for consumption.

And to confirm our procs have been imported and mapped, we can view this in the Model Browser (they’re just not exposed, yet).

Lets test one of the endpoints locally using just the browser. To do this, simply append the name of your table to the base URL so that it looks something like this:

http://localhost:52354/WcfDataService.svc/DimProducts

This should bring back all the data from the DimProducts table in xml, the default format for OData and with it, confirmation our web services is working.

Now we’re ready to deploy our app to Azure and make our data, it’s models and associated business rules available across the globe. With just a few clicks, we can share as little or as much data as we like with our overseas subsidiaries and/or clients. I won’t go into details of deployment as that’s another topic but it is fairly straightforward, just follow the normal process of deployment. I will show this below to show you can deploy it to an App Service or Azure Virtual Machine. I selected App Service as that’s faster and easier to manage.

Before deploying the app, I will update some codes so as to expose the endpoints for the stored procs. Mappings and other scaffolds were generated by EF and WCF for the procs but for security reason, you need to do the rest manually, unlike tables and views. To expose the procs, just add the two methods you see below and note the name of the procs will form part of your URL, just like the tables and views. To call the procs, your URL will look something like this:  http://restsam.azurewebsites.net/WcfDataService.svc/SpGetProduct 

If you don’t intend on exposing your procs, you can skip this part.

With the app deployed, our web services is ready and in the clouds, ready for consumption across the globe. Lets test it with some front end clients. First off, we’ll use Linqpad, my favourite toy. Simply add a WCF Data Services connection (though there are other ways to use Linqpad without these drivers).

For URI, just add your base URL, hit Test and OK if connection made. You’ll recall we didn’t use authentications earlier to keep things simple so name and password is not needed.

With connection made, you can start coding in C#, VB.NET or simply using LINQ.

Next up, we’ll use Powershell. Note how little coding needed.

And with Postman- this time to call our stored proc SpGetEmp with the (first) name parameter of “David”.

Tableau also has OData connectors. To use these, just add the full URL for your resource like this below for DimProducts (recall convention is base URL+name of object)

Same with PowerBI.

And my other favourite tool, SSIS. You just need to install the Odata drivers for SSIS. These are available in the SQL Service 2014 Feature Pack.

Other Configurations: What if I want JSON instead of XML or if I only want a subset of the data. No problems, like any REST API, just state so within your payload. To limit the load, just add the $top parameter and its value and to get JSON, just add  Accept headers with the value “application/json” like this below. As expected, we’re only getting two rows back and in JSON format for this call.

And doing the same thing via a browser..

So there you go, something extremely powerful and easy at the same time that can be built in minutes with just a few lines of code. But don’t let this fool you though because both WCF and Entity Framework have much much more to offer. For those that just want a public OData services to play with, try: https://services.odata.org/V3/Northwind/Northwind.svc/

Happy coding.

Sam