How do I map an OData query against a DTO to another entity?

Question

My question is very similar to this one: How do I map an OData query against a DTO to an EF entity? I have a simple setup to test the ASP.NET Web API OData V4 $filter functionality. What I would like to do is to “alias” some properties of the ProductDTO to match the properties of Product entity. The user will call the ProductsController for example with the following request:

GET products?$filter=DisplayName eq ‘test’

The Product class:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Level { get; set; }
    public Product()
    { }
}

The ProductDTO class:

public class ProductDTO
{
    public int Id { get; set; }
    public string DisplayName { get; set; }
    public int DisplayLevel { get; set; }
    public ProductDTO(Product product)
    {
        this.DisplayName = product.Name;
        this.DisplayLevel = product.Level;
    }
}

The ProductsController:

public class ProductsController : ApiController
{
    public IEnumerable<ProductDTO> Get(ODataQueryOptions<Product> q)
    {
        IQueryable<Product> products = this._products.AsQueryable();
        if (q.Filter != null) products = q.Filter.ApplyTo(this._products.AsQueryable(), new ODataQuerySettings()) as IQueryable<Product>;
        return products.Select(p => new ProductDTO(p));
    }
}

Of course I’m getting the following exception:

Could not find a property named 'DisplayName' on type 'TestAPI.Models.Product'

I tried to use the newly introduced aliasing feature by adding the following lines to the WebApiConfig.cs

public static class WebApiConfig
{
    public static void Register(HttpConfiguration config)
    {
        …
        IEdmModel model = GetModel();
        config.MapODataServiceRoute("*", "*", model);
    }

    private static IEdmModel GetModel()
    {
        ODataModelBuilder builder = new ODataConventionModelBuilder();
        EntitySetConfiguration<Product> products = builder.EntitySet<Product>("Product");
        products.EntityType.Property(p => p.Name).Name = "DisplayName";
        products.EntityType.Property(p => p.Level).Name = "DisplayLevel";
        return builder.GetEdmModel();
    }
}

I suppose that I'm using the aliasing feature incorrectly, because the same exception as described above is thrown. If I invoke the following request it works, but this is not what I'm trying to achieve:

GET products?$filter=Name eq ‘test’

Update:

I agree with gdoron, the Get endpoint should look like this:

public IEnumerable<ProductDTO> Get(ODataQueryOptions<ProductDTO> q)

But this should be solvable without AutoMapper?

Answer 1

I found a solution without using AutoMapper.

The ProductsController now looks like this:

public class ProductsController : ApiController
{
    public IEnumerable<ProductDTO> Get(ODataQueryOptions<ProductDTO> q)
    {
        IQueryable<Product> products = this._products.AsQueryable();

        IEdmModel model = GetModel();
        IEdmType type = model.FindDeclaredType("TestAPI.Models.Product");
        IEdmNavigationSource source = model.FindDeclaredEntitySet("Products");
        ODataQueryOptionParser parser = new ODataQueryOptionParser(model, type, source, new Dictionary<string, string> { { "$filter", q.Filter.RawValue } });
        ODataQueryContext context = new ODataQueryContext(model, typeof(Product), q.Context.Path);
        FilterQueryOption filter = new FilterQueryOption(q.Filter.RawValue, context, parser);

        if (filter != null) products = filter.ApplyTo(products, new ODataQuerySettings()) as IQueryable<Product>;
        return products.Select(p => new ProductDTO(p));
    }
}

The WebApiConfig:

public static class WebApiConfig
{
    public static void Register(HttpConfiguration config)
    {
        …
        IEdmModel model = GetModel();
        config.MapODataServiceRoute("*", "*", model);
    }

    private static IEdmModel GetModel()
    {
        ODataModelBuilder builder = new ODataConventionModelBuilder();
        EntitySetConfiguration<Product> product = builder.EntitySet<Product>("Products");
        product.EntityType.Name = "Product";
        product.EntityType.Namespace = "TestAPI.Models";
        product.EntityType.Property(p => p.Name).Name = "DisplayName";
        product.EntityType.Property(p => p.Level).Name = "DisplayLevel";
        return builder.GetEdmModel();
    }
}

Answer 2

If you decided you want to use DTOs (which is definitely a good idea in my opinion), then use it...
The $metadata should reflect the DTO's properties names and not of the EF entity, since this is what clients get and this is what the clients should send.
It means you should change the Get endpoint to something like this:

public IEnumerable<ProductDTO> Get(ODataQueryOptions<ProductDTO> q)

To avoid the coupling between ProductDTO and Product you can use AutoMapper to map between the classes for you. Also, if you use AutoMapper's Project method, you can cleanup you methods to somthing like:

public IQueryable<ProductDTO> Get(ProductDTO dto)

You can check Asp.net official demo for versioning, it heavily uses DTOs and AutoMapper, it will give you a good direction, just ignore the versioning if it doesn't interest you now.

Answer 3

For those using .NET 6 with Microsoft.AspNetCore.OData 8.0.8 you can do it like so:

[ApiController]
[Route("[controller]")]
public class ProductsController : ControllerBase
{
    private readonly MyDbContext _context;

    public ProductsController(MyDbContext context)
    {
        _context = context; 
    }

    [HttpGet]
    [EnableQuery]
    public IQueryable<ProductDto> Get()
    {
        return _context.Products
            .Select(p => new ProductDTO()
            {
                DisplayName = p.Name,
                DisplayLevel = p.Level
            });
    }
}

And in your startup:

builder.Services
    .AddControllers()
    .AddOData(opt => opt.Filter().Select())

Note that the key is in the projection. Changing it to .Select(p => new ProductDto(p) will not work since it cannot be translated into SQL. There is no longer any need for an EDM-model.

Given there's a table named Products that look something like this:

A GET for this url:

http://localhost:XXXX/products?$filter=DisplayName eq 'Emma'&select=DisplayLevel

The result will be:

[{"DisplayLevel":3}]

Will generate a SQL looking something like this:

exec sp_executesql N'SELECT [p].[Level], [p].[Name]
FROM [Products] AS [p]
WHERE [p].[Name] = @__TypedProperty_0',N'@__TypedProperty_0 nvarchar(4000)',@__TypedProperty_0=N'Emma'

As can be seen in the SQL there is a downside with this approach and that is that the entire model Name and Level is fetched from the database despite the select in the url only requests DisplayLevel. This is probably a limitation due to the projection.

A complete example can be found here: https://github.com/smoksnes/ODataExample

Answer 4

Try using AutoMapper, you will need to add these references to your controller

using AutoMapper;
using AutoMapper.QueryableExtensions;

Your method

[EnableQuery(AllowedQueryOptions = AllowedQueryOptions.All)]
public IQueryable<ObjectDTO> Get()
{
    return dbContext.Entities.ProjectTo<ObjectDTO>();
}

In your global

protected void Application_Start()
{
        //Usually in a diff class Mapping.ConfigureDataTransferObjects();
        Mapper.CreateMap<MyEntity, ObjectDTO>();
        Mapper.CreateMap<ObjectDTO, MyEntity>();
}

Answer 5

To me the solution was just adding the DTO to the EDM config (v4):

edmBuilder.EntitySet<Contact>("Contacts");
edmBuilder.EntityType<ContactDto>();

Answer 6

Patrick, you can fill a destinationvalue from a calculated sourceValue, like:

Mapper.CreateMap<Customer, CustomerDTO>()
    .ForMember(dest => dest.InvoiceCount, opt =>
        opt.MapFrom(src => src.Invoices.Count()));

I got this Example from: http://codethug.com/2015/02/13/web-api-deep-dive-dto-transformations-and-automapper-part-5-of-6/

Arturo, you can use reverseMap on the CreateMap if it is not a complex mapping, to do a one-liner.