ASP.NET Web API return queryable DTOs?

Question

I built a nice little API with the ASP.NET Web API, but I guess it's not right to return the entities from my context (entity framework) AsQueryable, so I'm mapping everything to DTO objects.

What I don't quite understand however: how can I keep my context queryable, but still only return DTO's instead of entities? Or is this not possible?

This is my code:

public IQueryable<ItemDto> Get()
{
    using (EfContext context = new EfContext())
    {
        Mapper.CreateMap<Item, ItemDto>()
            .ForMember(itemDto => itemDto.Category, mce => mce.MapFrom(item => item.Category.Name));

        IEnumerable<ItemDto> data = Mapper.Map<IEnumerable<Item>, IEnumerable<ItemDto>>(context.Items
            .OrderByDescending(x => x.PubDate)
            .Take(20));

        return data.AsQueryable();
    }
}

As you can see I load the data, and make that little IEnumerable collection queryable. The problem is that the query that is generated for this piece of code is probably quite inefficient because it loads all the items first (or at least the 20 first items) and then filters the output.

I hope I described my problem as good as possible, it's a little bit hard to explain. I couldn't find anything about it on Google.

Answer 1

Don't select everything in memory first. Do something like this:

public IQueryable<ItemDto> Get()
{
    using (EfContext context = new EfContext())
    {
        var query = from item in context.Items
                    select Mapper.Map<Item, ItemDto>(item)

        return query.OrderByDescending(x => x.PubDate).Take(20));
    }
}

BTW The following code is something you want to do once, for example in a static constructor or in the WebApiConfig.cs file.

Mapper.CreateMap<Item, ItemDto>()
    .ForMember(itemDto => itemDto.Category, mce => mce.MapFrom(item => item.Category.Name));

Answer 2

If the only querying takes place in the code we see (i.e. ordering and Take ) your code is fine. It will only map the result (max 20). However, since you are returning IQueryable I assume you'd like to further query the result. May be OData style parameters?

With max 20 items you're probably better off not writing any code. The rest of the queries will be performed as object queries. However, if you decide to remove that constraint (max 20) or put that after further queries are made then this way will be inefficient.

Basically, you need to move the mapping at the very end of the query chain if you'd like all your queries run in the EF database.

What you can do is actually return the actual entity objects

    public IQueryable<ItemDto> Get()
    {
        using (EfContext context = new EfContext())
        {
            return context.items
                       .OrderByDescending(x => x.PubDate)
                       .Take(20));
         }
     }

And tell MVC how to serialize this in a MediaTypeFormatter. Here you could use the AutoMapper.

Answer 3

http://dotnetplusplus.wordpress.com/2013/08/30/odata-web-apis-with-automapper-3/

Use return _itemRepository .GetItemsQuery() .Project().To();