Enable OData on you WebAPI project

1. Create new project in Visual Studio 2022 and select ASP.NET Core Web API as project template

Provide a project name and select .NET 6.0 as the framework. You should get a sample Web API with one WeatherForecast controller, that has one GET action returning a collection of random generated objects.

2. Install a nuget package called Microsoft.AspNetCore.OData.

3. Modify Program.cs as follows

[…]
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddControllers()
     .AddOData(o =>
     {
         o.Select().Filter().OrderBy();
     });
[…]

That enables OData on your project and allows to use selection, filtering and ordering functions.

4. Next go to WeahterForecast controller and decorate the only get action with [EnableQuery] attribute

[EnableQuery]
[HttpGet(Name = "GetWeatherForecast")]
public IEnumerable<WeatherForecast> Get()
{
    return Enumerable.Range(1, 5).Select(index => new WeatherForecast
    {
        Date = DateTime.Now.AddDays(index),
        TemperatureC = Random.Shared.Next(-20, 55),
        Summary = Summaries[Random.Shared.Next(Summaries.Length)]
    })
    .ToArray();
}

Now run the project and query to test it (using your favourite tool)…

As you can see we can already  query data using all enabled OData features.

Let’s now see how will it work with Entity Framework Core.

5. Install two additional nuget packages: Microsoft.EntityFrameworkCore and Microsoft.EntityFrameworkCore.Sqlite

6. Add new class to the project WeatherForecastContext

public class WeatherForecastContext : DbContext
{
    public DbSet<WeatherForecast>? WeatherForecasts { get; set; }

    private static readonly string[] Summaries = new[]
   {"Freezing", "Bracing", "Chilly", "Cool", "Mild", "Warm", "Balmy", "Hot", "Sweltering", "Scorching" };

    public WeatherForecastContext(DbContextOptions options) : base(options)
    {  }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
      modelBuilder.Entity<WeatherForecast>(b =>
      {
         b.HasData(GenerateRandomForeacasts());
      });
   }

   private static WeatherForecast[] GenerateRandomForeacasts()
  {
      return Enumerable.Range(1, 15).Select(index => new WeatherForecast
      {
        Id = index,
        Date = DateTime.Now.AddDays(index),
        TemperatureC = Random.Shared.Next(-20, 55),
        Summary = Summaries[Random.Shared.Next(Summaries.Length)]
      })
      .ToArray();
   }
}

Here we generate 15 random entities and seed them to the SQLite database. Note that WeatherForecast entity was extended with a integer Id property.

7. Go to WeatherForecast controller and modify it as follows:

[ApiController]
[Route("[controller]")]
public class WeatherForecastController : ControllerBase
{
   private readonly ILogger<WeatherForecastController> _logger;
   private readonly WeatherForecastContext _context;

   public WeatherForecastController(WeatherForecastContext context, ILogger<WeatherForecastController> logger)
   {
       _context = context;
       _logger = logger;
       _context.Database.EnsureCreated();
   }

   [EnableQuery]
   HttpGet(Name = "GetWeatherForecast")]
   public IQueryable<WeatherForecast> Get() => _context.WeatherForecasts!;
}

Note that we simply return a Queryable dataset right from the EntityFramework Core context.

8. Now wee need to register our new EF Core database context with application DI services. Got to program.cs and add following lines at the end of application builder instructions:

builder.Services.AddDbContext<WeatherForecastContext>(o =>
{
     o.UseSqlite("Data Source=foreacasts.db");
     o.LogTo((m) => Debug.WriteLine(m)); 
});

The logging line will become clear in a minute. 
9. Run the project.

10. Query it with OData filtering

Let’s now check how the query is actually constructed. While running the application, query it with the client applying filtering and ordering. Then open the Output window in Visual Studio. Here are the log messages written by EF, as we instructed it to before.

info: 04.07.2022 17:48:13.773 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (1ms) [Parameters=[@__TypedProperty_0='?' (DbType = Int32), @__TypedProperty_1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SELECT "w"."TemperatureC", "w"."Summary", "w"."Id"
      FROM "WeatherForecasts" AS "w"
      WHERE ("w"."TemperatureC" > @__TypedProperty_0) AND ("w"."TemperatureC" < @__TypedProperty_1)
      ORDER BY "w"."TemperatureC"

What’s nice the OData queries are automatically translated into Linq2SQL, so only required data is loaded from the database.

Full solution is available for download with this post.

Cheers.