user2501749
user2501749

Reputation: 33

Expose the SQL Sever tables and its data as oData

Is there a tool or framework to expose SQL Server tables and its data of as oData. Consider that tables are generated dynamically so using OR Mapper Entity Framework is not an option.

We need a mechanism to expose data as OData without generating C# classes.

Upvotes: 2

Views: 2214

Answers (1)

Murray Foxcroft
Murray Foxcroft

Reputation: 13765

There are a number of options here.

From a coding perspective, you can build something generic. .Net (C#) wraps OData support around the IQueryable interface and the [EnableQuery] attribute. The Pseudo code below demonstrates how you can do this generically with WebAPI2. A working demo can be stood up in minutes:

    [EnableQuery(PageSize = 100)]
    public IQueryable Get()
    {
        var data = (IQueryable)<get any data from the DB as IQueryable>;
        return Okay(data, data.GetType());
    }

Keep in mind that the filtering etc can end up being performed in memory, so trying to push as much of the filtering back to the database will give better performance. I have mainly used this with strongly typed objects and Entity Framework pushes all the filtering to the DB - very powerful and very quick. Keep in mind that OData is very flexible and you need to optimise your database indexes and queries for your common use cases.

From a Database perspective, if you are running in Azure, you have OData a few clicks away. See this article. Further Azure Table Storage's raw format is OData from the get go. Beware there may be limitations, for example, I think OData results from SQL Azure are paged to 50 rows to avoid denial of service type scenarios that thrash your database, especially for OData queries over non indexed data.

If your SQL is on premise, I don think there is anything out of the box, however there are a number of vendors that offer connectors. Here is an example from a quick Google. I have no affiliation with them.

Upvotes: 1

Related Questions