Using OData with Entity Framework

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]
	WITH PASSWORD = 'PASSWORD'

GO

GRANT CONNECT TO [adventureworks_user]
GO

EXEC sp_addrolemember 'db_owner', 'adventureworks_user';  
GO

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

Startup.cs

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

public void ConfigureServices(IServiceCollection services)
{
	services.AddControllersWithViews();
	services.AddDbContext<Models.AdventureWorksContext>(options =>
		options.UseSqlServer(Configuration.GetConnectionString("DataConnection"))
		);

	services.AddOData();
}

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

private IEdmModel GetEdmModel()
{
	var builder = new ODataConventionModelBuilder();
	builder.EntitySet<Address>("Addresses");
	builder.EntitySet<Customer>("Customers");
	builder.EntitySet<CustomerAddress>("CustomerAddresses")
		.EntityType
		.HasKey(table => new { table.CustomerId, table.AddressId });
	builder.EntitySet<Product>("Products");
	builder.EntitySet<ProductCategory>("ProductCategories");
	builder.EntitySet<ProductDescription>("ProductDescriptions");
	builder.EntitySet<ProductModel>("ProductModels");
	builder.EntitySet<ProductModelProductDescription>("ProductModelProductDescriptions")
		.EntityType
		.HasKey(table => new { table.ProductModelId, table.ProductDescriptionId });
	builder.EntitySet<SalesOrderDetail>("SalesOrderDetails");
	builder.EntitySet<SalesOrderHeader>("SalesOrderHeaders")
		.EntityType
		.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())
	{
		app.UseDeveloperExceptionPage();
	}
	else
	{
		app.UseExceptionHandler("/Home/Error");
		// The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
		app.UseHsts();
	}
	app.UseHttpsRedirection();
	app.UseStaticFiles();

	app.UseRouting();

	app.UseAuthorization();

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

		endpoints.MapControllers();
		endpoints.Select().Filter().OrderBy().Count().Expand().MaxTop(100);
		endpoints.MapODataRoute("api", "api", GetEdmModel());
	});
}

ApiController.cs

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
[HttpGet]
[EnableQuery(PageSize = 50)]
[ODataRoute("addresses")]
public IQueryable<Address> GetAddresses()
{
	return _context.Addresses;
}

// GET: api/addresses(451)
[HttpGet]
[EnableQuery]
[ODataRoute("addresses({id})")]
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
[HttpGet]
[EnableQuery(PageSize = 50)]
[ODataRoute("customers")]
public IQueryable<Customer> GetCustomers()
{
	return _context.Customers;
}

// GET: api/customers(5)
[HttpGet]
[EnableQuery]
[ODataRoute("customers({id})")]
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.

https://localhost:44366/api/customers?$top=2&$count=true

{
	"@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": "[email protected]",
			"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": "[email protected]",
			"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.

https://localhost:44366/api/customers?$top=2&$count=true&$expand=CustomerAddresses

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