ASP.NET Web Api: Correct way to serve OData-queryable GET requests

Question

What is the right way to serve OData-queryable GET requests in ASP.NET Web Api? That may sound like a "what is better" question, but it should be a "what does work" question.

Some assumptions:

• To enable OData-querying, you have to put the Queryable attribute to the action method that returns IQueryable<Model>. Therefore, you have to expose the domain model?
• The domain model uses Entity Framework 5 and has navigation properties. The XML and Json Serializers do not like the EF proxies, so you have to disable them for OData queries?
• The serializers pick up the navigation properties and serve them to the user.

So if I have a Category type that has navigation properties for parent and children, the serializers complain that I have cyclic references, and I cannot get rid of this error.

I have read that I should use DTOs, but HOW? How can I provide a IQueryable<DTOModel> to the user that will create the appropriate SQL for the database? Remember, I want to use $filter and the like.

I just want to give the user a filterable list of Model objects without the serialized navigation properties.... but HOW?

Answer 1

You don't have to expose IQueryable<> - you can create a method that accepts an instance of ODataQueryOptions and process this yourself. Here's a code sample that does most of what you require. It should be more than enough for you to work out the solution that works best for you. This method will also allow you to keep your EF proxy classes.

using System.Web.Http.OData;
using System.Web.Http.OData.Builder;
using System.Web.Http.OData.Query;
[ActionName("Dto")]
public IList<DtoModel> GetDto(ODataQueryOptions<DtoModel> queryOptions)
{
    var data2 = DatabaseData();

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

    var t = new ODataValidationSettings() { MaxTop = 25 };
    var s = new ODataQuerySettings() { PageSize = 25 };
    newQueryOptions.Validate(t);
    IEnumerable<Model> results =
        (IEnumerable<Model>)newQueryOptions.ApplyTo(data2, s);

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

    IList<Model> internalResults = results.Skip(skip).Take(take).ToList();

    // map from Model to Dto here using AutoMapper
    AutoMapper.Mapper.CreateMap<Model, DtoModel>();
    IList<DtoModel> webResults =
        AutoMapper.Mapper.Map<IList<Model>, IList<DtoModel>>(internalResults);

    return webResults;
}

The data used in the example is a simple Queryable set of data:

private IQueryable<Model> DatabaseData()
{
    return (
        new Model[] { 
        new Model() { id = 1, name = "one", type = "a" },
        new Model() { id = 2, name = "two", type = "b" },
        new Model() { id = 3, name = "three", type = "c" },
        new Model() { id = 4, name = "four", type = "d" },
        new Model() { id = 5, name = "five", type = "e" },
        new Model() { id = 6, name = "six", type = "f" },
        new Model() { id = 7, name = "seven", type = "g" },
        new Model() { id = 8, name = "eight", type = "h" },
        new Model() { id = 9, name = "nine", type = "i" }
    })
    .AsQueryable();
}

These are the test classes:

public class Poco
{
    public int id { get; set; }
    public string name { get; set; }
    public string type { get; set; }
}
public class DtoModel
{
    public int id { get; set; }
    public string name { get; set; }
    public string type { get; set; }
}
public class Model
{
    public int id { get; set; }
    public string name { get; set; }
    public string type { get; set; }
    public virtual ICollection<Poco> Pocos { get; set; }
}