Working with OData EDM Model

The OData uses Entity Data Model (EDM) to define and describe the data shape that is exposed by the OData endpoints. Assuming your application uses some sort of domain model, which represents your entities in database, you want to somehow control over what data exactly is exposed to the OData clients. For instance you dont want necessarily expose some sensitive data of person entity you store in database, but still want to expose person’s name and and its order history for example.

Ok let’s assume we have a Customer and Order entity, that are backed by EF Core 6 and stored in SQLite database:

public class Customer
{
    public Guid Id { get; set; }
    public string? Name { get; set; }
    public string? Phone { get; set; }
    public ICollection? Orders { get; set; }
}

public class Order
{ 
    public Guid Id { get; set; }
    public Guid CustomerId { get; set; }
    public DateTime Date { get; set; }
    public string? Product { get; set; }
    public int Quantity { get; set; }
    public double Total { get; set; }
}

Database context is straight foreward, nothing fancy. We just seed the database with some sample data. I’ll skip that part here for brevity and ancourage you to download full source code from the link at the bottom of the post.

Next step is to instruct OData about our model. So go to Program.cs and modify OData configuration as follows

[…]
builder.Services.AddControllers()
    .AddOData(o =>
    {
        o.EnableQueryFeatures()
        .AddRouteComponents("api", CreateODataModel());
    });
[…]

EnableQueryFeatures makes all OData commands available like $filter, $select, $expand and so on.
Then second line says the OData is exposed with api prefix and uses the provided EDM model. The model itself is created in separate method:

static IEdmModel CreateODataModel()
{
    var builder = new ODataConventionModelBuilder();
    builder.EntitySet<Customer>("Customer");
    builder.EntitySet<Order>("Order");
    return builder.GetEdmModel();
}

We use here a OData Convention builder, which is one of three (and recommended btw.) available methods to construct the EDM model. Anyway the convention builder is super convinient, because it allows to use model attributing like DataContract attributes, which we will see in action in the moment.
In above model we instruct the OData that we want to use our domain entities Customer and Order as EDM model entities. Whats important here, the names we pass in to the EntitySet methods must match the api controller’s route name. So in this example we link the CustomerController with Customer OData endpoint and Customer entity.

[ApiController]
[Route("[controller]")]
public class CustomerController : ControllerBase
{
     private readonly ILogger<CustomerController> _logger;
     private readonly AppDbContext _context;
     public CustomerController(AppDbContext context, ILogger<CustomerController> logger)
     {
         _context = context;
         _logger = logger;
         _context.Database.EnsureCreated();
     }

     [EnableQuery]
     [HttpGet]
     public ActionResult<IQueryable<Customer>> Get() => Ok(_context.Customers!);
}

Ok, lets start the application.

Although we only have one api controller with one action, we get quite a number of endpoints. These endpoints are generated by OData and gives us full possibilities in context of client’s calls and model proxy creation. Let’s see the /api/$metadata endpoint

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
 <edmx:DataServices>
 <Schema Namespace="WebAPI2" xmlns="http://docs.oasis-open.org/odata/ns/edm">
 <EntityType Name="Customer">
    <Key>
       <PropertyRef Name="Id" />
    </Key>
    <Property Name="Id" Type="Edm.Guid" Nullable="false" />
    <Property Name="Name" Type="Edm.String" />
    <Property Name="Phone" Type="Edm.String" />
    <NavigationProperty Name="Orders" Type="Collection(WebAPI2.Order)" />
 </EntityType>
 <EntityType Name="Order">
    <Key>
      <PropertyRef Name="Id" />
    </Key>
    <Property Name="Id" Type="Edm.Guid" Nullable="false" />
    <Property Name="CustomerId" Type="Edm.Guid" Nullable="false" />
    <Property Name="Date" Type="Edm.DateTimeOffset" Nullable="false" />
    <Property Name="Product" Type="Edm.String" />
    <Property Name="Quantity" Type="Edm.Int32" Nullable="false" />
    <Property Name="Total" Type="Edm.Double" Nullable="false" />
 </EntityType>
 </Schema>
 <Schema Namespace="Default" xmlns="http://docs.oasis-open.org/odata/ns/edm">
 <EntityContainer Name="Container">
 <EntitySet Name="Customer" EntityType="WebAPI2.Customer">
    <NavigationPropertyBinding Path="Orders" Target="Order" />
 </EntitySet>
 <EntitySet Name="Order" EntityType="WebAPI2.Order" />
 </EntityContainer>
 </Schema>
 </edmx:DataServices>
