Exception when navingating to one of the odata uris

[apedzko]

Hi!

I am trying to use the source code of your project in a new web api application. My web api starts fine, but when i navigate to one of the uris e.g. http://localhost/odata/db/activity i get the following exception:

Could you please assist?

System.InvalidOperationException occurred
HResult=0x80131509
Message=The container built by the container builder must not be null.
Source=System.Web.OData
StackTrace:
at System.Web.OData.Extensions.HttpConfigurationExtensions.GetODataRootContainer(HttpConfiguration configuration, String routeName)
at System.Web.OData.Extensions.HttpRequestMessageExtensions.GetRootContainer(HttpRequestMessage request, String routeName)
at System.Web.OData.Extensions.HttpRequestMessageExtensions.CreateRequestScope(HttpRequestMessage request, String routeName)
at System.Web.OData.Extensions.HttpRequestMessageExtensions.CreateRequestContainer(HttpRequestMessage request, String routeName)
at maskx.OData.DynamicODataPathRouteConstraint.Match(HttpRequestMessage request, IHttpRoute route, String parameterName, IDictionary`2 values, HttpRouteDirection routeDirection) in C:\Users<user>\Documents\Visual Studio 2017\Projects\odata.sqlserver\maskx.OData\DynamicODataPathRouteConstraint.cs:line 91
at System.Web.Http.Routing.HttpRoute.ProcessConstraint(HttpRequestMessage request, Object constraint, String parameterName, HttpRouteValueDictionary values, HttpRouteDirection routeDirection)
at System.Web.Http.Routing.HttpRoute.ProcessConstraints(HttpRequestMessage request, HttpRouteValueDictionary values, HttpRouteDirection routeDirection)
at System.Web.Http.Routing.HttpRoute.GetRouteData(String virtualPathRoot, HttpRequestMessage request)
at System.Web.Http.WebHost.Routing.HttpWebRoute.GetRouteData(HttpContextBase httpContext)

[maskx]

Hi @apedzko ,

you can set a breakpoint at SQLDataSource's BuildEdmModel method to see the EdmModel is builded success or fail.

and there have 3 branch, which codebase are you used?

The master branch have some problem and cannot run at this time. because new version Microsoft.AspNet.OData has a break change.

The code at dev branch can compile and run, this branch use a old version Microsoft.AspNet.OData. the assembly publish to nuget.com is come from this branch.

The code at Dev branch is .net core version, this branch use Microsoft.AspNetCore.OData. at this time Microsoft.AspNetCore.OData is in beta, lack of some functions such as batch support.

[apedzko]

Hi! I was referring to the issue in the Master branch. Now i switched to the Dev branch and no longer see the issue.

There is another question, however:

Is there a correct way to register 2 databases? I see there is no more database name in the url.
When i am doing the following thing i get exception that dependency injection cannot be enabled 2 times:

routeBuilder.MapDynamicODataServiceRoute("odata1", "db1",
new maskx.OData.Sql.SQL2012("db1", "..."));

routeBuilder.MapDynamicODataServiceRoute("odata2", "db2",
new maskx.OData.Sql.SQL2012("db2", "..."));

[maskx]

This is a bug, Thx.

I have check in the fixed code to Dev branch, pls get the lasted code.

[apedzko]

Hi! Thanks for your quick response.

I'm not sure this is the best place, but i have a couple of questions/considerations and would like to know your opinion on them:

1. I am planning to implement a multi-tenant OData service where a user needs to query a different database depending on the tenant id which is dynamically extracted from his user account.
   It looks like in the Dev branch you no longer support database name in the url. Could you please advise on how could i get it back?

2. It looks like your library is case-sensitive to the table names in the URIs. E.g. in my case http://server/odata/Customers works but http://server/odata/customers does not. It is not common for the web, thus i would suggest to adjust the library to ignore the casing.

3. There might be databases where tables are residing in different schemas e.g. dbo.Customers, staging.Customers etc. Your library does not currently support multiple schemas.

[maskx]

1. routeBuilder.MapDynamicODataServiceRoute("odata1", "db1",new maskx.OData.Sql.SQL2012("db1", "..."));

routeBuilder.MapDynamicODataServiceRoute("odata2", "db2",new maskx.OData.Sql.SQL2012("db2", "..."));

"db1" and "db2" can be your database name

the query url is: http://xxxx/< db1 or db2 >/< your table name>

2. case-sensitive is designed by Microsoft.AspNetCore.OData. There should be some research work for supporting case-insensitive

3. This should be supported

[apedzko]

Thanks for your reply.

1. I'm referring to the case when i have a number e.g. 100+ databases that can be created/deleted independently from the api. The approach with routes allows you to register them only at startup, but not during the lifetime of application.

2. As far as i could see your library shows the table from different schemas in the list when navigating to the root url of the database, however when navigating to the table url itself (not from the dbo schema) the sql command fails to find the table.

[maskx]

I had checked in to Dev branch, add schema support and and an LowerName configuration, when LowerName is true, all name in url will be lowercase

for schema support, please reference https://github.com/maskx/OData/tree/Dev#schema

as your case, I think you can do like this:

 public void Configure(IApplicationBuilder app)
        {
            app.UseMvc(routeBuilder =>
            {
                var sourceA = new maskx.OData.Sql.SQL2012("odata", "Data Source=.;Initial Catalog=Group;User ID=UserA;Password=***********");
                sourceA.Configuration.DefaultSchema = "SchemaA";
                sourceA.Configuration.LowerName = true;
                routeBuilder.MapDynamicODataServiceRoute("odata1", "db1/SchemaA", sourceA);
            });
            app.UseMvc(routeBuilder =>
            {
                var sourceB = new maskx.OData.Sql.SQL2012("odata", "Data Source=.;Initial Catalog=Group;User ID=UserB;Password=***********");
                sourceB.Configuration.DefaultSchema = "SchemaB";
                sourceB.Configuration.LowerName = true;
                routeBuilder.MapDynamicODataServiceRoute("odata2", "db1/SchemaB", sourceB);
            });
        }

[maskx]

SQL init scripts also updated, all script files is at: https://github.com/maskx/OData/tree/Dev/maskx.OData/maskx.OData/MSSQL/initialScript/v2012

[apedzko]

Good news, thank you! I would also recommend utilizing DatabaseSchemaReader package for reading metadata from the database - it would allow you to get rid of stored procedures.

[maskx]

DatabaseSchemaReader is excellent, it can read many database' metadata, this is what I need support next step.

why I choice using stored procedures is for flexibility. most time, expose entire database by webapi is not a good choice, we need filter by black/white list, developer can modify stored procedures by join a black/white list table in database simply, and this is configurable.

DatabaseSchemaReader get stored procedure's result set schema by executing the stored procedure in a transaction, then rollback after got the result. this will increase the webapi initialize time and may not be acceptable in some scenarios.

May be, I should modify the design of DataSource to support user to choose the way to build the EmdModel

[ravensorb]

What about allowing for a config extension method that accepted an array with * support for Tables/Views/Stored Procedures?

sourceA.EnableTable("*);

or

sourceA.EnableTable("prefix*");

Default behavior could be "*" and if any items are added to the Table/View/Stored Proc list, then only those items in the list are exposed.

[maskx]

you can filter Table/View and SP exposed by modify the StoredProcedure: GetEdmModelInfo and GetEdmSPInfo
for example:
select
c.TABLE_NAME
,c.COLUMN_NAME
,c.DATA_TYPE
,c.IS_NULLABLE
,k.COLUMN_NAME as KEY_COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS as c
inner join TableWhiteList as w on c.TABLE_NAME=w.TABLE_NAME
left Join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as k on
OBJECTPROPERTY(OBJECT_ID(k.CONSTRAINT_NAME), 'IsPrimaryKey')=1
and k.COLUMN_NAME=c.COLUMN_NAME
and k.TABLE_NAME=c.TABLE_NAME
order by c.TABLE_NAME

[ravensorb]

Yep, I noticed that -- this was more of an idea for enhancement to move things out of the db and into code config :)

[maskx]

good idea, code config is next step in my plan, I'm doing something in Dev-core branch