.png)
.png)
Introducing $select and $expand support in Web API OData
In this new release of Web API we are expanding our OData support 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 Get()
{
return context.Customers.AsQueryable();
}
[Queryable(MaxExpansionDepth = 5)]
public SingleResult 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.
GET http://localhost:12345/odata/Customers?$select=Name HTTP/1.1 Host: localhost:12345 accept: application/json
Here is the response that we get:
HTTP/1.1 200 OK Content-Length: 415 Content-Type: application/json; charset=utf-8 Server: Microsoft-HTTPAPI/2.0 DataServiceVersion: 3.0 Date: Tue, 28 May 2013 23:58:17 GMT
{ "odata.metadata":"http://localhost:12345/odata/$metadata#Customers","value":[ { "Name":"Name 1" },{ "Name":"Name 2" },{ "Name":"Name 3" },{ "Name":"Name 4" },{ "Name":"Name 5" },{ "Name":"Name 6" },{ "Name":"Name 7" },{ "Name":"Name 8" },{ "Name":"Name 9" },{ "Name":"Name 0" } ] }
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:
SELECT [Extent1].[Id] AS [Id], N'bf436648-6bf3-4bd2-9639-2810e0a91f53' AS [C1], N'Id' AS [C2] FROM [dbo].[Customers] AS [Extent1]
Aditionally, we can also apply $select when we are retrieving just a single entity. We could send the following request using fiddler:
GET http://localhost:12345/odata/Customers(5)?$select=Name HTTP/1.1 Host: localhost:12345 accept: application/json
As the following table shows, we get back a Customer, but we only get back its name property:
HTTP/1.1 200 OK Content-Length: 100 Content-Type: application/json; charset=utf-8 Server: Microsoft-HTTPAPI/2.0 DataServiceVersion: 3.0 Date: Wed, 29 May 2013 00:01:21 GMT
{ "odata.metadata":"http://localhost:12345/odata/$metadata#Customers/@Element","Name":"Name 5" }
Here is a more complex example in which we use $select and $expand together:
GET http://localhost:18340/odata/Customers?$select=Id,Name,Orders/BillingAddress&$expand=Orders HTTP/1.1 accept: application/json Host: localhost:18340
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:
{ "odata.metadata":"http://localhost:18340/odata/$metadata#Customers","value":[ { "Orders":[ { "BillingAddress":{ "FirstLine":"FirstLine 1", "SecondLine":"SecondLine 1", "ZipCode":214, "City":"City 1", "Country":"Country 1" } },{ "BillingAddress":{ "FirstLine":"FirstLine 2", "SecondLine":"SecondLine 2", "ZipCode":20, "City":"City 2", "State":"State 2", "Country":"Country 2" } },{ "BillingAddress":{ "FirstLine":"FirstLine 3", "SecondLine":"SecondLine 3", "ZipCode":88, "City":"City 3", "State":"State 3", "Country":"Country 3" } } ], "Id":1, "Name":"Name 1" }, ... }
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 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 version 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.
In this new release of Web API we are expanding our OData support 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
.png "thumb")
.png)
