ASP.NET Web API OData - Translating DTO queries into Entity queries

Question

We are currently investigating the use of OData query syntax in our Web APIs. We are not looking to implement a full OData implementation - merely leverage the query syntax.

It is generally considered good application architecture to separate your application into several layers. In modern web applications these layers would include a data layer and UI/Transport layer which may model the information stored in your database differently when sending it to your customers.

eg: Your may have a database (Entity Framework) model that looks like this:

public class Employee
{
    public Guid Id {get; set;}
    public string Name {get; set;}
    public int AccessLevel {get; set;}
}

but your Web APIs may expose this data to your customers in a different wire format:

public class EmployeeDto
{
    public string Name {get; set;}
    public string SecurityClearence {get; set;}
}

Using ASP.NET Web API and (presumably??) the Microsoft ASP.NET Web API OData libraries how would we achieve a scenario whereby our customers would write a query against the DTO format eg:

?$filter=(SecurityClearence eq 'TopSecret')

... and we would then translate that against our data format. eg:

?filter=(AccessLevel eq 007)

or some other format that will allow me to dynamically query my database such as an Expression. eg:

db.Employees.Where(translatedExpression);

I have thought of several ways to achieve this manually but I'm keen to know how other people would solve this because I feel my implementation so far is quite crude and unlikely to hold up to scrutiny.

Are there features of the Web API OData libraries (and related EDM libraries) that would achieve some or all of this for me?

Exclave

Answer 1

Gavin,

I know I might be way too late for this party, but here are my two cents on this matter.

Back in 2013, I was working in a project and came up with this very same scenario. I had an entity model and also a DTO model to be transferred in and out the service layer. I did my research but did not find any libraries that would allow me to: 1. Map from my entity model to my DTO model in an automated way and 2. Translate filtering and sorting expressions on the DTO to the entity model.

In the end I decided to code my own "projections" framework. A few months ago I published the code at GitHub and uploaded a NuGet package in case someone would need to do something like that.

Now, in case you decide to try it, you have to build and configure a ProjectionsModel (similar to how you would configure a EF code-first data model) and specify how the DTO model classes map to the entity model ones.

using ExpressionFramework.Projections;

namespace TestDTO
{
    public class MyProjectionModel : ProjectionModel
    {
        protected override void OnModelCreating(ProjectionModelBuilder modelBuilder)
        {
            modelBuilder
                .Projection<UserAccountDTO>()
                .ForSource<UserAccountEntity>(configuration => 
                {
                    configuration.Property(dto => dto.RoleCount).ExtractFrom(entity => entity.Roles.Count());
                });
        }
    }
}

Then, you can create a model and use it to project and filter DTO queries into entity queries:

IQueryable<UserAccountEntity> usersQuery = usersList.AsQueryable();

var myProjectionModel = new MyProjectionModel();
var dtoUsersList = myProjectionModel.Project<UserAccountDTO, UserAccountEntity>(usersQuery).ToList();

Even tough filtering is not part of the example, you can create a collection of QueryFilter and QuerySorter objects and send them as parameters to the Project method.

Let me know if you want to know more about this library.

Ludendorff

This work you've done, is truly superb. Thank you so much. – Violette

Answer 2

We have been using a combination of Entity Framework, Automapper and LinqToQueryable.

A typical controller action would look like this:

[HttpGet]
[LinqToQueryable]
public IEnumerable<SomeViewModel> SomeAction()
{
    return _databaseContext.Entities
                           .Where(e => e.SomeProp = "Example")
                           .Project()
                           .To<SomeViewModel>();
}

The nuget packages we needed were, LinqToQuerystring, linqtoquerystring.entityframework, LinqToQuerystring.WebApi. The docs are here: http://linqtoquerystring.net/

Morganatic

I'll take a look at that package, I looked at writing something similar myself. I didn't like what I came up with for two reasons: 1) The projections were being done in SQL which meant we were no longer querying over key indexes. 2) We version our APIs and I didn't want to write a different projection per API version. My preferred approach is to translate they query before it hits the database. Though this feels a lot harder. – Exclave

Answer 3

Our company is looking to do this as well. One possible solution I have been mulling over is creating a custom IQueryable LINQ Provider. This provider would be able to translate the clean model to the database model and query on the appropriate fields, as well as transform the data.

Piece of cake, right? Um.. yea.. we haven't started yet.

If anyone has additional suggestions, let me know.

Walkthrough: Creating an IQueryable LINQ Provider

LINQ: Building an IQueryable Provider - Part I

Sick