Last week Microsoft released the preview of Visual Studio 2013, and along with it came the ASP.NET and Web Tools for Visual Studio 2013 Preview. In this new release, we are expanding the OData support in Web API to include support for $select and $expand, two of the most popular OData query operators. In this blog post we’ll cover the following topics:

• What are $select and $expand.
• How to use $select and $expand in an application.
• Configuration options and formatter support for $select and $expand.

What are $select and $expand

The $select operator allows a client to pick a subset of the properties of an entity to be retrieved when querying a feed or a single entity. The $expand operator allows a client to retrieve related entities for a given navigation property in line with the entities being retrieved.

By using $select and $expand, we can make sure that we get the data we need in an optimal way.

For example, we could use $select to return only the Id and Name properties of an entity, and we could use $expand to retrieve a customer and its related Orders on a single query.

How to use $select and $expand in an application

Let’s see an example, we will start by retrieving only the Name property of a feed of customers. In order to do that, we need to do 3 things:

• Create a model like the one in the following diagram:

• Write a CustomersController that returns a feed of Customers or a specific Customer for a given key:

[ODataNullValue]
public class CustomersController : ODataController
{
    ShoppingContext context;

    public CustomersController()
    {
        context = new ShoppingContext();
    }

    [Queryable(MaxExpansionDepth = 5)]
    public IQueryable<Customer> Get()
    {
        return context.Customers.AsQueryable();
    }

    [Queryable(MaxExpansionDepth = 5)]
    public SingleResult<Customer> Get(int key)
    {
        return SingleResult.Create(context.Customers.Where(c => c.Id == key));
    }
}

• Setup the server and map the OData route as in the following code:

class Program
{
    static void Main(string[] args)
    {
        string serviceUrl = "http://localhost:12345";
        using (WebApp.Start(serviceUrl, Configure))
        {
            Console.WriteLine("Server listening on {0}", serviceUrl);
            Console.ReadKey();
        }
    }

    private static void Configure(IAppBuilder builder)
    {
        HttpConfiguration configuration = new HttpConfiguration();
        IEdmModel model = ShoppingEdmModel.GetModel();
        HttpServer server = new HttpServer(configuration);
        configuration.Routes.MapODataRoute("odata", "odata", model, 
        new DefaultODataBatchHandler(server));

        builder.UseHttpMessageHandler(server);
    }
}

Once we have our server up and running, we only need to send the following request using fiddler in order to get just the Name property of the Customers feed.

Here is the response that we get:

As we see on the response, we are still sending back a feed of customers, but we are only retuning their name, which allows us to improve the efficiency of our applications by reducing the amount of data returned from the database.

We can see this if we see the query that Entity Framework sends to the database when we query just for the Ids of the customers:

Aditionally, we can also apply $select when we are retrieving just a single entity. We could send the following request using fiddler:

As the following table shows, we get back a Customer, but we only get back its name property:

Here is a more complex example in which we use $select and $expand together:

In the example above, we are selecting the Id, Name, and the Billing Address property of the related Orders of a Customer.

Here is the first entity of the associated response:

In order to apply a $select or $expand to a single entity, we can write our action method in two ways. If we just return an entity, like a customer and we apply the [Queryable] attribute on top of the action, $select and $expand will work, but the underlying query won’t be optimized. Here is an example on how to do it.

[Queryable]
public Customer Get(int key)
{
    return context.Customers.Single(x => x.Id == key);
}

This is not a problem if we are using Linq to objects as our backend, but it might cause a performance issue if we are using Entity Framework as the backend, as if we perform an $expand operation and lazy loading is enabled, we will be triggering an extra query for each navigation property we are expanding.

In order to optimize queries to the backend when using $select and $expand, we can write a different version of this action that returns a special class, SingleResult, that allows the queryable attribute to access the query object, apply the $select and $expand operators, and that takes care of ensuring that only one entity is returned.

[Queryable]
public SingleResult<Customer> Get(int key)
{
    return SingleResult.Create(context.Customers.Where(x => x.Id == key));
}

Configuration options and formatter support for $select and $expand

When dealing with a real application, we need a way to configure operators like $expand so that a client (by mistake or maliciously, can’t send an expensive query to the service). In the case of $expand, we can configure the depth of the expansions that we allow our clients to perform in their queries so that they don’t cause a denial of service on the database. In order to do this, we can set the MaxExpansionDepth property on the ODataQueryOptions object or in the Queryable attribute.

In this release of web API the OData formatter and the Json formatter support $select and $expand. An example of $select and $expand queries can be found at the ODataQueryableSample in the aspnet samples codeplex project, and this functionality is available with visual studio 2013 preview, on the official Nuget package feed or in the nightly build Nuget feed. For more information, please look at http://aspnetwebstack.codeplex.com