$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.



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.

[Table("Employee")]
public partial class Employee
{
    public int Id
    {
        get;
        set;
    }
    [Required]
    [StringLength(50)]
    public string Name
    {
        get;
        set;
    }
    public int DepartmentId
    {
        get;
        set;
    }
    [Column(TypeName = "money")]
    public decimal ? Salary
        {
            get;
            set;
        }
        [StringLength(255)]
    public string EmailAddress
    {
        get;
        set;
    }
    [StringLength(50)]
    public string PhoneNumber
    {
        get;
        set;
    }
    public string Address
    {
        get;
        set;
    }
    public virtual Department Department
    {
        get;
        set;
    }
}
[Table("Department")]
public partial class Department
    {
    public Department()
    {
        Employees = new HashSet < Employee > ();
    }
    public int DepartmentId
    {
        get;
        set;
    }
    [StringLength(50)]
    public string DepartmentName
    {
        get;
        set;
    }
    public virtual ICollection < Employee > Employees {
        get;
        set;
    }
}
//entity model definition
public partial class EntityModel: DbContext
    {
    public EntityModel(): base("name=EntityModel") {}
    public virtual DbSet < Department > Departments
    {
        get;
        set;
    }
    public virtual DbSet < Employee > Employees
    {
        get;
        set;
    }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity < Department > ().Property(e => e.DepartmentName).IsUnicode(false);
        modelBuilder.Entity < Department > ().HasMany(e => e.Employees).WithRequired(e => e.Department).WillCascadeOnDelete(false);
        modelBuilder.Entity < Employee > ().Property(e => e.Salary).HasPrecision(19, 4);
    }
}

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.

namespace WebAPITest.Controllers
{
    using System.Linq;
    using System.Net;
    using System.Net.Http;
    using System.Web.OData;
    public class EmployeeController: ODataController
    {
        EntityModel context = new EntityModel();
        [EnableQuery]
        public IQueryable < Employee > Get()
        {
                return context.Employees;
            }
            /// <summary>
            /// Read Operation
            /// </summary>
            /// <param name="key">Key</param>
            /// <returns></returns>
        public HttpResponseMessage Get([FromODataUri] int key)
        {
                Employee data = context.Employees.Where(k => k.Id == key).FirstOrDefault();
                if (data == null)
                {
                    return Request.CreateResponse(HttpStatusCode.NotFound);
                }
                return Request.CreateResponse(HttpStatusCode.OK, data);
            }
            /// <summary>
            /// Dispose
            /// </summary>
            /// <param name="disposing"></param>
        protected override void Dispose(bool disposing)
        {
            context.Dispose();
            base.Dispose(disposing);
        }
    }
}

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:



The client can use $expand to get related entity.

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

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.



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



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



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.

public async Task < IHttpActionResult > GetName(int key)
{
    Employee employee = await context.Employees.FindAsync(key);
    if (employee == null)
    {
        return NotFound();
    }
    return Ok(employee.Name);
}

The following request gets the property value in OData format.

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

Output



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

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

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.