OData Expand

By FIVIL and Rick Anderson

This article demonstrates querying related entities using OData.

The ContosoUniversity sample is used for the starter project.

A malicious or naive client may construct a query that consumes excessive resources. Such a query can disrupt access to your service. Review Security Guidance for ASP.NET Core Web API OData before starting this tutorial.

Enable OData

Update Startup.cs with the following highlighted code:

public class Startup
{
    public Startup(IConfiguration configuration)
    {
        Configuration = configuration;
    }

    public IConfiguration Configuration { get; }

    // This method gets called by the runtime. Use this method to add services to the container.
    public void ConfigureServices(IServiceCollection services)
    {

        services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2);

        services.AddDbContext<SchoolContext>(options =>
           options.UseInMemoryDatabase("OData-expand"));

        services.AddMvc(option => option.EnableEndpointRouting = false);

        services.AddOData();
    }

    public void Configure(IApplicationBuilder app, IHostingEnvironment env)
    {
        if (env.IsDevelopment())
        {
            app.UseDeveloperExceptionPage();
        }
        else
        {
            app.UseExceptionHandler("/Error");
            app.UseHsts();
        }

        app.UseHttpsRedirection();

        app.UseMvc(routeBuilder =>
        {
            routeBuilder.EnableDependencyInjection();
            routeBuilder.Expand().Select();
        });
    }
}

The preceding code:

• Calls services.AddOData(); to enable OData middleware.
• Calls routeBuilder.Expand().Select() to enable querying related entities with OData.

Add a controller

Create new Controller named EnrollmentController and with the following action:

[HttpGet, EnableQuery]
public IQueryable<Enrollment> Get([FromServices]SchoolContext context) 
    => context.Enrollment;

The preceding code enables OData queries and returns enrollment entities SchoolContext.

$expand

OData expand functionality can be used to query related data. For example, to get the Course data for each Enrollment entity, include ?$expand=course at the end of the request path:

This tutorial uses Postman to test the web API.

• Install Postman

• Start the web app.

• Start Postman.

• Disable SSL certificate verification

  • From File > Settings (*General tab), disable SSL certificate verification.

    Warning

    Re-enable SSL certificate verification after testing the controller.

• Create a new request.

  • Set the HTTP method to GET.
  • Set the request URL to https://localhost:5001/api/Enrollment/?$expand=course($expand=Department). Change the port as necessary.

• Select Send.

• The Course data for each Enrollment entity is included in the response.

Expand depth

Expand can be applied to more than one level of navigation property. For example, to get the Department data of each Course for each Enrollment entity, include ?$expand=course($expand=Department) at the end of the request path. The following JSON shows a portion of the output:

[
    {
        "Course": {
            "Department": {
                "DepartmentID": 3,
                "Name": "Engineering",
                "Budget": 350000,
                "StartDate": "2007-09-01T00:00:00",
                "InstructorID": 3
            },
            "CourseID": 1050,
            "Title": "Chemistry",
            "Credits": 3,
            "DepartmentID": 3
        },
        "EnrollmentID": 1,
        "CourseID": 1050,
        "StudentID": 1,
        "Grade": 0
    },
    {
        "Course": {
            <!-- Deleted for brevity -->
]

By default, Web API allows the maximum expansion depth of two. To override the default, set the MaxExpansionDepth property on the [EnableQuery] attribute.

Security concerns

Consider disallowing expand:

• On sensitive data for security reasons.
• On non-trivial data sets for performance reasons.

In this section, code is added to prevent querying CourseAssignments related data.

Override SelectExpandQueryValidator to prevent $expand=CourseAssignments. Create a new class named MyExpandValidator with the following code:

public class MyExpandValidator : SelectExpandQueryValidator
{
    public MyExpandValidator(DefaultQuerySettings defaultQuerySettings) 
        : base(defaultQuerySettings)
    {

    }
    public override void Validate(SelectExpandQueryOption selectExpandQueryOption, 
        ODataValidationSettings validationSettings)
    {           
        if (selectExpandQueryOption.RawExpand.Contains(nameof(Course.CourseAssignments)))
        {
            throw new ODataException(
                $"Query on {nameof(Course.CourseAssignments)} not allowed");
        }

        base.Validate(selectExpandQueryOption, validationSettings);
    }
}

The preceding code throws an exception if $expand is used with CourseAssignments.

Create a new class named MyEnableQueryAttribute with the following code:

public class MyEnableQueryAttribute : EnableQueryAttribute
{
    private readonly DefaultQuerySettings defaultQuerySettings;
    public MyEnableQueryAttribute()
    {
        this.defaultQuerySettings = new DefaultQuerySettings();
        this.defaultQuerySettings.EnableExpand = true;
        this.defaultQuerySettings.EnableSelect = true;
    }
    public override void ValidateQuery(HttpRequest request, ODataQueryOptions queryOpts)
    {
        queryOpts.SelectExpand.Validator = 
                                       new MyExpandValidator(this.defaultQuerySettings);
        base.ValidateQuery(request, queryOpts);
    }
}

The preceding code creates the MyEnableQuery attribute. The MyEnableQuery attribute adds the MyExpandValidator, which prevents $expand=CourseAssignments

Replace the EnableQuery attribute with MyEnableQuery attribute in the EnrollmentController:

[HttpGet, MyEnableQuery]
public IQueryable<Enrollment> Get([FromServices]SchoolContext context) 
                                               => context.Enrollment;

In Postman:

• Send the previous Get request https://localhost:5001/api/Enrollment/?$expand=course($expand=Department). The request returns data because ($expand=Department) is not prohibited.
• Send a Get request for with ($expand=CourseAssignments). For example, https://localhost:5001/api/Enrollment/?$expand=course($expand=CourseAssignments)

The preceding query returns 400 Bad Request.