Styjun

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.

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)