Exposing OData from an Entity Framework Model

After reading Scott Hansleman’s article on exposing OData for Stack Overflow, I thought it would be nice to update the previous post I did on ADO.NET data services to include the new WCF Data Services. WCF Data Services (formerly called ADO.NET Data Services, and “Astoria”) can expose OData to callers through a very simple interface. LINQPad was not available to query the interface at the time, so I will also discuss how to use LINQPad to write queries against a Data Service.

For my example, I am going to expose a VistaDB test database that shows SQL Commands, and examples of their syntax. It is a very simple model, but provides interesting data to query against (other than Northwind!). You can use any Entity Framework provider to perform these steps, they are not specific to VistaDB.

Being able to consume data across the web in a rest-ful manner is part of the power of OData, lots of applications that are powered by .NET are going to be able to consume OData services very easily. But the OData protocol is not just for .NET, PHP, Java, Javascript and others also have the ability to consume the data.

What is OData?

The Open Data Protocol (OData) is an open web protocol started by Microsoft to expose data using existing web technologies. HTTP, AtomPub (similar to RSS), and JSON are all supported. The protocol matches very closely the way web technologies work, and the URL is the primary operator on the data query. The HTTP verbs match very closely their CRUD operations. The URL has a very descriptive syntax that makes it easy to build queries by hand, or with any programming language. OData is not unique to .NET, although .NET sure makes it easy to expose and consume OData through WCF.

WCF Data Service

To expose OData, we will build a WCF Data Service and expose our VistaDB EF model. I am using Visual Studio 2010 and .NET 4 for this example. The WCF Data Service item template in Visual Studio makes it very easy to expose an Entity Framework model over a service based interface. You don’t have to use Entity Framework, but doing so makes it really easy to build and deploy. I believe you could expose a custom collection through the data service as well, but I have not tried this yet.

Creating the Entity Framework Model

I first created a Visual Studio 2010 Web Application targeted to .NET 4. Then through right clicking on the project Add - New Item and then choose the ADO.NET Entity Data Model.

This is a simple model against a VistaDB 4 database named CommandToolDB.vdb4. We have been using this internally to build up samples of SQL code for VistaDB and SQL Server, then flagging the differences in the database. This is not a completed project, so I am only including a sample of the dataset with this service.

We would like to eventually have this service exposed online and queryable through Data Builder. That would allow people to look up snippet examples of SQL Syntax and see the differences between VistaDB and SQL Server.

Creating an OData / WCF Data Service

Right click on the project, Add – New Item – WCF Data Service. I named the service VistaDBCommandService.svc. To add any class to be exposed through OData, all you have to do is change the class name in the DataService< ClassNameHere > definition. The default class generated by the template includes a comment in the class definition where you put your class name.

public class VistaDBCommandService : DataService< VistaDBCommandsEntities >

Since we are exposing the EF Model, I put the name of the entities class as the type to be exposed.

public static void InitializeService(DataServiceConfiguration config)
{
    // Give readonly access to all of the entities
    config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
    
    // Pagesize will change the max number of rows returned
    config.SetEntitySetPageSize("*", 25);

    config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
}

View the Feed Output

Now if you right click the service and say to view in a browser, you will see the following output (well, it will look like an RSS Feed if your browser knows how – so you may need to view source to see this ).

<service xml:base="http://localhost:1883/VistaDBCommandService.svc/"
    xmlns:atom="http://www.w3.org/2005/Atom" 
    xmlns:app="http://www.w3.org/2007/app" 
    xmlns="http://www.w3.org/2007/app"> 
  <workspace> 
    <atom:title>Default</atom:title> 
    <collection href="Commands"> 
      <atom:title>Commands</atom:title> 
    </collection> 
    <collection href="Databases"> 
      <atom:title>Databases</atom:title> 
    </collection> 
    <collection href="Statuses"> 
      <atom:title>Statuses</atom:title> 
    </collection> 
  </workspace> 
</service>

The links are all relative, and can be used to query the entities in the database.

Browse to the Commands Entity

Pointing by browser to the commands entity ends up looking something like this in my browser.

http://localhost:1883/VistaDBCommandService.svc/Commands

Not very pretty, is it?

But now you can write some queries against the data just from the URL parameters.

http://localhost:1883/VistaDBCommandService.svc/Commands()?$top=5&$select=SQLCommand,ExampleSyntax

That will give me the First 5 commands (top=5) and only return the SQLCommand, and ExampleSyntax instead of the entire object.

Ok, but who wants to write queries in the browser? We want LINQ!

LINQPad 4 Beta and OData

Grab the most recent LINQPad 4 beta for .NET 4 and follow along.

In LINQPad, click the Add Connection at the top of the left panel. The dialog will appear that allows you to choose what type of connection you want, and if you want an automatic data context built for you.

Choose the WCF Data Services option from that dialog and click Next. The LINQPad connection dialog then appears. Choose the Data Services radio button, and then enter the local service. In my case, it was the URL listed in the box. I found this by looking the browser window launched when I clicked view in browser for the service item of the website. Clicking test should bring up the Connection OK dialog.

Data Service in LINQPad

Now our data service is in LINQPad and looks like the image show here. The entities are exposed, and so are their relationships.

We can now write LINQ queries against our data service and see the results in a nice graphical way inside of LINQPad.

LINQPad knows how to query the WCF Data Service and dynamically built up a local model for querying against. The SQL output from LINQPad will now show the URL it used to query the service.

This is very similar to how Silverlight loads data exposed on remote servers without knowing anything about ADO.NET.

LINQ Query Against the Data Service

(from c in Commands
where c.Status.Description.Equals("Implemented") &&
    c.Database.Description.Equals("VistaDB")
select new { c.SQLCommand, c.ExampleSyntax }).Take( 5 )

This command queries the database to find those entries that are flagged as implemented, for the VistaDB Database, and gets the first five entries SQLCommand and ExampleSyntax columns.

Notice how I was able to drill down into the entities (c.Status.Description) and interact with the data very much like I would a local EF model.

The results look like this in the Results pane:

Viewing the SQL in LINQPad shows the following URL:

http://localhost:1883/VistaDBCommandService.svc/Commands()?$filter=(Status/Description eq 'Implemented') and (Database/Description eq 'VistaDB')&$top=5&$select=SQLCommand,ExampleSyntax

The entire LINQ statement is running on the server through that URL.

Consuming the Data through a .NET Application

Consuming the data feed through a .NET application is very easy. In your .NET application, right click and use the Add Service Reference, then point the dialog to your same service.

Adding the service reference will actually generate a client side proxy for your application to communicate with that looks like a full blown entity framework model. You can call it using code like a normal EF entities context, but the initialization must point to your URL. I hard coded it in the code below, but in a normal app you would put this in the app.config to allow for easier management of the service endpoint.

static void Main(string[] args)
{
    VistaDBCommandsEntities cs = new 
                       VistaDBCommandsEntities(new 
                               Uri("http://localhost:1883/VistaDBCommandService.svc"));


    var result = (from c in cs.Commands
                  where c.Status.Description.Equals("Implemented") &&
                      c.Database.Description.Equals("VistaDB")
                  select new { c.SQLCommand, c.ExampleSyntax }).Take(5);

    foreach (var r in result)
    {
        Console.WriteLine(r.SQLCommand + " : " + r.ExampleSyntax);
    }

}

Note that the entities are not IDisposable, so you cannot put them in a using statement.

Visual Studio 2010 Makes OData Easy

The combination of WCF Data Services and Entity Framework makes it VERY easy to expose your data in a rest-ful manner over the web. Take a look at the DataService options and you will find a very deep system for controlling who can query data, update, how many rows they can pull at once, etc.