How to apply ODataQueryOptions for DTO to underlying EntitySet?

Question

There are 2 classes, Foo and Bar. There is a Bar object nested in a Foo object.

public class Foo {
    public Guid FooId { get; set; }
    public string FooName { get; set; }
    [ForeignKey("Bar")]
    public Guid BarId { get; set; }

    public virtual Bar Bar { get; set; }
}

public class Bar {
    public Guid BarId { get; set; }
    public string BarName { get; set; }
}

public class FooBarContext : DbContext {
    public DbSet<Foo> Foos { get; set; }
    public DbSet<Bar> Bars { get; set; }
}

public class FooDTO {
    public Guid FooId { get; set; }
    public string FooName { get; set; }
    public Guid BarId { get; set; }
    public string BarName { get; set; }
}

My question is: can I somehow translate the OData query for FooDTO to OData query for Foo, such that it can be applied to the Foos DbSet?

For example, I'd like to query by BarName, which is ultimately from the nested Bar object.

GET /Foos?$filter=BarName eq 'Bar2'

And here is the controller and action to process the query

public class FoosController {
    public async Task<IHttpActionResult> GetFoos(ODataQueryOptions<FooDTO> queryOptions) {
        // translate filter FooDTO.BarName to filter Foo.Bar.Name
        // ODataQueryOptions<Foo> fooQueryOptions = ....
        using (var context = new FooBarContext()) {
            return fooQueryOptions.ApplyTo(context.Foos);
        }
    }
}

Thank you.

Answer 1

First Install the OData packages to your Web API project

Install-Package Microsoft.AspNet.OData -Version 7.1.0

Configure the OData endpoint in WebApiConfig.cs by Add the following using statements

using System.Web.OData.Builder;
using System.Web.OData.Extensions;

And following code to the Register method

public static void Register(HttpConfiguration config)
{
    config.MapHttpAttributeRoutes();

    // New code start
    ODataModelBuilder builder = new ODataConventionModelBuilder();
    builder.EntitySet<Foo>("Foos");
    builder.EntitySet<Bar>("Bars");
    config.MapODataServiceRoute(
        routeName: "ODataRoute",
        routePrefix: null,
        model: builder.GetEdmModel());

    config.Count().Filter().OrderBy().Expand().Select().MaxTop(null);

    config.EnableDependencyInjection();
    // New code end

    config.Routes.MapHttpRoute(
        name: "DefaultApi",
        routeTemplate: "api/{controller}/{id}",
        defaults: new { id = RouteParameter.Optional }
    );
}

Updated for mapping: In your controller

[EnableQuery()] // Enables clients to modify the query, by using query options such as $filter, $sort, and $page
public async Task<IHttpActionResult> GetFoos(ODataQueryOptions<FooDTO> queryOptions)
{
    using (var context = new FooBarContext())
    {
        return queryOptions.ApplyTo(context.Foos.AsQueryable().Select(f => new FooDTO
        {
            BarId = f.BarId,
            BarName = f.Bar.BarName,
            FooId = f.FooId,
            FooName = f.FooName
        }));
    }
}

For more check Create an OData v4 Endpoint Using ASP.NET Web API,

Also Supercharging your Web APIs with OData and ASP.NET Core (For .Net core but it could help)