How to apply ODataQueryOptions<T> to IQueryable<X>

1
votes

I wish to apply ODataQueryOptions on to a DbSet. Let me give quick background of my app. I have a requirement to create an app that reads data from two tables T1 & T2. Both the tables have almost the same schema except a few columns in addition to each table. My entity class design is somewhat like below: (for the sake of brevity, I've kept the schema and nomenclature simple)

class Base
{
    [Key]
    public int ID;
    public string Name;
    public string Gender;
    public string Comment;
}

[Table("T1")]
class TableOneEntity : Base
{

}

[Table("T2")]
class TableTwoEntity : Base
{
    // extra columns from T2
    public string Country;
    public string City;
}

DbContext class:

class ApplicationDbContext : DbContext
{
    public DbSet<TableOneEntity> TableOneDbSet;
    public DbSet<TableTwoEntity> TableTwoDbSet;
}

My OData GET API looks like below:

public PageResult<TableTwoEntity> GetTableResult(ODataQueryOptions<TableOneEntity> options)
{
    var result1 = options.ApplyTo(appDbCtx.TableOneDbSet) as IQueryable<TableOneEntity>;

    var result2 = options.ApplyTo(appDbCtx.TableTwoDbSet) as IQueryable<TableTwoEntity>;

    return new PageResult<TableTwoEntity>(output, odataProperties.NextLink, odataProperties.TotalCount);
}

When the GET API is called I get the following exception:

Cannot apply ODataQueryOptions of 'TableOneEntity' to IQueryable of 'TableTwoEntity'.

How can I apply the same 'options' to the TableTwoDbSet ?

2
c#entity-frameworkodata

2 Answers

0
votes

The pipeline will translate the URL into a transformation on precisely one EDM entity class. So the short answer is you can't do what you're trying to do the way you're doing it. However, what you can do is to create a composite class (by using a view if you're using EF):

class Base
{
    [Key]
    public int ID;
    public string Name;
    public string Gender;
    public string Comment;
}

[Table("T1")]
class TableOneEntity : Base
{

}

[Table("T2")]
class TableTwoEntity : Base
{
    // extra columns from T2
    public string Country;
    public string City;
}

[Table("V1")] // which is a view containing columns from Base, T1 and T2.
class CompositeEntity : Base
{
    // extra columns from T1
    .....

    // extra columns from T2
    public string Country;
    public string City;
}

Then you can do this:

[EnableQuery(PageSize = 100)] // For server-driven paging -- recommended but not obligatory
public IHttpActionResult GetTableResult()
{
    return appDbCtx.Composite;
}

Remember to add your composite class to the EDM (usually in WebApiConfig.cs). And that's it. The OData pipeline will do all the rest of the work for you.

EDIT:

In fact, it turns out that you may be able to do what you're trying to do by using open types. See http://www.asp.net/web-api/overview/odata-support-in-aspnet-web-api/odata-v4/use-open-types-in-odata-v4. However, I suspect that the composite class/view would be the simpler option in your case.

0
votes

I normally include a reference to the OData WebApi nuget package.

Then, for a controller, add the Queryable attribute and return your collection as Queryable and you are done, just add OData query options to your calls.

The cool part is if you are using EF, then all the filters and constraints are passed all the way through to the DB queries.

public class MyController : ApiController
{
    // Add the queryable attribute
    [Queryable]
    IQueryable<stuff> Get() {
        ...
        ...
        return myStuff.AsQueryable();
}

You could then use it like so:

http://www.blabla.bla/api/v1/stuff?$top=10&$skip=20&$orderby=name

--- to give you the 20th page of 10 rows, ordered by name.

See the OData conventions for a good reference: