Using EF and WebAPI, how can I return a ViewModel AND support IQueryable/OData?

Question

I've got an ASP.NET WebAPI project. I've recently created EntityFramework entities for all my data tables. But I don't want to expose my data layer & schema to my users. How can I map my entities to a ViewModel (automapper?) and provide IQueryable return type so that my API supports OData?

OData supports query composition and SQL-like parameters. I guess I'd need to provide some kind of 2-way translation for the query-composition part? Does that mean a custom LINQ provider? I hope it's easier than that.

Or should I give up on IQueryable/OData?

score 13 · Accepted Answer · edited May 23 '17 at 12:17

13

I found the answer here: Web API Queryable - how to apply AutoMapper?

Instead of using [Queryable] you can use a parameter of type ODataQueryOptions<T> to apply OData operations against any type or LINQ query you wish. Here's a great example that doesn't even need to use AutoMapper:

public virtual IQueryable<PersonDto> Get(ODataQueryOptions<Person> odataQuery){
    odataQuery.Validate(new ODataValidationSettings(){
        AllowedFunctions = AllowedFunctions.AllMathFunctions
    });
    var people = odataQuery.ApplyTo(uow.Person().GetAll());
    return ConvertToDtos(people);
}

Here's the Microsoft page explaining the specifics of this usage. (about half way down)

edited May 23 '17 at 12:17

Community

1
1

answered Mar 05 '13 at 17:53

Eric Falsken

4,796
3
28
46

is this still viable in Web Api 2 ? – Bart Calixto Feb 22 '14 at 00:24
Absolutely! of course it is. Now there are just more options. – Eric Falsken Feb 23 '14 at 17:14
3

I don't think this method works any more. The method does not get called unless the parameter is changed to ODataQueryOptions. If you change the parameter to that, then the method does get called, but odataQuery.ApplyTo() throws an exception, saying that the query applies to the type PersonDto, not Person. – sheamus Mar 24 '14 at 07:30
1

@sheamus, you will need to ApplyTo the Dto type. like: `odataQuery.ApplyTo(ConvertToDtos(uow.Person().GetAll()))` – Eric Falsken Mar 25 '14 at 16:14
6

@EricFalsken won't that cause all of the Persons to be pulled into memory so ConvertToDtos can operate on them? – sheamus Apr 02 '14 at 06:14
1

Replace `ConvertToDtos` with `Automapper.ConvertTo()` or `odataQuery.ApplyTo(db.Person().GetAll().To())` and you'll get back a LINQ query that will only convert the necessary objects and will NOT get back everything from the DB, as long as you use the 2-way object binding syntax. – Eric Falsken Apr 03 '14 at 15:53
3

@EricFalsken but if you convert it to a DTO before performing the query, how will you do relations? For example I get this error when trying to expand: No NavigationLink factory was found for the navigation property 'Address' from entity type 'PersonDto' on entity set 'Persons' – Roger Far Jul 03 '14 at 21:07

score 6 · Answer 2 · answered Mar 01 '13 at 09:29

I was able to successfully test this using a ViewModel class.

public class InvoiceViewModel
{
    public int InvoiceID { get; set; }
    public string InvoiceNumber { get; set; }
}

in the Get, select from your entity into your viewmodel:

public override IQueryable<InvoiceViewModel> Get()
    {
        var ctx = new CreditPointEntities();
        return ctx.Invoices.Select(i => new InvoiceViewModel
            {
                InvoiceID = i.InvoiceID,
                InvoiceNumber = i.InvoiceNumber
            }).AsQueryable();
    }

Make sure you use the viewmodel in your modelbuilder line in webapiconfig.cs

modelBuilder.EntitySet<InvoiceViewModel>("Invoice");

with this, you can use a url like

http://website/odata/Invoice?$filter=InvoiceID eq 1

I confirmed through sql profiler that the filter was being passed through to SQL.

score 1 · Answer 3 · answered Feb 21 '13 at 17:34

1

if you are using Automapper, you could use projections in it. Example:

    public class ProductsController : EntitySetController<Product, int>
    {
         private DbProductsContext _db = new DbProductsContext();

         public override IQueryable<ProductDto> Get()
         {
            return _db.Products.Project().To<ProductDto>();
         }
    ...

answered Feb 21 '13 at 17:34

Kiran

56,921
15
176
161

It's really irrespective of using AutoMapper. It's a function of the fact that the EntityFramework LINQ provider (at least with DbContext, someone said ObjectContext didn't work) supports the fact that you can query results on a projection (Select) and it will recognize and translate it into a query on the original data source. – Rich Feb 21 '13 at 19:15
How does LINQ allow queries against the viewmodel to be translated to a query on the original datasource? I don't understand that. I assume that it definitely wouldn't work if we have to use a typeconverter (custom method) to map classes? – Eric Falsken Feb 22 '13 at 18:39
I understand that IQueryable would allow OData queries to execute against the translated class, but I don't see how this could work without retrieving all possible `Products` and converting them to `ProductDto` instances before filtering. How can the OData query get translated into proper SQL? – Eric Falsken Feb 23 '13 at 00:12
Eric, at some point in your Get, you have to provide some translation from your DTO/ViewModel to the EntityFramework class, whether through direct mapping, like in my example, or through auto mapper or something similar. The OData library can use this to determine how to pass the filter on to the entity framework, so that your Select is translated into proper SQL. – Gareth Suarez Jan 25 '14 at 04:53

Using EF and WebAPI, how can I return a ViewModel AND support IQueryable/OData?

3 Answers3

Linked