Post
Ask Question

$expand, $select, And $value In ASP.NET Web API 2 OData

Introduction

OData (Open Data Protocol) is an open protocol for sharing the data. It becomes a very popular way to communicate. Web API is also supporting OData. In my previous article, we learned Filter Expressions in OData URIs with Web API.

Web API 2 supports the $expand, $select and $value options with OData. These options are very helpful in controlling the response back from server.

$expand: This is useful in retrieve the related entities and it to be included in response.
$select: This is useful to select the subset of properties.
$value: This is useful in retrieve the raw value of the property.

Example:

Here, I am using entity framework as data source and I have created two tables named Employee and Department. Each employee has one department. The following figure shows the relation between the entities.

entities

The following are classes that define in the entity model. In this example, I am using entity framework as a data source.

The following code also contains the entity model definition.
  1. [Table("Employee")]  
  2. public partial class Employee   
  3. {  
  4.     public int Id   
  5.     {  
  6.         get;  
  7.         set;  
  8.     }  
  9.     [Required]  
  10.     [StringLength(50)]  
  11.     public string Name   
  12.     {  
  13.         get;  
  14.         set;  
  15.     }  
  16.     public int DepartmentId   
  17.     {  
  18.         get;  
  19.         set;  
  20.     }  
  21.     [Column(TypeName = "money")]  
  22.     public decimal ? Salary   
  23.         {  
  24.             get;  
  25.             set;  
  26.         }  
  27.         [StringLength(255)]  
  28.     public string EmailAddress   
  29.     {  
  30.         get;  
  31.         set;  
  32.     }  
  33.     [StringLength(50)]  
  34.     public string PhoneNumber   
  35.     {  
  36.         get;  
  37.         set;  
  38.     }  
  39.     public string Address   
  40.     {  
  41.         get;  
  42.         set;  
  43.     }  
  44.     public virtual Department Department   
  45.     {  
  46.         get;  
  47.         set;  
  48.     }  
  49. }  
  50. [Table("Department")]  
  51. public partial class Department   
  52.     {  
  53.     public Department()   
  54.     {  
  55.         Employees = new HashSet < Employee > ();  
  56.     }  
  57.     public int DepartmentId   
  58.     {  
  59.         get;  
  60.         set;  
  61.     }  
  62.     [StringLength(50)]  
  63.     public string DepartmentName   
  64.     {  
  65.         get;  
  66.         set;  
  67.     }  
  68.     public virtual ICollection < Employee > Employees {  
  69.         get;  
  70.         set;  
  71.     }  
  72. }  
  73. //entity model definition   
  74. public partial class EntityModel: DbContext   
  75.     {  
  76.     public EntityModel(): base("name=EntityModel") {}  
  77.     public virtual DbSet < Department > Departments   
  78.     {  
  79.         get;  
  80.         set;  
  81.     }  
  82.     public virtual DbSet < Employee > Employees   
  83.     {  
  84.         get;  
  85.         set;  
  86.     }  
  87.     protected override void OnModelCreating(DbModelBuilder modelBuilder)   
  88.     {  
  89.         modelBuilder.Entity < Department > ().Property(e => e.DepartmentName).IsUnicode(false);  
  90.         modelBuilder.Entity < Department > ().HasMany(e => e.Employees).WithRequired(e => e.Department).WillCascadeOnDelete(false);  
  91.         modelBuilder.Entity < Employee > ().Property(e => e.Salary).HasPrecision(19, 4);  
  92.     }  
  93. }  
Here, Employee class defines the navigation property for Department and same as Department class has navigation property Employee class.

In the OData controller, I have defined two methods for retrieving the data. Following is controller code. Here my GET method returns the IQueryable. EnableQuery Attribute enables us to query using OData query syntax.
  1. namespace WebAPITest.Controllers  
  2. {  
  3.     using System.Linq;  
  4.     using System.Net;  
  5.     using System.Net.Http;  
  6.     using System.Web.OData;  
  7.     public class EmployeeController: ODataController  
  8.     {  
  9.         EntityModel context = new EntityModel();  
  10.         [EnableQuery]  
  11.         public IQueryable < Employee > Get()  
  12.         {  
  13.                 return context.Employees;  
  14.             }  
  15.             /// <summary>  
  16.             /// Read Operation  
  17.             /// </summary>  
  18.             /// <param name="key">Key</param>  
  19.             /// <returns></returns>  
  20.         public HttpResponseMessage Get([FromODataUri] int key)  
  21.         {  
  22.                 Employee data = context.Employees.Where(k => k.Id == key).FirstOrDefault();  
  23.                 if (data == null)  
  24.                 {  
  25.                     return Request.CreateResponse(HttpStatusCode.NotFound);  
  26.                 }  
  27.                 return Request.CreateResponse(HttpStatusCode.OK, data);  
  28.             }  
  29.             /// <summary>  
  30.             /// Dispose  
  31.             /// </summary>  
  32.             /// <param name="disposing"></param>  
  33.         protected override void Dispose(bool disposing)  
  34.         {  
  35.             context.Dispose();  
  36.             base.Dispose(disposing);  
  37.         }  
  38.     }  
  39. }  
To test the application, I am using Telerik's Fiddler. It’s free. Using Fiddler, we can analyze the input / output of URL and also able to POST data without any user interface.

Using $expand

When we query OData controller, the default response does not include the related entity. For example, if I am querying employee data, it does not include department data in to the response.

URL: http://localhost:24367/Employee

Output:

output

The client can use $expand to get related entity.

URI: http://localhost:24367/Employee?$expand=Department

Output

output

We can pass comma-separated list of navigation properties to $expand options for expanding multiple navigation properties.

http://localhost:24367/Employee?$expand=Department,OtherNavigationProperty

We can also expand more than one level of navigation property. The following example includes departments for all employees and also includes level1 navigation property for department.

http://localhost:24367/Employee?$expand=Department/level1

By default, web API set the maximum expansion to 2. It means we can only expand entity up to two levels. If we use complex request like "$expand=Department/Level1/Level2/Level3", we might get inefficient query result. MaxExpansionDepth property helps us to override this default value.

code

Using $select

The $select option is used to retrieve subsets of properties in response. For example, to get only the employee name and salary, use the following query.

URI: http://localhost:24367/Employee?$select=Name,Salary

Output

Output

We can also use $select option with $expand option. The following request expands Department and select employee name and salary.

URI: http://localhost:24367/Employee?$select=Name,Salary&$expand=Department

Output

Output

Using $value

The $value option is used to get individual properties of an Entity. There are two ways to get individual properties from an entity. We can get the response in either OData format or get the raw value of the property.

We need to add method to the controller named GetProperty here property is a name of the property. For example, if we want to get name property of employee entity the method name becomes "GetName" and definition is as following.
  1. public async Task < IHttpActionResult > GetName(int key)   
  2. {  
  3.     Employee employee = await context.Employees.FindAsync(key);  
  4.     if (employee == null)   
  5.     {  
  6.         return NotFound();  
  7.     }  
  8.     return Ok(employee.Name);  
  9. }  
The following request gets the property value in OData format.

URI: http://localhost:24367/Employee(1)/Name

Output

Output

Append $value to the URI to get the raw value of the property.

URI: http://localhost:24367/Employee(1)/Name/$value

Output

Output

Summary

Web API 2 supports the $expand, $select and $value options with OData. These options are very helpful in controlling the response back from server.


Similar Articles