This is a series of blogs detailing my introduction to using OData v4 with C# and ASP.NET WebApi.
The source code for the project can be found here.
Up to now we have been creating individual controller actions for our entities either for selecting all entities or selecting entities by key. One of the powers of OData is the query like syntax we can apply within the Url which does not need additional controller actions. In this blog we will look at the SELECT query option.
Creating the Project
The Project is called Part14.
Creating a Database
Up to now we having been using data from in-memory collections. To get a better understanding of the query options and their performance impact, we need to demonstrate their behaviour with a database back end. I will be using SQL Server 2016 Express LocalDB . In order to use the project without any changes, I will show you the steps I took to create the LocalDB instance.
We can manage our database instance using C:\Program Files\Microsoft SQL Server\130\Tools\Binn\SqlLocalDB.exe.
From a command prompt run
- sqllocaldb create NorthWind
- sqllocaldb start NorthWind
- sqllocaldb info NorthWind
From Server Explorer in Visual Studio, under Data Connections, add a connection to (localdb)\NorthWind. Download the sample databases from here, install and you will see a file called instnwnd.sql. This will create a database called NorthWind.
Configuring Entity Framework 6
At the time of writing this blog the current version of the Nuget package EntityFramework was 6.2.0. To keep things simple, we are just going to use two tables from NorthWind. Employees and Orders.
Right click the Models folder and Add -> New Item. Select ADO.NET Entity Data Model. Call it Northwind and click add. Select Code First From Database and click Next. As long as you have created a connection in server explorer, the defaults on the “Choose your Data Connection” page should be fine. Click Next and select the Employees and Orders table and click finish. Your Models folder should now contain three files.
Creating the Entity Data Model
The Entity Framework wizard has created our two CLR classes with attributes. Rather than mix OData requirements and EF requirements together it makes better sense to keep them separated unless you are confident. In our WebApiConfig.cs file the following OData EDM has been created.
public static class WebApiConfig
{
public static void Register(HttpConfiguration config)
{
var model = GetEdmModel();
config.MapODataServiceRoute("OData", "OData", b => b
.AddService(ServiceLifetime.Singleton, s => model)
.AddService(ServiceLifetime.Singleton, s => ODataRoutingConventions.CreateDefaultWithAttributeRouting("OData", config))
.AddService(ServiceLifetime.Singleton, s => new AlternateKeysODataUriResolver(model))
);
}
private static IEdmModel GetEdmModel()
{
ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
builder.EntitySet("Employees");
builder.EntityType().Ignore(p => p.Photo);
builder.EntityType().Ignore(p => p.PhotoPath);
return builder.GetEdmModel();
}
}
I have added just the Employees entity set and excluded two properties to limit the output. Here I am using the new Model Bound Fluent API which came in version 6.x of Microsoft.AspNet.OData.
Create the Employees Controller
Let’s create a single action to return all the Employees. There should be 9 in the Northwind database.
public class EmployeesController : ODataController
{
private Northwind _repo;
public EmployeesController()
{
_repo = new Northwind();
}
public IQueryable Get()
{
return _repo.Employees;
}
protected override void Dispose(bool disposing)
{
if (disposing)
{
if (_repo != null)
{
_repo.Dispose();
_repo = null;
}
}
}
}
Query All Employees
From Postman issue a GET request with http://localhost:40000/OData/Employees.
So far so good. We have successfully retrieved the rows from our database but to be honest we have not really done anything new. Until now. Let’s look at using the SELECT query option.
Query Options
An OData Url consists of the following:
http://host:port/path to the service/resources?query options
So far up to now we have used a url up to but not including the ?.
Select Query Option
From Postman issue a GET request with http://localhost:40000/odata/Employees?$select=LastName,FirstName. Properties are case sensitive.
This is saying, return all employees but only the properties LastName and FirstName. Notice it did not work.
The reason it did not work was because we need to add the EnableQuery attribute to our controller action. This is a WebApi filter action that automatically alters the query expression tree with the additional query options before the query is executed.
[EnableQuery]
public IQueryable Get()
{
return _repo.Employees;
}
Rerun the postman query.
We have made some progress but we get an error. Basically all query options are off by default. You need to enable them in your EDM. Let’s say that all properties are selectable.
private static IEdmModel GetEdmModel()
{
ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
builder.EntitySet("Employees")
.EntityType
.Select();
builder.EntityType().Ignore(p => p.Photo);
builder.EntityType().Ignore(p => p.PhotoPath);
return builder.GetEdmModel();
}
Rerun the postman query.
This is more like it. Let’s just reflect on this simple yet powerful feature. We have just added the ability to give the client a choice on what properties to return without us having to alter our controller action. You might be wondering what query was executed in the database. Lets check.
Viewing the SQL Server Backend Query
Visual Studio has an IntelliTrace feature that enables you to capture ADO.NET events. It works ok but actually Entity Framework has a neat little option that will send the SQL queries to the Output window which you can view anytime.
public EmployeesController()
{
_repo = new Northwind();
_repo.Database.Log = sql => Debug.WriteLine(sql);
}
In our controller code where we instantiate our Northwind DBContext, there is a Log property that takes an action delegate with a single parameter that contains the SQL statement executed.
Rerun the postman query and then look in the visual studio Output window.
This tells us two things. It gives us the time the query took to execute which is really important for performance. A badly performing query may indicate that you are allowing the user too much free rain when querying the database. It may also indicate if indexes are required. The second thing it shows is the SQL executed. SQL generators are notorious for generating bad looking code and in extreme cases calling a stored procedure may be your only option if the code performs badly.
Going back to our query we asked for just the LastName and FirstName properties. We can see from the output the number of columns was restricted to what we asked. Yes it includes a few others that the framework requires but the important thing is that the server did not send every column and relied on the client to filter it. This would be bad.
Controlling what Properties are retrieved by default
Notice that when we retrieved the Employees without specifying the $select, all properties were returned. We may not want this. Perhaps some of the properties are only needed if the client really needs them or perhaps some of the properties should never be selected on their own. Let’s look at some of the options we have.
When we declared the Entity set above we used the fluent API to call the Select() method without any arguments. This says, all properties can be selected.
builder.EntitySet("Employees")
.EntityType
.Select()
The Select method is overloaded and allows us finer control. If we wanted to select what properties are shown when no select is specified, we add the following:
builder.EntitySet("Employees")
.EntityType
.Select()
.Select(SelectExpandType.Automatic, "TitleOfCourtesy", "EmployeeID", "FirstName", "LastName", "Title, "Notes")
From Postman issue a GET request with http://localhost:40000/OData/Employees.
These are our default select properties. We can also allow a property to be returned by default but not allowed in a select.
builder.EntitySet("Employees")
.EntityType
.Select()
.Select(SelectExpandType.Disabled, "Notes");
Notice the Notes property is marked disabled for individual selection. I noticed in testing that we cannot use the automatic and disabled options together otherwise the property in the disabled list can never be retrieved either way.
In the next blog I’ll take a look at the $filter query option.
Denham Coder 