Using EF and WebAPI, how can I return a ViewModel

Question

This question already has an answer here:

• Web API Queryable - how to apply AutoMapper? 4 answers

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?

Answer 1

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)

Answer 2

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>();
         }
    ...

Answer 3

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.