OdataToEntity – an easy way to create .Net Core OData services

When .Net Core was released, the old version of OData ASP.NET Web API turned out to be incompatible with the new platform. This fatal flaw allowed me to create my OData implementation on the .Net Core platform. After the creative rethinking of the previous implementation, I came to an understanding that it suffered from a complicated design with a lot of unnecessary abstractions. An idea to create an easy-to-use library that requires minimal coding came into my mind. I would like to present you OdataToEntity, the library for creating OData services without code writing; the only thing needed is data access context.

As a consequence, to simplify the design of the API, it was decided not to use interfaces in the code, while the library has a full test coverage. To reduce external dependencies, the library is unbound from HTTP, which allows implementing OData on top of any transport. This engineering marvel is assembled for Framework 4.8 or .Net Core 3.1 and uses Microsoft.OData.Core 7.6.1. The following data contexts are supported:

1. Entity Framework 6.4
2. Entity Framework Core 3.1
3. Linq2Db

How it works

The main idea of the project is the translation of OData queries into an expression tree, which is then transferred to the appropriate data access adapter.

To isolate the library from various ORM APIs, the OdataToEntity.Db.OeDataAdapter “data access adapter” abstract class is used. Each context implements its successor from this class (Ef6: OeEf6DataAdapter, EfCore: OeEfCoreDataAdapter, Linq2Db: OeLinq2DbDataAdapter).

Based on the data model, the OData Entity Data Model (EDM) is constructed to describe the data provided by your service. The EDM model is required for the ODataLib library to parse the query string. If the user entities are marked with attributes (System.ComponentModel.DataAnnotations), then the model can be built using a standard method suitable for all data providers.

//Create adapter data access, where OrderContext your DbContext
var dataAdapter = new OeEfCoreDataAdapter<Model.OrderContext>();
//Build OData Edm Model
EdmModel edmModel = dataAdapter.BuildEdmModel();

If the Entity Framework context is used and the “Fluent API” (without using attributes) is used to describe the entities:

Entity Framework Core

//Create adapter data access, where OrderContext your DbContext
var dataAdapter = new OeEfCoreDataAdapter<Model.OrderContext>();
//Build OData Edm Model
EdmModel edmModel = dataAdapter.BuildEdmModelFromEfCoreModel();

Entity Framework 6

//Create adapter data access, where OrderEf6Context your DbContext
var dataAdapter = new OeEf6DataAdapter<OrderEf6Context>();
//Build OData Edm Model
EdmModel edmModel = dataAdapter.BuildEdmModelFromEf6Model();

Build from multiple data contexts

//Create referenced data adapter
var refDataAdapter = new OeEfCoreDataAdapter();
//Build referenced Edm Model
EdmModel refModel = refDataAdapter.BuildEdmModel();

//Create root data adapter
var rootDataAdapter = new OeEfCoreDataAdapter();
//Build root Edm Model
EdmModel rootModel = rootDataAdapter.BuildEdmModel(refModel);

The library can be used for reading and editing data.

In the read mode, an OData query is sent to the library input, it is parsed using the Microsoft.OData.Core (ODataLib) into the ODataLib representation, which is translated into the standard expression tree. The query is parameterized (i.e. constant expressions are replaced with variables) and then passed to the data access adapter. The adapter translates the standard expression tree into more specific one that is applicable in this data context. This creates a context that executes the query against the database, and the resulting entities are serialized in the OData JSON format.

In the editing mode, the model entities serialized in the OData JSON format are sent to the library input. With the help of ODataLib, data in the model entities are deserialized, added to the data access context, and stored in the database. Fields computed on the database side are returned to the client. “Batch change set” – batch addition, deletion, change of entities is supported. Editing of tables that describe the tree-like data structures (self-referencing table) is supported as well. A data context similar to DbContext Entity Framework allowing you to edit the object graph was implemented for Linq2Db.

Supported Query Types

1. $apply (filter, groupby, aggregate (average, count,  virtual property $count, countdistinct, max, min, sum))
2. $count
3. $filter
4. $orderby
5. $select
6. $skip
7. $top
8. $compute
9. $skiptoken
10. lambda any
11. lambda all

