10 OData FAQs

Friday, September 12, 2014 by Rainer Stropek

I have been speaking about OData at conferences for many years now. As a database guy, the standard fascinated me from the first day on. It perfectly combines the flexibility that SQL offers in the RDBMS-world with modern web and cloud technologies.

Today will have another talk about OData at NRWConf conference in Wuppertal, Germany. It will be a 100% code-only talk so I thought it might be useful to provide some of the information I will cover in my session in a blog article. However, I have written quite some OData-related articles in my blog before. So this one has to be a bit different. I decided to write in in a FAQ style and answer some questions I regularly get when talking with people about OData.

You can find the entire source code in my GitHub repository. Feel free to download it, follow along, or just experiment with the code.

Description of the sample used in this article

Why OData and not just plain web API?
What about metadata?
Can I generate C# proxy code for OData services?
Is there something like stored procedures or functions to encapsulate logic?
Can I restrict users so that they can only use certain query options?
How does ASP.NET Web API relate to WCF Data Services?
Can I still use OData if I don't use Entity Framework?
Is OData already supported by major tool vendors?
Is OData just for reading data?
What about authentication and authorization?

The Sample

I would like to demonstrate different aspects of OData based on a simple but not trivial example. Here is the class diagram for the sample. It consists of the classical customers - order header - order details - products data structure.

ClassDiagram
Note that the OrderManagementContext class includes a helper method ClearAndFillWithDemoData to generate some demo data. The sample solution contains a helper command line tool ODataFaq.DemoDataGenerator that you can use to generate demo data. It assumes that you have localdb installed ( Server=(localdb)\v11.0) and it contains an empty sample database called ODataFaq.

Why OData and not just plain web API?

Today, only very few people still write two-tier applications where a client application (e.g. WPF) accesses a database (e.g. SQL Server) directly (e.g. using ADO.NET and TDS in the background). Most applications consist of at least three tiers:

Client application (e.g. Windows Store app, mobile app, browser app)
Web services (e.g. REST services based on HTTP and JSON)
Database (e.g. SQL Server)

In the good old days you might have used WCF to create web services. Today, most people prefer Microsoft’s ASP.NET Web API library. It makes it quite simple to create REST-based web services.

Let’s think about how we would design a REST web service for maintaining our customers in the example shown above. We would likely offer a service to get all customers using HTTP GET at e.g. http://localhost:12345/api/Customer. The ASP.NET Web API code for this is really simple:

public class CustomerWebApiController : ApiController
{
	[HttpGet]
	public IEnumerable<customer> Get()
	{
		using (var context = new OrderManagementContext())
		{
			return context.Customers.ToArray();
		}
	}
}

Note that the sample solution in my GitHub repository contains an OWin/Katana self-hosting command line program ODataFaq.SelfHostService that you can use to try and debug the web API. It also contains the code necessary to setup the web API routes. I will not include it in the blog post to keep the text clean. If you are interested in this aspect of the sample, please download the sample code.

However, in practice that would not be sufficient. A client application will likely need to load a single customer, too. So we add a second API e.g. at http://localhost:12345/api/Customer/<customerid>:

public class CustomerWebApiController : ApiController
{
	[...]
	[HttpGet]
	public Customer Get(Guid id)
	{
		using (var context = new OrderManagementContext())
		{
			return context.Customers
				.SingleOrDefault(c => c.CustomerId == id);
		}
	}
}

Imagine your client application allows the user to filter customers by country. Of course it could use the get all customers service mentioned above and do the filtering on the client-side. This would be very inefficient. So we have to add a third service in order to let the database do the heavy lifting of filtering our customers. The service could life at e.g. http://myserver/api/CustomerByCountry/<countryisocode>.

public class CustomerByCountryWebApiController : ApiController
{
	[HttpGet]
	public IEnumerable<Customer> Get(string countryIsoCode)
	{
		using (var context = new OrderManagementContext())
		{
			return context.Customers
				.Where(c => c.CountryIsoCode == countryIsoCode)
				.ToArray();
		}
	}
}

Now think about where this approach would lead us to in a more complex, real-world example. You would likely create dozens or even hundreds of different services to access your database via the web service layer. Each time the client application’s developer wants to add a feature that needs another kind of filtering or sorting, you need to extend and deploy the service layer. In practice, this is often not done. Generic data access functions like get all customers are used and people end up having performance problems because they do not use the database for what it is good at (efficient querying) and transfer an unnecessary large amount of data.

Wouldn’t it be nice if we had a more generic web service? The approach shown above reminds me a bit of the "good" old times when I had been programming dBase and BTrieve. At that time, SQL’s SELECT statement was a huge step forward. Why not creating a single web service that accepts something like a SELECT statement? Well, OData does exactly that.

Here is the code for adding an OData endpoint to our web API:

[ODataRoutePrefix("Customer")]
public class CustomerController : ODataController
{
	private OrderManagementContext context = new OrderManagementContext();
	[EnableQuery]
	public IQueryable<customer> Get()
	{
		return context.Customers;
	}
	protected override void Dispose(bool disposing)
	{
		base.Dispose(disposing);
		if (disposing)
		{
			this.context.Dispose();
			GC.SuppressFinalize(this);
		}
	}
}

Note that return type of the Get method is not IEnumerable but IQueryable. That enables queries to go all the way through to the underlying database ending up in a WHERE clause in the SQL SELECT statement.

Let us look at some sample OData queries that can be sent to the OData endpoint shown above (note that my sample uses OData v4 so its new filter possibilities can be used):

http://localhost:12345/odata/Customer will give us a list of all customers.
http://localhost:12345/odata/Customer?$filter=CountryIsoCode eq 'AT' returns all customers from Austria.
http://localhost:12345/odata/Customer?$expand=Orders will join the Customer and the OrderHeader tables and return the nested result in a single request.
http://localhost:12345/odata/Customer?$select=CompanyName will return only a single column per customer.
http://localhost:12345/odata/Customer?$filter=concat(CountryIsoCode,'!') eq 'AT!' doesn’t make very much sense but demonstrates the use of built-in functions.

As you can see, a single OData endpoint acts as a generic web API for querying. Ok, it is not SELECT anymore. It is a different query language that fits perfectly into URLs and that is defined in the OData OASIS standard (do check out this link if you want to learn more details about all the query options that OData supports).

What about metadata?

Imagine the web service developer and the client developer are not the same person. They might even be located in different organizations. How should the client developer know which web APIs are available? How should she know which fields e.g. a customer consists of? She will have to read the documentation (if there is one and if it is complete and if it is up to date ... - you know what I mean).

Metadata is another thing that is great about OData. You will get it for free in a format that is called CSDL). You can consume it manually or use tools like Visual Studio to e.g. auto-generate proxy code (in case you are using a typed language like C# or TypeScript).

In the example shown above, you can ask for metadata using the URL http://localhost:12345/odata/$metadata:

Can I generate C# proxy code for OData services?

Let’s use the metadata shown above to generate a C# proxy in a console application. For this, we use a Visual Studio extension from Microsoft. First, add an OData Client to your console application project:

Next, enter your OData metadata URL and configure the client:

Build your program and you will get auto-generated proxy classes based on service metadata. You can now use Linq to query your OData service:

class Program
{
	static void Main(string[] args)
	{
		var container = new Container(
			new Uri("http://localhost:12345/odata/", UriKind.Absolute));
		foreach (var c in container.Customer
			.Where(c => c.CountryIsoCode == "AT"))
		{
			Console.WriteLine(c.CompanyName);
		}
	}
}

Is there something like stored procedures or functions to encapsulate logic?

Of course there is. You can define actions and functions. They can be unbound, bound to a single entity (e.g. customer), or bound to a collection (e.g. customers).

Here is an example for a function that is bound to the Customers entity. It should return all customers that have bought at least one product from the category BIKE. This is a rather complex query and therefore it makes sense to encapsulate it in a function.

[ODataRoutePrefix("Customer")]
public class CustomerController : ODataController
{
	[...]
	[EnableQuery]
	[ODataRoute("Default.OrderedBike")]
	[HttpGet]
	public IQueryable<customer> OrderedBike()
	{
		return from c in this.context.Customers
				where c.Orders.Count(o => o.OrderDetails.Count(od => od.Product.CategoryCode == "BIKE") > 0) > 0
				select c;
	}
}

You can access this function using the URL http://myserver/odata/Customer/Default.OrderedBike(). The database will handle the complex query with nested sub-selects.

By the way, did you recognize that OrderedBike returns an IQueryable? Because of that, you can combine the function with additional OData query elements like $filter: http://myserver/odata/Customer/Default.OrderedBike()?$filter=CountryIsoCode eq 'CH'. In this example, the $filter clause is not executed on the client. It is combined with the function’s complex LINQ query so that SQL Server’s powerful query engine is used. The following screenshot shows the query logged by Visual Studio's IntelliTrace:

Here you can learn more about actions and functions in OData v4.

Can I restrict users so that they can only use certain query options?

This is possible, too. You can define which query options you want to support using the EnableQuery or Queryable attribute.

Here you can learn more about OData security considerations.

How does ASP.NET Web API relate to WCF Data Services?

Before ASP.NET Web API learned to speak OData, Microsoft provided a library called WCF Data Services (aka Astoria As the name indicates, it is not based on the latest and greatest OWin/Katana technology stack. However, it is still maintained (e.g. see recently published WCF Data Services EF Provider).

WCF Data Services uses OData v3 instead of v4. This needs not to be a disadvantage as many tools including Microsoft Office Excel and LinqPad do not support OData v4 yet.

The beauty of WCF Data Services is that it makes it super simple to publish your entire Entity Framework model as an OData Feed with just two lines of code:

public class ODataFaqService : EntityFrameworkDataService<ordermanagementcontext>
{
    public static void InitializeService(DataServiceConfiguration config)
    {
		config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
        config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V3;
    }
}

Just like ASP.NET Web API, WCF Data Services offers many options for securing and extending (e.g. with Interceptors) your OData services. Here you can learn more about it if you are interested.

You can use Excel to try the OData service:

You can also try LinqPad:

So when should you use ASP.NET Web API’s OData endpoint and when to go for WCF Data Services? Here is my personal opinion:

If you have a large, complex entity framework project and you need to make it available for a client application that is under your control, WCF Data Services is still a valid option.
If you want to develop a public OData service and you want to fine-tune and control it in detail, I would recommend using ASP.NET Web API.
If you start a new project and you don't know which one to choose, I would tend to ASP.NET Web API as it builds on OWin/Katana which enables a lot of interesting scenarios (e.g. run it on an offline-enabled client).

Can I still use OData if I don't use Entity Framework?

Of course you can. If you have a data source that supports IQueryable, adding an OData endpoint should not be a big problem. Without that, you can still build an OData service but it will be much more work.

The basis of Microsoft's OData implementation is the ODataLib. You can the corresponding NuGet package at https://www.nuget.org/packages/Microsoft.OData.Core/. It contains classes to manually build OData's Entity Data Model (EDM), to parse OData query strings, etc. You can build on this and add your own custom backend.

The OData endpoint for our own product time cockpit has been built like that. We do not use Entity Framework as we offer a completely customizable data model. You can add tables, columns, relations, validation rules, etc. at runtime. Therefore we cannot make use of design-time code generation and compilation. We used the ODataLib to translate OData requests into requests to our own data access layer and back.

Is OData already supported by major tool vendors?

The popularity of OData has been rising in the last few years remarkably. The driving forces behind the OData OASIS standard are Microsoft and SAP. Both companies support OData in many of their products (e.g. SharePoint, Microsoft Azure, etc.).

Many BI tools support OData sources already. This also includes Microsoft's Excel-based Power BI tools. I included a screenshot of Excel accessing an OData feed above. PragmatiQa's XOData tool also demonstrates why having a standard is so important: People can write generic tools, in this case a generic query builder, that can work with numerous different source systems.

In our own software time cockpit, we also offer a read- and write-enabled OData endpoint. Our customers use it for reporting purposes, for writing time cockpit add-ons, and for automating routine tasks (e.g. scheduled checking for projects with budget overrun).

Is OData just for reading data?

No, it fully supports changing data (insert, delete, replace, merge), too. You can even group multiple operations in so called batches and make the server run them in the context of a DB transaction. However, you are not forced to allow changing data. If you just want to publish a read-only endpoint for your users for e.g. reporting purposes, that is fine, too.

The interesting thing about OData is that it does also standardize the serialization format of data. Data types, representation of relations, nested objects, etc. are well defined. Additionally, the OData's metadata format is extensible so you can add your own annotations.

Here you see the sample implementation that enables inserting of new customers in our ASP.NET Web API OData implementation:

[HttpPost]
public async Task<ihttpactionresult> Post([FromBody] Customer customer)
{
	context.Customers.Add(customer);
	await context.SaveChangesAsync();
	return Created(customer);
}

In WCF Data Services it is even simpler to enable inserting customers:

public class ODataFaqService : EntityFrameworkDataService<ordermanagementcontext>
{
    public static void InitializeService(DataServiceConfiguration config)
    {
		config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
		// Add the following line to enable writing new customers
		config.SetEntitySetAccessRule("Customers", EntitySetRights.AllRead | EntitySetRights.WriteAppend);
        config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V3;
    }
}

Here you see how we can now add a new customers using the Fiddler:

What about authentication and authorization?

OData does not cover authentication or authorization. However, it is a REST web service and therefore you can use existing standards from that domain for auth (e.g. basic auth, bearer tokens, OAuth2, Open ID Connect, etc.).

In my example I want to demonstrate how to do authorization using the OAuth2 protocol with its Resource Owner Password Credentials Grant flow. With that, accessing the OData service is a two-step process:

First, you need to get a token by presenting username and password.
Next, you need to sent the token in the Authorization header of subsequent OData requests.

Microsoft offers a ready-made OAuth2 middleware for OWin/Katana. I will use it and add a very simple password validation logic (username and password simply have to match). Additionally, I add an IsAdmin claim if the username is admin. Your implementation would probably contain a more sophisticated logic with additional claims.

private static void SetupOauthServer(IAppBuilder app)
{
	app.UseOAuthAuthorizationServer(new OAuthAuthorizationServerOptions
	{
		AllowInsecureHttp = true,
		TokenEndpointPath = new PathString("/token"),
		AccessTokenExpireTimeSpan = TimeSpan.FromHours(8),
		Provider = new DummyAuthorizationProvider()
	});
	app.UseOAuthBearerAuthentication(new OAuthBearerAuthenticationOptions());
}
private class DummyAuthorizationProvider : OAuthAuthorizationServerProvider
{
	public static Task FinishedTask = Task.FromResult(0);
	public override Task ValidateClientAuthentication(OAuthValidateClientAuthenticationContext context)
	{
		// No validation code -> all clients are ok
		context.Validated();
		return FinishedTask;
	}
	public override Task GrantResourceOwnerCredentials(OAuthGrantResourceOwnerCredentialsContext context)
	{
		// If username and password are equal, they are ok
		if (context.UserName != context.Password)
		{
			context.Rejected();
			return FinishedTask;
		}
		// Build claims identity
		var identity = new ClaimsIdentity("OAuth2");
		identity.AddClaim(new Claim("User", context.UserName));
		if (context.UserName == "admin")
		{
			identity.AddClaim(new Claim("IsAdmin", "IsAdmin"));
		}
		context.Validated(identity);
		return FinishedTask;
	}
}

Now we can protect our OData API using the Authorize attribute or in code by manually inspecting the claims of the user:

[Authorize]
[ODataRoutePrefix("Customer")]
public class CustomerController : ODataController
{
	[...]
	[EnableQuery]
	public IHttpActionResult Get()
	{
		if (!string.IsNullOrWhiteSpace(((ClaimsPrincipal)Thread.CurrentPrincipal).Claims.FirstOrDefault(c => c.Type == "IsAdmin").Value))
		{
			return Ok(context.Customers);
		}
		return Unauthorized();
	}
	
	[...]
}

If we try to access our OData service now without a token, we get an Unauthorized error. We have to acquire a token first.

Once we received the token, we can pass it to subsequent OData requests: