returning entity formed from two different databases from within one odata web api endpoint

Question

I have 2 databases needed to support a single web api. I want to return a new "entity" that reflects entities from both databases. I call this entity a "view" even though it has no relationship to a view in a database and the data comes from the 2 different databases. The data would be used to power a UI page.

For another similar problem that needed to return a new shape of data from a single database, I setup a new NotMapped entity call PeopleHomeView and added a single Find function to a controller so that when I access /PeopleHomeViews/NS.Find(id='...') I return the entity that has a custom shape. The entity only has primitive data values, no ref types. This all worked fine.

However, I need to return an entity that combines data from the 2 databases, which is a different problem.

Setup 2 DbContexts, one for each database. Both are injected into the controller.
Defined a .net class with the properties I wanted in the returned data. The properties had objects that reflect the two different database entity models. So in PersonEntityView I have a Person object and a Rating object. Person and Rating come from different databases.
Created a control PersonEntityViewsController and added builder.EntitySet<PersonEntityView>("PersonEntityViews") to the EDM model builder. I do not have any other EDM builder code.
Added a Get([FromODataUri] Guid key) method to the PersonEntityViewsController controller. The key passed into the Get is the Person key. I then use linq to manually build the data I need to return.
```
public PersonEntityView Get(
       [FromODataUri] Guid key)
{
    ...controller code to build up PersonEntityView...
    var x = new PersonEntityView();
    ...
    return x;
}
```

Now when I access /PeopleEntityViews(..guid of person...), build the return entity and return it from Get, I only get the primitive data values in the .net class and even though I manually build up the entity contents e.g. add Person and Rating. They are not serialized back out to the client. The $metadata shows the navigation properties correctly. Using expands, e.g. $expand=Person, does not work.

I've see some other SO posts on this but they were not relevant to the problem. For example, returning a DTO is described in the docs, but not from two different databases.

I don't want to have my client ask several times to load mountains of reference data and other data needed for a page. I'd like to get most of it in one query but still use the odata backend.

Thoughts on how to do this? It seem that odata can be sensitive to the return type from controllers so perhaps I need something special in my return type or a I'm missing an annotation.

user1763729 user1763729 · Accepted Answer · 2018-10-25T14:02:21

It looks like the odata formatters require a fairly strict set of return types to be activated correctly. Not quite sure the rules so I'll have to read the odata source code more carefully. To get this to work I had to return an IQueryable from Get so I did:

public IQueryable<PersonEntityView> Get([FromOdataUri] Guid key) {
  ...
  (new List<PersonEntityView>{v}.AsQueryable();
}

The signature does not make alot off sense to me as a Get for a key-based get should just return a single entity and hence not need to have a IQueryable. The returned value is a member of an array under the "value" property. If you only use IQueryable without the type, it returns a json object but lacks the full odata response format. I needed to still expand the property via ?$expand=ThePropertyToExpand otherwise the expansion did not take place.

I'll keep playing with it but this seems to the best way to return non-database backed (hence not mapped in entityframework) entities that are highly customized to a specific UI view's needs and are not based only on a single object root.

You could also drive this off of functions/actions say, using a bound entity, which I describe here. However, in some cases the value may not be neatly related to a single root entity.

returning entity formed from two different databases from within one odata web api endpoint

1 Answers