Supported functions

1. cast
2. ceiling
3. concat
4. contains
5. day
6. endswith
7. floor
8. fractionalseconds
9. hour
10. in
11. indexof
12. length
13. minute
14. month
15. round
16. second
17. startswith
18. substring
19. tolower
20. toupper
21. trim
22. year

Example of use

The following data model is used in the tests and examples

public sealed class Category
{
    public ICollection<Category> Children { get; set; }
    public int Id { get; set; }
    [Required]
    public String Name { get; set; }
    public Category Parent { get; set; }
    public int? ParentId { get; set; }
}

public sealed class Customer
{
    public String Address { get; set; }
    [InverseProperty(nameof(Order.AltCustomer))]
    public ICollection<Order> AltOrders { get; set; }
    [Key, Column(Order = 0), Required]
    public String Country { get; set; }
    [Key, Column(Order = 1)]
    public int Id { get; set; }
    [Required]
    public String Name { get; set; }
    [InverseProperty(nameof(Order.Customer))]
    public ICollection<Order> Orders { get; set; }
    public Sex? Sex { get; set; }
}

public sealed class Order
{
    [ForeignKey("AltCustomerCountry,AltCustomerId")]
    public Customer AltCustomer { get; set; }
    public String AltCustomerCountry { get; set; }
    public int? AltCustomerId { get; set; }

    [ForeignKey("CustomerCountry,CustomerId")]
    public Customer Customer { get; set; }
    public String CustomerCountry { get; set; }
    public int CustomerId { get; set; }

    public DateTimeOffset? Date { get; set; }
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public ICollection<OrderItem> Items { get; set; }
    [Required]
    public String Name { get; set; }
    public OrderStatus Status { get; set; }
}

public sealed class OrderItem
{
    public int? Count { get; set; }
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public Order Order { get; set; }
    public int OrderId { get; set; }
    public Decimal? Price { get; set; }
    [Required]
    public String Product { get; set; }
}

public enum OrderStatus
{
    Unknown,
    Processing,
    Shipped,
    Delivering,
    Cancelled
}

public enum Sex
{
    Male,
    Female
}

public sealed class OrderContext : DbContext
{
    public DbSet<Category> Categories { get; set; }
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Order> Orders { get; set; }
    public DbSet<OrderItem> OrderItems { get; set; }

    [Description("dbo.GetOrders")]
    public IEnumerable<Order> GetOrders(int? id, String name, OrderStatus? status) => throw new NotImplementedException();
    public void ResetDb() => throw new NotImplementedException();
}

The sample OData query consists of only five lines:

//Create adapter data access, where OrderContext your DbContext
var dataAdapter = new OeEfCoreDataAdapter<Model.OrderContext>();
//Create query parser
var parser = new OeParser(new Uri("http://dummy"), dataAdapter, dataAdapter.BuildEdmModel());
//Query
var uri = new Uri("http://dummy/Orders?$select=Name");
//The result of the query
var response = new MemoryStream();
//Execute query
await parser.ExecuteGetAsync(uri, OeRequestHeaders.JsonDefault, response, CancellationToken.None);

An example of storing new entities in the database also consists of five lines:

string batch = @"
--batch_6263d2a1-1ddc-4b02-a1c1-7031cfa93691
Content-Type: multipart/mixed; boundary=changeset_e9a0e344-4133-4677-9be8-1d0006e40bb6

--changeset_e9a0e344-4133-4677-9be8-1d0006e40bb6
Content-Type: application/http
Content-Transfer-Encoding: binary
Content-ID: 1

POST http://dummy/Customers HTTP/1.1
OData-Version: 4.0
OData-MaxVersion: 4.0
Content-Type: application/json;odata.metadata=minimal
Accept: application/json;odata.metadata=minimal
Accept-Charset: UTF-8
User-Agent: Microsoft ADO.NET Data Services

{""@odata.type"":""#OdataToEntity.Test.Model.Customer"",""Address"":""Moscow"",""Id"":1,""Name"":""Ivan"",""[email protected]"":""#OdataToEntity.Test.Model.Sex"",""Sex"":""Male""}

--changeset_e9a0e344-4133-4677-9be8-1d0006e40bb6--
--batch_6263d2a1-1ddc-4b02-a1c1-7031cfa93691--
";

//Create adapter data access, where OrderContext your DbContext
var dataAdapter = new OeEfCoreDataAdapter<Model.OrderContext>();
//Create query parser
var parser = new OeParser(new Uri("http://dummy"), dataAdapter, dataAdapter.BuildEdmModel());
//Serialized entities in JSON UTF8 format
var request = new MemoryStream(System.Text.Encoding.UTF8.GetBytes(batch));
//The result of the query
var response = new MemoryStream();
//Execute query
await parser.ExecuteBatchAsync(request, response, CancellationToken.None);

An example of executing a stored procedure

//Create adapter data access, where OrderContext your DbContext
var dataAdapter = new OeEfCoreDataAdapter<Model.OrderContext>();
//Create query parser
var parser = new OeParser(new Uri("http://dummy"), dataAdapter, dataAdapter.BuildEdmModel());
//The result of the stored procedure
var response = new MemoryStream();
//Execute sored procedure
await parser.ExecuteGetAsync(new Uri("http://dummy/GetOrders(name='Order 1',id=1,status=null)"), OeRequestHeaders.Default

To specify a procedure name that differs from a method name in c#, you can use the attribute

 [Description("dbo.GetOrders")]
public IEnumerable<Order> GetOrders(int? id, String name, OrderStatus? status) => throw new NotImplementedException();

Other examples can be found in the test folder

Server-Driven Paging

To use responses that include only a partial set of the items identified by the request indicate maximum page size through the invoke method OeRequestHeaders.SetMaxPageSize(int maxPageSize). The service serializes the returned continuation token into the $skiptoken query option and returns it as part of the next link annotation (@odata.nextLink) to the client. If request returns result set sorted by nullable property, should set OeDataAdapter.IsDatabaseNullHighestValue (SQLite, MySql, Sql Server set false, for PostgreSql, Oracle set true).

//Create adapter data access, where OrderContext your DbContext
var dataAdapter = new OeEfCoreDataAdapter<Model.OrderContext>(Model.OrderContext.CreateOptions())
{
 IsDatabaseNullHighestValue = true //PostgreSql
};
//Create query parser
var parser = new OeParser(new Uri("http://dummy"), dataAdapter, dataAdapter.BuildEdmModel());
//Query
var uri = new Uri("http://dummy/Orders?$select=Name&$orderby=Date");
//Set max page size
OeRequestHeaders requestHeaders = OeRequestHeaders.JsonDefault.SetMaxPageSize(10);
//The result of the query
var response = new MemoryStream();
//Execute query
await parser.ExecuteGetAsync(uri, requestHeaders, response, CancellationToken.None);

To use server side paging in expanded to-many navigation properties, should invoke method OeRequestHeaders.SetNavigationNextLink(true)

//Query
var uri = new Uri("http://dummy/Orders?$expand=Items");
//Set max page size, to-many navigation properties
OeRequestHeaders requestHeaders = OeRequestHeaders.JsonDefault.SetMaxPageSize(10).SetNavigationNextLink(true);
//The result of the query
var response = new MemoryStream();
//Execute query
await parser.ExecuteGetAsync(uri, requestHeaders, response, CancellationToken.None);

A function specific to the Entity Framework Core

For the Entity Framework Core provider, there is the ability to cache queries, which on existing tests allows you to increase the performance up to two times. The cache key is a parsed OData query with remote constant values, and the value is a delegate accepting the data context and returning the query result. This allows to exclude the stage of building the data query itself (IQueryable). To use this feature, you need to use the constructor OeEfCoreDataAdapter(DbContextOptions options, Db.OeQueryCache queryCache)

For use pooling (DbContextPool) in Entity Framework Core create instance OeEfCoreDataAdapter use constructor with DbContextOptions parameter.

//Create adapter data access, where OrderContext your DbContext
var dataAdapter = new OeEfCoreDataAdapter<Model.OrderContext>(Model.OrderContext.CreateOptions());

Source code structure

The source code is divided into two parts: the source folder contains the library itself and assemblies to access various data sources, and the test folder contains tests and code samples.

Files of solutions are located in the sln folder.

The library itself is located in the source/OdataEntity project.

Adapter to the Entity Framework 6.2 context – in source/OdataToEntity.Ef6.

Adapter to the Entity Framework Core context – in source/OdataToEntity.EfCore.

Adapter to the Linq2Db context – in source/OdataToEntity.Linq2Db.

Tests:

Entity Framework Core in-memory database test/OdataToEntity.Test

Entity Framework Core SQL Server test/OdataToEntity.Test.EfCore.SqlServer

Entity Framework Core PostgreSql test/OdataToEntity.Test.EfCore.PostgreSql

Entity Framework 6 SQL Server test/OdataToEntity.Test.Ef6.SqlServer

Linq2Db SQL Server test/OdataToEntity.Test.Linq2Db

Examples of queries can be found in tests

[expand title=”Tested OData requests”]

OrderItems?$apply=filter(Order/Status eq OdataToEntity.Test.Model.OrderStatus'Processing')
Orders?$apply=filter(Status eq OdataToEntity.Test.Model.OrderStatus'Unknown')/groupby((Name), aggregate(Id with countdistinct as cnt))
OrderItems?$apply=groupby((Product))
OrderItems?$apply=groupby((OrderId, Order/Status), aggregate(Price with average as avg, Product with countdistinct as dcnt, Price with max as max, Order/Status with max as max_status, Price with min as min, Price with sum as sum, $count as cnt))
OrderItems?$apply=groupby((OrderId), aggregate(Price with sum as sum))/filter(OrderId eq 2 and sum ge 4)
OrderItems?$apply=groupby((OrderId), aggregate(Price with sum as sum))&$filter=OrderId eq 2
OrderItems?$apply=groupby((OrderId, Order/Name))/filter(OrderId eq 1 and Order/Name eq 'Order 1')
OrderItems?$apply=groupby((OrderId), aggregate(Price mul Count with sum as sum))
OrderItems?$apply=groupby((OrderId, Order/Name))&$orderby=OrderId desc, Order/Name
OrderItems?$apply=groupby((OrderId, Order/Name))&$orderby=OrderId desc, Order/Name&$skip=1&$top=1
OrderItems?$apply=groupby((OrderId))&$orderby=OrderId&$skip=1
OrderItems?$apply=groupby((OrderId))&$top=1
OrderItems?$apply=groupby((OrderId), aggregate(substring(Product, 0, 10) with countdistinct as dcnt, $count as cnt))/filter(dcnt ne cnt)
Orders/$count
Orders?$expand=Customer,Items&$orderby=Id
Orders?$expand=AltCustomer,Customer,Items&$select=AltCustomerCountry,AltCustomerId,CustomerCountry,CustomerId,Date,Id,Name,Status&$orderby=Id
Customers?$expand=AltOrders($expand=Items($filter=contains(Product,'unknown'))),Orders($expand=Items($filter=contains(Product,'unknown')))
Customers?$expand=AltOrders($expand=Items),Orders($expand=Items)
OrderItems?$expand=Order($expand=AltCustomer,Customer)&$orderby=Id
Customers?$expand=Orders($expand=Items($orderby=Id desc))
Customers?$orderby=Id&$skip=1&$top=3&$expand=AltOrders($expand=Items($top=1)),Orders($expand=Items($top=1))
Customers?$expand=Orders($filter=Status eq OdataToEntity.Test.Model.OrderStatus'Processing')
Customers?$expand=AltOrders,Orders
Customers?$expand=Orders($select=AltCustomerCountry,AltCustomerId,CustomerCountry,CustomerId,Date,Id,Name,Status)
Orders?$expand=*&$orderby=Id
Orders?$filter=Items/all(d:d/Price ge 2.1)
Orders?$filter=Items/any(d:d/Count gt 2)
Orders?$filter=Status eq OdataToEntity.Test.Model.OrderStatus'Unknown'&$apply=groupby((Name), aggregate(Id with countdistinct as cnt))
Orders?$filter=Items/$count gt 2
Orders?$filter=Date ge 2016-07-04T19:10:10.8237573%2B03:00
Orders?$filter=year(Date) eq 2016 and month(Date) gt 3 and day(Date) lt 20
Orders?$filter=Date eq null
OrderItems?$filter=Price gt 2
OrderItems?$filter=Price eq null
Customers?$filter=Sex eq OdataToEntity.Test.Model.Sex'Female'
Customers?$filter=Sex eq null
Customers?$filter=Sex ne null and Address ne null
Customers?$filter=Sex eq null and Address ne null
Customers?$filter=Sex eq null and Address eq null
OrderItems?$filter=Count ge 2
OrderItems?$filter=Count eq null
OrderItems?$filter=Order/Customer/Name eq 'Ivan'
Customers?$filter=Address eq 'Tula'
Customers?$filter=concat(concat(Name,' hello'),' world') eq 'Ivan hello world'
Customers?$filter=contains(Name, 'sh')
Customers?$filter=endswith(Name, 'asha')
Customers?$filter=length(Name) eq 5
Customers?$filter=indexof(Name, 'asha') eq 1
Customers?$filter=startswith(Name, 'S')
Customers?$filter=substring(Name, 1, 1) eq substring(Name, 4)
Customers?$filter=tolower(Name) eq 'sasha'
Customers?$filter=toupper(Name) eq 'SASHA'
Customers?$filter=trim(concat(Name, ' ')) eq trim(Name)
Customers(Country='RU',Id=1)
Orders(1)?$expand=Customer,Items
Orders(1)/Items?$filter=Count ge 2
OrderItems(1)/Order/Customer
OrderItems(1)/Order?$apply=groupby((CustomerId), aggregate(Status with min as min))
Orders(1)/Items?$orderby=Count,Price
OrderItems?$orderby=Id desc,Count desc,Price desc
OrderItems?$orderby=Order/Customer/Sex desc,Order/Customer/Name,Id desc
Orders?$filter=AltCustomerId eq 3 and CustomerId eq 4 and ((year(Date) eq 2016 and month(Date) gt 11 and day(Date) lt 20) or Date eq null) and contains(Name,'unknown') and Status eq OdataToEntity.Test.Model.OrderStatus'Unknown'
&$expand=Items($filter=(Count eq 0 or Count eq null) and (Price eq 0 or Price eq null) and (contains(Product,'unknown') or contains(Product,'null')) and OrderId gt -1 and Id ne 1)
Orders?$select=AltCustomer,AltCustomerId,Customer,CustomerId,Date,Id,Items,Name,Status&$orderby=Id
Orders?$select=Name
Customers
Customers?$orderby=Id&$top=3&$skip=2
Orders?$expand=Items&$count=true&$top=1
OrderItems?$filter=OrderId eq 1&$count=true&$top=1

[/expand]

Examples:

HTTP сервис test/OdataToEntityCore.Asp/OdataToEntity.Test.AspServer

HTTP Mvc сервис test/OdataToEntityCore.Asp/OdataToEntity.Test.AspMvcServer

Microsoft.OData.Client for HTTP service test/OdataToEntity.Test.AspClient

Microsoft.OData.Client and WCF servers source/OdataToEntity.Test.Wcf

An example contract of a Wcf service that works with Microsoft.OData.Client

[ServiceContract]
public interface IOdataWcf
{
   [OperationContract]
   Task<Stream> Get(String query, String acceptHeader);
   [OperationContract]
   Task<OdataWcfPostResponse> Post(OdataWcfPostRequest request);
}

[MessageContract]
public sealed class OdataWcfPostRequest
{
   [MessageHeader]
   public String ContentType { get; set; }
   [MessageBodyMember]
   public Stream RequestStream { get; set; }
}

[MessageContract]
public sealed class OdataWcfPostResponse
{
   [MessageBodyMember]
   public Stream ResponseStream { get; set; }
}

The script for creating an SQL Server database for tests test\OdataToEntity.Test.EfCore.SqlServer\script.sql

The script for creating an SQL Server database for tests test\OdataToEntity.Test.EfCore.PostgreSql\script.sql

→ Source code

→ Nuget packages