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

c# entity-framework entity-framework-6 odata

Question

I'm working on an OData service that exposes an Entity Framework 6 model. These entities are rather huge to load and are read-only. (A number of gigabytes)

The most of this is working good, but I have a new requirement that needs to expose an entity that is actually a union of numerous other entities for the convenience of our EndUser / customer. I'm using Microsoft.OData.EntityFrameworkProvider to accomplish this (I'm not currently utilizing WebAPI).

Context Snippet for EF

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

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

What is the best method for achieving this with OData, I wonder?

Due to the potential size of the data and the time required to load that data, which is done every night, I'd like to avoid putting duplicate data into a third intermediate table. That doesn't seem to work because it appears that QueryInterceptor only allows you to subfilter data that has already been queried.

I made some ridiculous attempts to break the cycle, such as extending DbSet and creating my own DbViewSet that accepts both sets in its constructor.

What is the best method to use OData and EF6 to accomplish something similar to a view?

Thanks!

1
3
3/18/2014 4:18:52 PM

Popular Answer

I develop a Web API and OData sample. It seems quite straightforward and can satisfy your needs:

In order to map your types, I first define the following CLR classes so that you don't need to construct any views:

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; }
}

I then build an OData EdmModel using the CLR types mentioned above:

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

To handle the OData URI, I then develop OData controllers:

 // 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: To create a link between FooBarRec, Foo, and Bar, I create two properties (FooRec and BarRec) and use them.

We can now give the end user any data they request.

For instance, the EndUser can query the single data using the following two URIs:

  1. ~/odata/Foos
  2. ~/odata/Bars

    Alternatively, one might query the Union data using the URI listed below:

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

Just that.

2
3/25/2014 7:31:50 AM






Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow