ASP.NET WebApi OData support for DTOs

Question

I have Project entity and ProjectDTO. I'm trying to create an WebAPI controller method that can take and return ProjectDTOs and make it support OData.

The problem is that I'm using ORM that can query the database using Project entity not Project DTO. Is there any way that I can apply filtering/sorting/paging from OData based on ProjectDTO to Project entity query?

public ODataQueryResult<ProjectDTO> GetProjects(ODataQueryOptions<ProjectDTO> query)
{
    var context = new ORM_Context();

    var projects = context.Projects; // IQueryable<Project>
    var projectDtos = query.ApplyTo(projectDTOs)); // <-- I want to achieve something similar here
    var projectDTOs =
        projects.Select(
            x =>
            new ProjectDTO
                {
                    Id = x.Id,
                    Name = x.Name
                });

    var projectsQueriedList = projectDtos.ToList();

    var result = new ODataQueryResult<ProjectDTO>(projectsQueriedList, totalCount);

    return result;
}

Answer 1

Something like this (I haven't tried to compile it)

using(var dataContext = new ORM_Context())
{
    var projects = dataContext.Projects; // IQueryable<Project>

    //Create a set of ODataQueryOptions for the internal class
    ODataModelBuilder modelBuilder = new ODataConventionModelBuilder();
    modelBuilder.EntitySet<Project>("Project"); 
    var context = new ODataQueryContext(
         modelBuilder.GetEdmModel(), typeof(Project));
    var newOptions = new ODataQueryOptions<Project>(context, Request);

    var t = new ODataValidationSettings() { MaxTop = 25 };
    var s = new ODataQuerySettings() { PageSize = 25 };
    newOptions.Validate(t);
    IEnumerable<Project> internalResults =
        (IEnumerable<Project>)newOptions.ApplyTo(projects, s);

    int skip = newOptions.Skip == null ? 0 : newOptions.Skip.Value;
    int take = newOptions.Top == null ? 25 : newOptions.Top.Value;

    var projectDTOs =
            internalResults.Skip(skip).Take(take).Select(x =>
                new ProjectDTO
                    {
                        Id = x.Id,
                        Name = x.Name
                    });

    var projectsQueriedList = projectDtos.ToList();
    var result = new ODataQueryResult<ProjectDTO>(
        projectsQueriedList, totalCount);
    return result;
}

Answer 2

I think the easiest way to do this is by using AutoMapper. So, for your DTO

    [DataContract(Name = "Products")]
    public class ProductDTO
    {
        [Key]
        [DataMember]
        public string MyProductMember1 { get; set; }

        [DataMember]
        public string MyProductMember2 { get; set; }
        ...
    }

you should write somewhere in AutoMapper configuration:

Mapper.CreateMap<Product, ProductDTO>();

and somewhere in building IEdmModel for OData:

builder.EntitySet<ProductDTO>("Products");

and the code for your controller will look like

public class ProductsController : ODataController
{
    [EnableQuery]
    public IHttpActionResult Get()
    {
        var products = context.Products; // IQueryable<Product>
        return Ok(products.Project().To<ProductDTO>());
    }
}

This way you don't need to expose your ORM entities directly, and can use OData for filtering, paging, count, and even expand nested collections, and for EF it will translate into corresponding SQL requests using table to which Product is mapped. But be careful: for more complicated cases (nested collections, for example) it may result in non-optimal SQL request.

Answer 3

Try this:

    public object GetProjects(ODataQueryOptions<Project> query)
    {
        var context = new ORM_Context();

        var projects = query.ApplyTo(context.Projects);
        var projectDTOs = projects.Select(
                x =>
                new ProjectDTO
                    {
                        Id = x.Id,
                        Name = x.Name
                    });

        return new
        {
            TotalCount =  Request.GetInlineCount(), //before paging
            Results = projectDTOs.ToList()
        };
    }

Apparently the most important thing here is to pass the correct type to ODataQueryOptions<> and then it performs its magic just fine. This is because it uses that particular type to query your collection/db context so it must the of the type that is actually stored in the collection/context rather that what you are trying to return.

Obviously, your DTOs should closely resemble your ORM objects (and they do in your snippet) or this will not work very well from user's/client's perspective.

I did not try to compile the above code cause I don't have your classes and other infrastructure but it should be sufficient to convey the idea.