Expanding results with associated entries using Simple.Data OData adapter

In the post where I introduced Simple.Data OData adapter I showed a few examples of retrieving single or multiple entities. What was common with those examples is that they presented technique to read just the requested resource type, even though there in can be useful to read associated entities at once. Soon after I wrote that blog post, Mark Rendle extended Simple.Data with support to retrieve related data using “With” clause. This was exactly what Simple.Data OData adapter lacked in order to implement its own support to expand results with associated entities. Now I am going to show how it works.

We will continue using Northwind example, since every .NET developer must have learned by heart all its entity types. First, we will retrieve a single category by its name and expand it with associated products. This is how the code will look (I will also show the test assertions to make it clear how to deal with request results):

var category = _db.Category.WithProducts().FindByCategoryName("Beverages");
Assert.Equal("Beverages", category.CategoryName);
Assert.True(category.Products.Count > 0);

And here is the generated request URI:


Now let’s retrieve all categories and expand its products. Here is the code

var categories = _db.Category.All().WithProducts().ToList();
Assert.True(categories.Count > 0);
Assert.True(categories[0].Products.Count > 0);

The request URI will be as follows:


If we swap the master resource type and start retrieving products with associated categories, the relationship type will change from one-to-many to many-to-one, as you see from this example:

var products = _db.Products.All().WithCategory().ToList();
Assert.True(products.Count > 0);
Assert.Equal("Beverages", products[0].Category.CategoryName);

The request URI for the example above:


Finally, we will look at self-joins. In Northwind database an employee may have a superior who is also an employee, so for each Employee entity there is a corresponding Employee entity associated via “Superior” relationship (can be null), and a set of Employee entities associated via “Subordinates” relationship (can be empty). Here’s how to retrieve a given employee with its superior:

var employee = _db.Employees.WithSuperior().FindByFirstNameAndLastName("Nancy", "Davolio");

And here’s the request URI:

Employees?$expand=Superior&$filter=FirstName+eq+%27Nancy%27 and LastName+eq+%27Davolio%27

And in case we need to expand an employee with its subordinates, the following code will do the trick:

var employees = _db.Employees.All().WithSubordinates().ToList();
Assert.True(employees.Count > 0);
Assert.True(employees[0].Subordinates.Count > 0);

The corresponding request URI:

Employees?$expand=Superior&$filter=FirstName+eq+%27Nancy%27 and LastName+eq+%27Davolio%27

Simple.Data OData adapter source code is available at GitHub, and the binaries can be obtained from NuGet using package ID “Simple.Data.OData”.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s