Exposing a SQL DB through a WCF Service using Entity Framework

Its been a long time since I have written a blog. I had to implement a SQL database for an application and we needed to have exposed as a WCF endpoint. While there are several tutorial on how to do this, i thought it will be a good idea to show the simple and the quick way that we followed

Things needed

  • SQL Server
  • Microsoft Visual Studio 2012

This are two parts, creating the service and then the Client DLL.

Part 1 : Creating the WCF Service

Step 1: Create the Database

Create a SQL database, its tables, its relationships as you normally would.If there are any tables with binary columns, then please be informed that it should be excluded from the WCF service.

Step 2: Creating the EDMX file

Create a new ASP.NET Empty Web Application in a new solution, call it what you like, I am it the TestDataModel

Once this is done, its time to add the EDMX file. In order to do so Right click and add new item.


Choose ADO.NET Entity Data model and give it the name TestDataModel.edmx

Select Generate from Database

Fill in the DB details


You can change the name of the entities, we are leaving it as it is


Choose the tables you want to expose using the WCF


You can please note the Model Namespace, or change it. Once you finish the step will be done


Step 2: Add a Nuget Package.

In order to create the WCF, just add the entity framework nuget package. Right click on references, and Click on manage Nuget Packages


Download and install the WCF Data Services Server


This will install all the OData Components needs by our project


Step 3: Create the Data Service

Right Click the Project Name –> Add –> WCF Data Service


Give it a name – I am calling it AzMgmtDataService (as my database is called AZMGMT)


This will create a file and open it


There are 2 main changes that we will do


1. Put the Name of the data source class – in this case azmgmtEntities and setting the rights – I am going to give rights to every one for the sake of simplicity.


Notice the two lines that are written. First one gives the rights to all users, off course you can fine tune this line. The second line is optional, i leave it on for testing so that I can check the entity framework errors from the client. In production environment, please set the line to false


That’s it, the WCF service is ready, you can deploy it in a manner you choose fit. Run a debug and it should open a browser.

It may show a 404 not found or a forbidden page, please ensure the service name (WCF service name is provided in the URI)


In my case, I added /AzmgmtDataService.svc


This shows that the WCF service is working. You can use Linqpad to test it.



Part 2: Creating the Client DLL

This is an optional step, but I recommend it as it makes the client applications easy to use.

We will use a visual studio utility (DataSvcUtil.exe)to generate a client class

Open a Visual Studio command prompt and execute

DataSvcUtil.exe /out:ClientClass.cs /uri:”http://localhost:57125/AzMgmtDataService.svc”



You should get a clientclass.cs file created

The File should look like this


Add a new project to the solution of the type Class Library. Call it AzmgmtDataModelClient

Copy paste the contents to the Class1.cs – Rename the class1.cs as AzmgmtDataModelClient.cs


Add the references



Build the Client Project. You will have got the DLL file to use it in your applications


FINAL (Optional)

The below shows how to use the client in other applications. So we create a test console application to do this

Add a new project of the type Console application. Add the references (All the microsoft Odata references that we added in the client class and additionally the client dll)

We will add two lines of code


Please remember that your entities name, URL and the object name will be different. In my case, I have a table called BusinessUnit in the DB that I want to use.I can use a LINQ query in order to filter my results


Setting up multiple projects for debug: Please remember that the Web Project needs to run so that the client DLL can access it. so, we need to run both projects simultaneously, so, right click on the solution and select properties



In my case, i want to debug only my test application. The client generation project doesnt need to be debugged at all.

Click on the debug button and you will see a webpage and a console open. Since I have set a breakpoint in my console application its waiting for me


As you can see the application is working as intended.



I hope this has been the easiest way to expose a database using the WCF service. If you have any questions do let me know.


Popular posts from this blog

Juniper Aggregate Interfaces (LACP/No LACP)

HA Proxy for Exchange 2010 Deployment & SMTP Restriction

Configuring Multicasting with Juniper EX switches (Part 1)