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.

Don't expose Web API endpoints as being IQueryable at all...If you really need that, go with Web API OData. Otherwise, just stick with plain old REST endpoints and expose any kind of filtering possible as parameters on your controller actions. — mare, Jun 01 '14 at 16:20

score 7 · Accepted Answer · edited Oct 27 '15 at 12:48

7

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));

edited Oct 27 '15 at 12:48

David Ferenczy Rogožan

23,966
9
79
68

answered Mar 27 '12 at 18:22

Maurice

27,582
5
49
62

Does that work in EF4? As best I recall, EF didn't let you map to types that weren't defined in EF. – Mar 27 '12 at 18:46
So I only have to define my mapping once on application startup? Didn't know that. Thanks for pointing it out :) – Leon Cullens Mar 27 '12 at 18:49
@Ryan. In the EF query you are retrieving EF entities, only those loaded are transformed to DTO's using AutoMapper. But this way you get lazy loading so the order/filter is done in the database and only the max 20 records are mapped from EF entities to DTO's. – Maurice Mar 27 '12 at 18:53
1

@Avalaxy. Yes it only needs to be done once. Mapper.CreateMap() is a relatively slow and expensive call to speed up Mapper.Map(). – Maurice Mar 27 '12 at 18:55
Ok, I tried it but this is the result: "LINQ to Entities does not recognize the method 'API.Models.ItemDto Map[Item,ItemDto](Entities.Item)' method, and this method cannot be translated into a store expression." – Leon Cullens Mar 27 '12 at 19:19
You are right, I forgot about that problem. You have to do a ToList() but then everything is in memory. I typically use code first and then you can just use the DTO in a query as any poco with matching names will work. – Maurice Mar 27 '12 at 19:36
2

You should execute `Take(20)` before going to the database. Otherwise, you will be slicing the in-memory collection which is not that efficient. Code: `var query = from item in context.Items.Take(20) select Mapper.Map(Item, ItemDto>(item);` – tugberk Dec 12 '12 at 10:27
This one clearly is not an answer. How this one was ever accepted? @Maurice, consider edit this answer to reflect your last comment. Appreciated. – André Werlang Jan 19 '15 at 00:28

score 3 · Answer 2 · answered Apr 04 '12 at 15:34

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.

score 3 · Answer 3 · answered Nov 07 '13 at 07:56

3

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

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

answered Nov 07 '13 at 07:56

ikutsin

1,118
15
22

Project().To() throws a StackOverflowException when navigation properties are navigable from both ends. Any way to overcome that? – Isaac Llopis May 29 '14 at 08:53

ASP.NET Web API return queryable DTOs?

3 Answers3

Linked