Using OData to fast track your API development

I have been having a lot of interest in OData for a few years, but never had the opportunity to actually start implementing it. Sometimes you need an actual business case to start your work. Then you get to find out how things are done deeply. Since I can’t see an interesting project on the horizon, I thought I will start playing around with one of the sample databases, Adventure Work.

I created a blog post, Creating a Model for an Existing Adventure Works Database in Entity Framework Core. you can use this to get started with most of the EF classes.

Once you have the database restored and entity classes are created. You can start a new web API project, copy all entity classes to your project.

Install the NuGet https://www.nuget.org/packages/Microsoft.AspNetCore.OData/

Let’s start with a single entity, Customer object.

public class Customer
{
   public Customer()
   {
      CustomerAddresses = new HashSet<CustomerAddress>();
      SalesOrderHeaders = new HashSet<SalesOrderHeader>();
   }

   [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
   public int CustomerId { get; set; }
   public bool NameStyle { get; set; }
   public string Title { get; set; }
   public string FirstName { get; set; }
   public string MiddleName { get; set; }
   public string LastName { get; set; }
   public string Suffix { get; set; }
   public string CompanyName { get; set; }
   public string SalesPerson { get; set; }
   public string EmailAddress { get; set; }
   public string Phone { get; set; }
   public Guid RowGuid { get; set; }
   public DateTime ModifiedDate { get; set; }
   public virtual ICollection<CustomerAddress> CustomerAddresses { get; set; }
   public virtual ICollection<SalesOrderHeader> SalesOrderHeaders { get; set; }
}

Now you can create your DbContext,

public class AdventureWorksContext : DbContext
{
   public AdventureWorksContext(DbContextOptions options) : base(options) { }

   public virtual DbSet<Customer> Customers { get; set; }

  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
  {
    // below line to watch the ef core sql quiries generation, not recomonded for the production code
    optionsBuilder.LogTo(Console.WriteLine);
  }

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
    
  }
}

I use the fluent API to configure the database entities, but how every it works. If you know EF, this is pretty simple stuff

Now on your Startup.cs looks like this

public void ConfigureServices(IServiceCollection services)
{
  services.AddDbContext<AdventureWorksContext>(options => options.UseSqlServer(_config.GetConnectionString("DefaultConnection")));
  ....
  services.AddControllers()
  .AddOData(option => option.Select()
  .Filter()
  .Count()
  .OrderBy()
  .Expand()
  .SetMaxTop(30)
  .AddRouteComponents("api", GetEdmModel()));
}

Create a static method for the EDM Model, let’s keep it simple for this example. You can be fancy in the future with refactoring.

public static IEdmModel GetEdmModel()
{
  ODataConventionModelBuilder modelBuilder = new ODataConventionModelBuilder();
  modelBuilder.EntitySet<Customer>("Customers");
  ...
  modelBuilder.EnableLowerCamelCase();
  return modelBuilder.GetEdmModel();
}

Now create your controller

[Route("api/{Controller}")]
public class CustomersController : ControllerBase
{
  private readonly AdventureWorksContext _dbContext;
  public CustomersController(AdventureWorksContext dbContext)
  {
    _dbContext = dbContext;
  }

  [EnableQuery]
  public IActionResult Get()
  {
    return Ok(_dbContext.Customers.AsQueryable());
  }

  [EnableQuery]
  public IActionResult Get(int key, string version)
  {
    return Ok(_dbContext.Customers.Find(key));
  }
}

we have 2 endpoints

1. get a list of customers (with pagination)
2. get single customer with the primary key

your OData queries can be like this

1. /api/customers?$select=customerId,rowGuid&$count=true&$top=10&$skip=0
2. /api/customers(12)?$select=customerId,RowGuid

if you want to expand navigation properties and select you can do something like this

$expand=customerAddresses($select=CustomerId,AddressType)

You can read more info https://docs.microsoft.com/en-us/odata/webapi/netcore

Enjoy