OData is an open protocol for enabling interaction with a datasource in a standardised way via a RESTful API. Theoretically using OData in combination with Entity Framweork should provide a simple way of providing access to SQL (or other) data via API, this is indeed the case but setting things up is a bit more fiddly than expected.

The example below is based around using the demo AdventureWorks SQL database as a source for entity framework, this was installed on an Azure SQL server like so. The complete example project can be found here.

Once the database has been created add a new user to it with the db_owner role.

CREATE USER [adventureworks_user]


GRANT CONNECT TO [adventureworks_user]

EXEC sp_addrolemember 'db_owner', 'adventureworks_user';  

Create a new ASP .NET Core Web Application using .NET Core 3.1 and then add the following packages.

  • Microsoft.AspNetCore.OData
  • Microsoft.EntityFrameworkCore
  • Microsoft.EntityFrameworkCore.SqlServer
  • Microsoft.EntityFrameworkCore.Tools

The version of Microsoft.AspNetCore.OData I used is 7.4.0-beta as this has been updated to work with Endpoint Routing, a release version of this package will hopefully be published soon.

To create the C# models for the AdventureWorks SQL tables you need to run the following commands in the PM console window using the credentials created above as specified in my previous post.

Scaffold-DbContext "Server=tcp:SERVER.database.windows.net,1433;Initial Catalog=DATABASE;Persist Security Info=False;User ID=USERNAME;Password=PASSWORD;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models


The ConfigureServices method needs to be updated to dependency inject the newly scaffolded DBContext and OData services.

