3

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!

Kent
  • 201
  • 2
  • 8
  • Well, if both sets returned the same POCO ... – Jodrell Mar 18 '14 at 16:22
  • 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: http://stackoverflow.com/questions/5325797/the-entity-cannot-be-constructed-in-a-linq-to-entities-query 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 Mar 18 '14 at 16:41
  • 1
    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 Mar 18 '14 at 16:45
  • I see... My concern is if I do it in TSQL, then wouldn't any filtering done via OData only occur after pulling back the entire result set? This could cause huge performance issues. – Kent Mar 18 '14 at 16:52
  • 1
    if the union is performed by a view EF can still apply extra conditions as and when ODataController specifies. – Jodrell Mar 18 '14 at 17:25
  • You're right, I was misunderstanding you initially. I didn't realize you meant to explicitly create a VIEW in TSQL rather than just the union in TSQL. Thank you! You should post it as an answer to the question so I can credit you. – Kent Mar 18 '14 at 17:45
  • Running into an additional problem here, but I do feel like I'm getting closer. When the View is created EF is unable to map that Entity , It requires WITH SCHEMABINDING to be able to map the entity Key. Unfortunately since this view is a union, I'm unable to do that since of course you can't create a clustered index on a view containing a Union. – Kent Mar 18 '14 at 20:41
  • `WITH SCHEMABINDING` is required but it doesn't require an indexed view, and should work with UNION, for example http://sqlfiddle.com/#!3/9a89e/1 – Jodrell Mar 19 '14 at 09:40
  • 1
    You can't mix the Entity Frawmework Data Provider and an other provider in the same service. Read [WCF, OData, DbContext, and Joins](http://stackoverflow.com/questions/21804722/wcf-odata-dbcontext-and-joins/) – nlips Mar 19 '14 at 12:40
  • nlips: That's interesting, I perhaps could have gone that route and it may have been preferable. Currently I have it working w/ the advice Jodrell gave, but thank you for your input. – Kent Mar 19 '14 at 16:45

2 Answers2

2

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:

  1. ~/odata/Foos

  2. ~/odata/Bars

    Or, He can use the following URI to query the Union data:

    • ~/odata/FooBarRecs?$expand=FooRec,BarRec

That's all.

Sam Xu
  • 2,963
  • 1
  • 10
  • 15
1

As Jodrell stated in the comments.

The only approach to make this work is to make a DbSet to represent a SQL View.

The only way I could get Entity Framework to handle the model binding appropriately was to drop the table after it was created and create then create the view.

Because it was a unioned view I had to handle this after the model was done initializing because otherwise it will try to force a clustered index on a view which is not possible if the view contains a union.

Kent
  • 201
  • 2
  • 8