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
– mare, Commented Jun 1, 2014 at 16:20

David Ferenczy Rogožan · Accepted Answer · 2015-10-27 12:48:48Z

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, 2015 at 12:48

David Ferenczy Rogožan

25.7k12 gold badges88 silver badges73 bronze badges

answered Mar 27, 2012 at 18:22

Maurice

27.7k5 gold badges52 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

user29439 Over a year ago

Does that work in EF4? As best I recall, EF didn't let you map to types that weren't defined in EF.

Leon Cullens Over a year ago

So I only have to define my mapping once on application startup? Didn't know that. Thanks for pointing it out :)

Maurice Over a year ago

@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 Over a year ago

@Avalaxy. Yes it only needs to be done once. Mapper.CreateMap() is a relatively slow and expensive call to speed up Mapper.Map().

tugberk Over a year ago

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

|

cellik · Accepted Answer · 2012-04-04 15:34:21Z

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.

ikutsin · Accepted Answer · 2013-11-07 07:56:33Z

3

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

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

answered Nov 7, 2013 at 7:56

ikutsin

1,14815 silver badges23 bronze badges

1 Comment

Isaac Llopis Over a year ago

Project().To() throws a StackOverflowException when navigation properties are navigable from both ends. Any way to overcome that?

Collectives™ on Stack Overflow

ASP.NET Web API return queryable DTOs?

3 Answers 3

8 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related