locked
OData service on legacy database RRS feed

  • Question

  • User1673857584 posted

    Hi,

    What is the best option to build an OData service on a legacy database? 

    All the tutorials are creating a database from scratch to match the entity framework model. 

    How does it work with an existing database that does not match the service model being exposed?

    For example, I have these 2 db tables:

    Science_Data { country_id varchar(3), year int, fig decimal }

    Arts_Data { country_id varchar(3), year int, fig decimal }

    and I want to expose it using this path /Category('Science')/Data and /Category('Arts')/Data (The ids are just for the example).

    I was able to achieve some success by creating a service model that contains Category and Data entities and creating a controller:

    public class CategoryController : ODataController
    {
            [EnableQuery]
            [ODataRoute("/Category('{id}')/Data")]
            public IQueryable<Sector> Get([FromODataUri]string id)
            {
                if (id == "Science")
                { 
                    return db.Science_Data.Select(i => new Data() { CountryId = i.country_id, Year = year, Figure = fig });
                }
                else if(id == "Arts")
                {
                    return db.Args_Data.Select(i => new Data() { CountryId = i.country_id, Year = year, Figure = fig });
                }
            }
    }

    This works to a certain extents and supports query options and the filtering will happen at the database level. 

    But I'm having problems supporting $expand options so I'm wondering if my approach is correct to begin with.

    Thanks

    Thursday, January 29, 2015 12:32 PM

All replies

  • User-231660372 posted

    If my understanding of odata is correct, it doesn't really matter what your database is or their names are, it's all about the EntitySet in your configuration. 

    You expose an entity, which doesn't have to be the same as your domain model; however, I heard you, most of the online samples, they are the same. You just need an additional layer to translate them, so in the middle, you will need a service layer to perform some business logic. 

    In that case, you can set your ODataRoute to inject whatever you like and use your business logic to translate to query condition. 

    However, if you want to apply $expand or $filter, I am not sure. First, I think those criteria can only be applied to the first layer, i.e. parent entity, not children. Second, if you need to translation, how does oData know what to do.... I really don't know, sorry I can't help. I guess I just rephrase your questions.... 

    Thursday, January 29, 2015 8:28 PM
  • User1673857584 posted

    In principle, I agree with you. However if you need to support query options ($filter, $orderby, $select, etc) then you need to use entity framework to generate the sql when querying the database. Otherwise you will have to translate the URL to SQL yourself which is big effort.

    Friday, January 30, 2015 8:53 AM
  • User2052860206 posted

    this problem doesn't have a solution yet? 

    Saturday, May 2, 2015 12:26 PM