ADO.NET metadata/feature/capability discovery API

[roji]

There is a need for providers to expose capabilities/features/metadata to ADO.NET API consumers (or upper layers such as Dapper/Entity Framework). As an example, the introduction of a new batching API is now under discussion (#35135), and there's a need for providers to inform users whether the API has been implemented or not.

ADO.NET does have a database schema and metadata API, which contains the DataSourceInformation, providing something like this. It is problematic for the following reasons:

• It is a DataTable-based API. This makes it both weakly-typed (promoting inconsistency across providers) and clunky to work with.
• The API lacks some basic information and hasn't been updated in a long time
• The API seems to have been conceived mainly for the Visual Studio data UI components (DDEX) and addresses primarily those needs.

Other comments:

• There's the distinction of metadata being about a specific database or not - there seems to be a need for both cases. For the former an open connection would be needed, but not for the latter.
• JDBC has a structured, strongly-types metadata API, see their docs.
• If/when done, this feature should be coupled with an ADO.NET compliance test suite (Incorrect and unexpected optimizer behavior for Interlocked.Exchange with short value #7810), to ensure that metadata corresponds to actual provider behavior.

This is somewhat related to #19324, which is more about getting the database schema (rather than metadata/capabilities).

Note that we don't necessarily intend to work on this right away. In the meantime it should be possible to augment the older ADO.NET metadata DataTable-based API.

[NinoFloris]

There is a need for this in Dapper as well.

• Right now it does type name checks to figure out the database brand. It's a hack around the lack of performant introspection options and it breaks when you use both Dapper and Miniprofiler together on Postgres. Npgsql.PostgresException: 42809: op ANY/ALL (array) requires array on right side DapperLib/Dapper#1153
• After looking long and hard, there is just no good zero allocation option to getting any of this information as of today.

As to the actual design, if I were to design something like this I would take some good inspiration from ASP.NET Core's FeatureCollection type, something like it could very well be a good fit.

• It's a type indexed dictionary that most know from HttpContext.Features and it's a very good halfway option between something like DataTable — which is entirely untyped but endlessly extensible — and full rigidity found in the Java metadata apis where each new thing would have to go through api review and a BCL release.

• The dictionary supplies customization and extensibility options and as the return types are strongly typed there's good discoverability from that point on.

• For discoverability of the actual feature types, ASP.NET Core, and many community libraries, come with extension methods on HttpContext like GetEndpoint which returns the matched enpoint if you're using endpoint routing. All it does is delegate this work to IEndpointFeature via a call like context.Features.Get<IEndpointFeature>()?.Endpoint;

This could work very well for the growing landscape of fundamentally different database drivers. At least it won't require anybody to try (and fail) to predict all current and future use-cases while speccing the api.