</edmx:Edmx>

Great. Now lets query the Customer endpoint. We want to include customer’s orders and filter results by Order date.

https://localhost:7047/api/Customer?$expand=Orders($filter=Date lt 2022-05-30T00:00:00Z)

The results is:

{  
  "@odata.context": "https://localhost:7047/api/$metadata#Customer(Orders())",  
  "value": [  
    {  
      "Id": "74fd3f0b-97a7-4086-b72f-d731e1a1e756",  
      "Name": "Josh",  
      "Phone": "12345678",  
      "Orders": [  
        {  
          "Id": "05363830-ba57-426d-8b66-a851749d52c7",  
          "CustomerId": "74fd3f0b-97a7-4086-b72f-d731e1a1e756",  
          "Date": "2022-05-03T00:00:00+02:00",  
          "Product": "Desk",  
          "Quantity": 3,  
          "Total": 43.22  
        },  
        {  
          "Id": "75a17914-834a-4365-b9eb-0f0a6ec46e7e",  
          "CustomerId": "74fd3f0b-97a7-4086-b72f-d731e1a1e756",  
          "Date": "2022-05-23T00:00:00+02:00",  
          "Product": "Chair",  
          "Quantity": 1,  
          "Total": 3.22  
        },  
        {  
          "Id": "a2911394-f062-480f-92b2-fdc825ba0f3d",  
          "CustomerId": "74fd3f0b-97a7-4086-b72f-d731e1a1e756",  
          "Date": "2022-05-13T00:00:00+02:00",  
          "Product": "Desk",  
          "Quantity": 2,  
          "Total": 4.0  
        }  
      ]  
    },  
    {  
      "Id": "fce65cb5-bd6e-412a-876f-4185f0314896",  
      "Name": "Anna",  
      "Phone": "01243011",  
      "Orders": []  
    }  
  ]  
 }

How is that translated into SQL?

SELECT "c"."Id", "c"."Name", "c"."Phone", "t"."Id", "t"."CustomerId", "t"."Date", "t"."Product", "t"."Quantity", "t"."Total"
FROM "Customers" AS "c"
LEFT JOIN (
     SELECT "o"."Id", "o"."CustomerId", "o"."Date", "o"."Product", "o"."Quantity", "o"."Total"
     FROM "Orders" AS "o"
     WHERE "o"."Date" < @__TypedProperty_1
     ) AS "t" ON "c"."Id" = "t"."CustomerId"
ORDER BY "c"."Id"

Great, the filtering already happens on database. But lets say we don’t want to expose customer’s phone number nor order’s total price. With use of convention model builder its as easy as properly decorating model property.  To ignore a property use IgnoreDataMember attribute from System.Runtime.Serialization namespace like so

public class Customer
{
     public Guid Id { get; set; }
     public string? Name { get; set; }
     [IgnoreDataMember]
     public string? Phone { get; set; }
     public ICollection<Order>? Orders { get; set; }
}
 
public class Order
{
     public Guid Id { get; set; }
     public Guid CustomerId { get; set; }
     public DateTime Date { get; set; }
     public string? Product { get; set; }
     public int Quantity { get; set; }
     [IgnoreDataMember]
     public double Total { get; set; }
}

And thats it. The sensitive data is no longer exposed to the OData endpoint.

Cheers!

Reference links:

• ASP.NET Core OData 8.x
• Security guidance for ASP.NET Core Web API OData