public void ConfigureServices(IServiceCollection services)
	services.AddDbContext<Models.AdventureWorksContext>(options =>


A new GetEdmModel method needs to be created to register to entities to be accessed by OData.

private IEdmModel GetEdmModel()
	var builder = new ODataConventionModelBuilder();
		.HasKey(table => new { table.CustomerId, table.AddressId });
		.HasKey(table => new { table.ProductModelId, table.ProductDescriptionId });
		.HasKey(table => new { table.SalesOrderId });
	return builder.GetEdmModel();

The alowed OData operations then need to be enabled in the endpoint routing and OData is then mapped to a route (“api” in my case).

public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
	if (env.IsDevelopment())
		// The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.



	app.UseEndpoints(endpoints =>
			name: "default",
			pattern: "{controller=Home}/{action=Index}/{id?}");

		endpoints.MapODataRoute("api", "api", GetEdmModel());


The ApiController class inherits from the ODataController class rather than the standard Controller class and the Entity Framework DBContext is injected at creation.

public class ApiController : ODataController
	private readonly ILogger<ApiController> _logger;
	private readonly Models.AdventureWorksContext _context;

	public ApiController(ILogger<ApiController> logger, Models.AdventureWorksContext context)
		_logger = logger;
		_context = context;


Methods are then added to query the various AdventureWorks entities, both by ID which returns a single concrete response and by query which returns an IQueryable result.

// GET: api/addresses
[EnableQuery(PageSize = 50)]
public IQueryable<Address> GetAddresses()
	return _context.Addresses;

// GET: api/addresses(451)
public IActionResult GetAddresses([FromODataUri] int id)
	if (!ModelState.IsValid)
		return BadRequest(ModelState);

	var address = SingleResult.Create(_context.Addresses.Where(p => p.AddressId == id));

	return Ok(address);

// GET: api/customers
[EnableQuery(PageSize = 50)]
public IQueryable<Customer> GetCustomers()
	return _context.Customers;

// GET: api/customers(5)
public IActionResult GetCustomers([FromODataUri] int id)
	if (!ModelState.IsValid)
		return BadRequest(ModelState);

	var customer = SingleResult.Create(_context.Customers.Where(p => p.CustomerId == id));

	return Ok(customer);

When running the project in debug the SQL tables can be queried using the OData API like so.

	"@odata.context": "https://localhost:44366/api/$metadata#Customers",
	"@odata.count": 847,
	"value": [
			"CustomerId": 1,
			"NameStyle": false,
			"Title": "Mr.",
			"FirstName": "Orlando",
			"MiddleName": "N.",
			"LastName": "Gee",
			"Suffix": null,
			"CompanyName": "A Bike Store",
			"SalesPerson": "adventure-works\\pamela0",
			"EmailAddress": "orlando0@adventure-works.com",
			"Phone": "245-555-0173",
			"PasswordHash": "L/Rlwxzp4w7RWmEgXX+/A7cXaePEPcp+KwQhl2fJL7w=",
			"PasswordSalt": "1KjXYs4=",
			"Rowguid": "3f5ae95e-b87d-4aed-95b4-c3797afcb74f",
			"ModifiedDate": "2005-08-01T00:00:00+01:00"
			"CustomerId": 2,
			"NameStyle": false,
			"Title": "Mr.",
			"FirstName": "Keith",
			"MiddleName": null,
			"LastName": "Harris",
			"Suffix": null,
			"CompanyName": "Progressive Sports",
			"SalesPerson": "adventure-works\\david8",
			"EmailAddress": "keith0@adventure-works.com",
			"Phone": "170-555-0127",
			"PasswordHash": "YPdtRdvqeAhj6wyxEsFdshBDNXxkCXn+CRgbvJItknw=",
			"PasswordSalt": "fs1ZGhY=",
			"Rowguid": "e552f657-a9af-4a7d-a645-c429d6e02491",
			"ModifiedDate": "2006-08-01T00:00:00+01:00"

One thing to note is that there’s currently a bug with expanding ICollection properties of entities, so the below URL which should return the Customer entity as above but also with all CustomerAddress objects actually throws an EF.Property called with wrong property name error.


This bug seems to have been fixed in the nightly builds of the package but has not yet been pushed to NuGet.


Di · 28th May 2020 at 2:23 pm

Hi! Could you tell me please when do I need to indicate that a key consists of several properties
(like this line: “.HasKey(table => new { table.CustomerId, table.AddressId });”)?

    Shinigami · 28th May 2020 at 3:33 pm

    Hi, if you’ve got a composite key in your SQL table then you’d need to reflect this in the DbContext.

    CREATE TABLE [dbo].[Category]
    [SkuId] VARCHAR(100) NOT NULL CONSTRAINT fk__Category__Product REFERENCES Product(SkuId),
    [Category] VARCHAR(100) NOT NULL,
    CONSTRAINT pk__Category PRIMARY KEY ([SkuId], [Category])

    The above has a composite key on SkuId and Category so this would also need to be reflected in the DbContext with something like.

    .HasKey(table => new { table.SkuId, table.Category});

Dieter Wiesflecker · 6th August 2020 at 11:16 am

Thanks very much, this is working and saved me a lot of time!

Gilberto Alexandre · 1st September 2020 at 12:11 am

Finally a functional example. Thank you very much

Rich Gunther · 4th May 2021 at 8:05 pm

What if we have a property on Customer that is a collection of enums? How do we add that to the Edm and enable filtering by it? Example: I have a property called “CustomerLoyaltyLevels”, of which a customer can have more than one. I define it as:

public ICollection CustomerLoyaltyLevels { get; set; )

I believe that EFCore would, by default, persist this as a string value in the Customers table, but how them to expose it to OData so that we can do a filter on it, e.g.:

Customers?$filter=CustomerLoyaltyLevels/any(enum: enum eq enums.CustomerLoyaltyLevels’Gold’)

Thanks in advance!

    Shinigami · 6th May 2021 at 4:17 pm

    Sorry, I’m not sure how best to deal with this scenario. The end result you’d want would be a SQL table called CustomerLoyaltyLevels that matches between your Customer table and LoyaltyLevel lookup, I think it should then be possible to expand and filter on this relationship from OData however I’m not certain of the precise descriptions you’d need of these objects in your EF context to be able to do this.

Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *