OData over a "View" of unioned data in Entity Framework

Question

I'm developing an OData service which exposes a model created with Entity Framework 6. These entities are read only and are quite large to load. (Several gigabytes)

To accomplish this I'm using Microsoft.OData.EntityFrameworkProvider, (I'm not currently using WebAPI) This is mostly working fine, however I have a new requirement that needs to expose an entity which is actually a union of multiple other entities for convenience to our EndUser / customer.

EF Context Snippet

    ...
    public DbSet<Foo> FooRecs { get; set; }
    public DbSet<Bar> BarRecs { get; set; }
    public IQueryable<FooBarRec> FooBarRecs
    {
        get
        {
            return FooRecs.Select(f => new FooBarRec() { Id = f.Id, Description =  f.Description })
                .Union(
                BarRecs.Select(b => new FooBarRec() { Id = b.Id, Description = b.Description })
                );
        }
    }
    ...

I'm unable to expose this IQueryable property through odata as it appears that the EntityFrameworkProvider only exposes DbSets and not any IQueryable object, which makes sense.

My question is, what is the best approach to accomplish this with OData?

I'd like to avoid loading duplicate data into a third intermediate table as the data can be large, and the time to load that data which is done nightly. It appears that QueryInterceptor only lets you subfilter data which is already queried so that doesn't seem to work.

I tried doing crazy things like extend DbSet and make my own DbViewSet which takes both sets in it's constructor, but couldn't find a way to close the loop.

What is the best way to accomplish something analogous to a view with OData and EF6?

Thanks!

For clarity, technically FooBarRec is of the same structure as Foo, but not as Bar. Regardless, you can't project a query onto a Mapped Entity: stackoverflow.com/questions/5325797/… In this case I settled for creating an unmapped POCO to represent the unioned set so that I can sub filter the entity set. My question is how I can represent this in OData — Kent
I know this isn't helpful but, you need the union serverside in TSQL and a mapped POCO to game OData into working for you. — Jodrell
if the union is performed by a view EF can still apply extra conditions as and when ODataController specifies. — Jodrell
You can't mix the Entity Frawmework Data Provider and an other provider in the same service. Read WCF, OData, DbContext, and Joins — nlips

Sam Xu Sam Xu · Accepted Answer · 2014-03-25T07:31:50

I create an example with Web API & OData. It looks very simple and can meet your requirement:

First, I define the following CLR classes to mapping your types and needn't create any view:

public class FooBarRec
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Foo FooRec { get; set; }
    public Bar BarRec { get; set; }
}

public class Foo
{
    public int FooId { get; set; }
    public string FooName { get; set; }
}

public class Bar
{
    public int BarId { get; set; }
    public string BarName { get; set; }
}

Next, I create an OData EdmModel based on the above CLR types:

private static IEdmModel GetEdmModel()
{
    ODataModelBuilder builder = new ODataConventionModelBuilder();
    builder.EntitySet<FooBarRec>("FooBarRecs");
    builder.EntitySet<Foo>("Foos");
    builder.EntitySet<Bar>("Bars");
    return builder.GetEdmModel();
}

Next, I create OData controllers to handle the OData URI query:

 // Controller
 public class FoosController : ODataController
 {
     public const int Num = 10;
     public static IList<Foo> foos = Enumerable.Range(0, Num).Select(i =>
            new Foo
            {
                FooId = 100 + i,
                FooName = "Foo #" + (100 + i)
            }).ToList();

     [EnableQuery]
     public IHttpActionResult Get()
     {
         return Ok(foos);
     }
 }

 public class BarsController : ODataController
 {
     public const int Num = 10;
     public static IList<Bar> bars = Enumerable.Range(0, Num).Select(i =>
            new Bar
            {
                BarId = 1000 + i,
                BarName = "Bar #" + (1000 + i)
            }).ToList();

    [EnableQuery]
    public IHttpActionResult Get()
    {
        return Ok(bars);
    }
 }

 public class FooBarRecsController : ODataController
 {
     public const int Num = 10;
     public static IList<FooBarRec> fooBarRecs = Enumerable.Range(0, Num).Select(i =>
             new FooBarRec
             {
                   Id = i,
                   Name = "ForBarRec #" + i
              }).ToList();

     static FooBarRecsController()
     {
        for(int i = 0; i < Num; i++)
        {
            fooBarRecs[i].FooRec = FoosController.foos[i];
            fooBarRecs[i].BarRec = BarsController.bars[i];
        }
     }

     [EnableQuery]
     public IHttpActionResult Get()
     {
         return Ok(fooBarRecs);
     }
 }

Note: In FooBarRec, I create two properties (FooRec and BarRec) and use them to build a relationship between FooBarRec, Foo and Bar.

Now, we can provide any data what the EndUser want.

For example, The EndUser can use the following two URIs to query the single data:

~/odata/Foos
~/odata/Bars

Or, He can use the following URI to query the Union data:
- ~/odata/FooBarRecs?$expand=FooRec,BarRec

That's all.

OData over a "View" of unioned data in Entity Framework

2 Answers

Note: In FooBarRec, I create two properties (FooRec and BarRec) and use them to build a relationship between FooBarRec, Foo and Bar.