Efcore: Experimental: ODBC-based providers

[rowanmiller]

Look at a base provider implementation that uses ODBC.

[divega]

Related: System.Data.Odbc Namespace https://github.com/dotnet/corefx/issues/13035

[colltoaction]

Will take a look at this

[colltoaction]

@ajcvickers please re-triage and update https://github.com/aspnet/EntityFramework/wiki/Roadmap#ef-core-20 accordingly. I ported ODBC (https://github.com/dotnet/corefx/pull/15646) but very little progress was made on this.

[ErikEJ]

@tinchou That is very interesting news - Are you able to share your ODBC port (a branch??)

[colltoaction]

@ErikEJ if you mean System.Data.Odbc namespace, it's been merged into dotnet/corefx. I linked to the PR in the previous comment, but here it is again: https://github.com/dotnet/corefx/pull/15646.

If you mean my work on creating an ODBC-based provider, I think I just got a couple of very basic select statements working. I was making it run on Oracle, but now I realize I should've started with SQLServer or Sqlite 😛.

Anyway, it's probably not worth sharing, even more considering that EFCore 2 has been changing and it most definitely won't compile by now, but let me know.

[ajcvickers]

We are not planning to do this on the EF team, but once System.Data.Odbc is available in .NET Core, then somebody else could make use of it to do something like this.

[ErikEJ]

@tinchou Based on the comment from @ajcvickers - are you able to share ? I would consider moving forward with coordinating an ODBC provider...

[colltoaction]

Here you go!

Tinchou.EntityFrameworkCore.Oracle.zip

Comments:

1. This is targeting the Full Framework since the API surface stayed the same and it is easier than dogfooding corefx. But if you want to do that, you'd need to follow these instructions since the ODBC package doesn't seem to exist in nuget or myget yet
2. This project was sitting next to and targeting the EF Core 2 source, but maybe you can point to a nightly feed instead
3. It says "Oracle" everywhere, but I hadn't reached the point of writing plsql code. Everything should be working with a sql-92 compatible DB with ODBC support - probably Sqlite is a good option since it matches the SQL from the Relational base provider very well.

[grahamehorner]

@ajcvickers is this a u-turn from what was said on live.asp.net community standup, my understanding was odbc providers would be supported by the ef core team but not in 2.0 timeframe?

[ErikEJ]

@tinchou Great, will you be OK with me hosting a "EntityFrameworkCore.ODBC" package? Can you recommend a SQLite ODBC provider? Sounds like SQL Server might be a better (easier) option?

[colltoaction]

@ErikEJ how about EntityFrameworkCore.Odbc? Anyway, keep in mind that you will have to use a specific SQL syntax. This means that a generic ODBC provider might not work with any specific database unless you override the methods that generate the SQL code.

The project I shared uses the Relational provider as a starting point, and so it will use whatever SQL it generates. It more closely resembles Sqlite's syntax as far as I know. For Sqlite, this driver worked fine in my tests: http://www.ch-werner.de/sqliteodbc/

[ErikEJ]

This means that a generic ODBC provider might not work with any specific database unless you override the methods that generate the SQL code.

If that is the case, I do not really see the point...

[colltoaction]

The main use case for ODBC is databases that don't have a managed implementation. It abstracts away all the connection management and communication specifics, and you only focus on building a string of SQL code.

If I was going to write an EF provider for example for Oracle, I would:

1. Get a well supported database such as Sqlite working on top of ODBC
2. Switch the connection string to point to an Oracle instance
3. Override the SQL generation methods to use Oracle's syntax

Step 1 is generic and is a non-trivial task, but the SQL generation is always database-specific.

[ajcvickers]

@ErikEJ This whole thing is a little confusing. 😸 The idea, as I understand it, is that there is currently a barrier to entry on writing an EF Core provider for .NET Core which is that you have to have an ADO.NET provider for .NET Core to use at the lower level. If you've got a native one, then you're good to go. But this is not always the case, especially given that .NET Core is pretty new. But if there is an ODBC ADO.NET provider, and if EF can work with this, then its possible to build an EF provider _for the database you are targeting_ but making use of the ODBC underneath. We were considering this approach for some well-known database engines, but with what we know now about the state of .NET Core ADO.NET providers we now see no need to take this approach at this time.

[ErikEJ]

Thanks for clearing that up guys - I completely misunderstood the whole undertaking... 😕 - I will let it be...

[grahamehorner]

@ajcvickers is/could there be any mileage on making public the POC work that was mentation only a few days ago on the live.asp.net as working? would really like to try and hook up this with the oracle odbc c++ provider for linux ;) I do understand why it's moved across into a corefx task give that database connectivity via odbc etc. is a layer below EF and developers may not event want EF in the mix ;)

[ajcvickers]

@grahamehorner I don't know what was specifically mentioned in the stand-up--I would follow up with whomever mentioned it. With regards to making the work we have done public, I believe it is all public already. @tinchou referenced the corefx work above and also provided a zip of the prototyping work in another comment. I don't think we have anything else.

[grahamehorner]

@ajcvickers see https://www.youtube.com/watch?v=8jJVrYXSJvo&list=PL0M0zPgJ3HSftTAAHttA3JQU4vOjXFquF&index=0 or live.asp.net episode titled EF Core Team Apr 11, 2017

[ajcvickers]

@grahamehorner The things @divega talked about at around the 18 minute mark are the things that are referenced above.

[divega]

I have very little to add to @ajcvickers' explanation at https://github.com/aspnet/EntityFramework/issues/7432#issuecomment-294915210. I think that covers exactly what this item was about. But just as an update:

• At this point the System.Data.Odbc package containing the code that @tinchou ported is unlikely to be included in the 2.0 release but should come shortly after. The main reason is that we are still need to take a few steps to produce the package and to include necessary native dependencies in the .NET Core test infrastructure, and this caught us as a point in which we are really trying to stabilize the builds and land the 2.0 release rather than focus on adding features to it.
• Once the package is available I think System.Data.Odbc still represents a great opportunity for the OSS community and customers to build solutions that leverage the ODBC drivers available across the various operating systems supported by .NET Core, in particular for those combinations in which a regular ADO.NET implementation isn't available yet. In other words, the fact that we closed this issue should not discourage others from trying this approach.