Update: see the third post here.

Introduction

This is the second post on my series on using OData with ASP.NET Core 3. You can find the first here.

Querying

We’ve seen how we can expose an object model to OData. In the first post I used Entity Framework Core, but you don’t need to use any ORM.

Where OData really excels is in querying: you can perform LINQ-style queries over the URL. These include:

• Filtering
• Sorting
• Projections
• Pagination
• Counting
• Navigation property expansions

By default, when you access the entity set’s endpoint, you get all records, but you can enable querying over them. This needs to be done globally first, when you define the endpoint:

app.UseEndpoints(endpoints =>
{
    endpoints.MapODataRoute("odata", "odata", GetEdmModel(app.ApplicationServices));
    endpoints.Select().Expand().OrderBy().Filter().Count();
});

Let’s have a look at the extension methods after the route registration. Don’t worry, I will show examples in a moment.

• The Select extension method allows projections over an entity, that is, selecting only parts of it
• Expand is used to allow expansions, for example, while retrieving an entity, similar to what Include does in Entity Framework Core – in fact, it translates to it
• OrderBy is self explanatory. without it you won’t be able to sort the results of your query
• Filter is what permits querying
• Finally, Count is used to allow returning only the count, not the actual results

These are global settings, but we also need to enable them at the configuration, per entity, when we build the EDM Data Model (the GetEdmModel method I’ve shown previously):

builder
    .EntitySet<Parent>(“Parents”)
    .EntityType
    .Select()
    .Expand()
    .OrderBy()
    .Filter()
    .Count();

Finally, we also need to enable it in the action method that returns a query (IQueryable<T>), regardless of whether or not it is wrapped in an IActionResult:

[ODataRoute]

[EnableQuery]

public IQueryable<Parent> Get()

Once we do this, we can now query the entity set on the URL, but first, we need the [EnableQuery] attribute. This is what allows us to query over the results!

If you don’t want to decorate all your action methods with [EnableQuery], we can also do this globally, for any queryable action methods:

services.AddODataQueryFilter();

This has the advantage (or disadvantage) that it applies to all methods, unless we specifically tell OData that querying is not allowed.

As for querying, we have a number of options, I’m going to show just the simplest:

Filter by a property’s value:

/odata/Parents?$filter=Name eq ‘Ricardo Peres’

Sort by one property’s values descending:

/odata/Parents?$orderby=Name desc

Select just a single property:

/odata/Parents?$select=Name

Skip 10 records and retrieve the next 5, ordered by a property:

/odata/Parents?$skip=10&$top=5&$orderby=Name

Filter by a property’s value and return the count:

/odata/Parents?$filter=contains(Name,'Peres')&$count=true

Expand a collection property:

/odata/Parents?$expand=Children

The main keywords are:

• $filter: used for specifying conditions
• $orderby: for sorting, either ascending or descending
• $select: projections
• $top: getting only some records
• $skip: skipping some records
• $count: getting the count of the returned records together with them
• $expand: expansions (include navigation properties)

Some of the options can be enhanced, for example:

Filter by several conditions:

/odata/Parents?$filter=Id eq 1 or Id eq 2

/odata/Parents?$filter=Id eq 1 and Name eq 'abc'

Where property value is in list:

/odata/Parents$filter=Id in (1, 2)

Sort by two property’s values, one descending and the other ascending:

/odata/Parents?$orderby=Name desc,Id asc

Filtering an expansion:

/odata/Parents?$expand=Children($filter=Name eq 'A Child')

I won’t go through all of the possible expressions, but the full OData specification is available here.

Setting Limits

We’ve seen in the beginning, while defining the OData route, that we can tell OData what should it support (select, filter, orderby, expand). We can also define the maximum amount of records to return:

app.UseEndpoints(endpoints =>
{
    endpoints.MapODataRoute("odata", "odata", GetEdmModel(app.ApplicationServices));
    endpoints.Select().Expand().OrderBy().Filter().Count().MaxTop(100);
});

Notice the MaxTop extension method, it defines the global maximum amount of records that can be returned by an OData endpoint. But we can also configure it on the action method, using the [EnableQuery] attribute:

[ODataRoute]

[EnableQuery(MaxTop = 10)]

public IQueryable<Parent> Get()

The [EnableQuery] attribute allows you to define a lot of other restrictions:

• AllowedArithmeticOperations: the allowed operators (add, divide, multiply, subtract, etc)
• AllowedFunctions: the allowed date and numeric functions (year, month, day, floor, ceiling, round, etc)
• AllowedLogicalOperators: the allowed logical operators (equal, not equal, and, or, greater than, lesser than, etc)
• AllowedOrderByProperties: the properties by which rhe result can be sorted
• AllowedQueryOptions: all of the query options (select, expand, orderby, filter, skip, top, count, etc)
• MaxAnyAllExpressionDepth: the maximum depth to be applied to any or all expressions
• MaxExpansionDepth: the maximum depth for expansions using the $expand keyword
• MaxNodeCount: the maximum set of expressions in a $filter
• MaxOrderByNodeCount: the maximum set of order by properties
• MaxSkip: the maximum set of rows to skip
• MaxTop: the maximum page size
• PageSize: the maximum number of records to return

All of these options can also be specified through the AddODataQueryFilter extension method, but for global restrictions. It is at least advisable to define a maximum number of return records (MaxTop) and also the maximum number of expansions (MaxExpansionDepth).

Return Result

Querying will work both if you are returning an IQueryable<T> collection or an IEnumerable<T>. The thing is, with the former, your query will be executed in the server (the database), whereas with the latter, it will be executed in memory (LINQ to Objects). You will still be able to query, but it won’t be the same!

Inspecting Query Options

In your action methods, whenever querying is allowed, we have a way to get the query options passed to the URL ($filter, $orderby, etc), and then choose whether or not we want to apply them. The key lies in the ODataQueryOptions<T> class:

[ODataRoute]
[EnableQuery]
public IQueryable<Parent>Get(ODataQueryOptions<Parent> options)
{
    if (options.Top != null && options.Top.Value == 0)
    {
        options.Top.Value = 10;
    }

    var parents = _ctx.Parents.AsQueryable();
    parents = options.ApplyTo(parents) as IQueryable<Parent>;
    return parents;
} 

Here you can inspect the current filter (Filter), sort order (OrderBy), expand (SelectExpand), skip (Skip), count (Count) and even make changes. When you’re happy with then, just ApplyTo a base queryable collection.

Conclusion

This covers the basic querying options of OData. In the next post, functions and actions!

References

Please refer to the following links for additional information:

• OData Documentation: https://www.odata.org/documentation/
• OData URL Conventions: https://docs.oasis-open.org/odata/odata/v4.01/odata-v4.01-part2-url-conventions.html