Skip to content

Using 'System.Data.OleDb' on 'net472' throws 'TypeLoadException' #83

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
lauxjpn opened this issue Dec 2, 2020 · 33 comments · Fixed by #84 or #88
Closed

Using 'System.Data.OleDb' on 'net472' throws 'TypeLoadException' #83

lauxjpn opened this issue Dec 2, 2020 · 33 comments · Fixed by #84 or #88
Assignees
@lauxjpn @ChrisJollyAU
Labels
bug
Milestone
3.1.0-alpha.4

Comments

@lauxjpn
Copy link
Member

lauxjpn commented Dec 2, 2020

@lauxjpn: I am not sure if the implementation is buggy or if I do something wrong, but when trying to load my database via oledb the version I provide with nuget gets ignored and I get the TypeLoadException from JetFactory. The call of Type.GetType(...) seems to be problematic, if I load the System.Data.OleDb via Assembly.Load(..) the correct one gets loaded, but Type.GetType(...) uses the default for my target framework (in this case net472). Should I open up a bug or is this my fault?

Originally posted by @xoniuqe in #34 (comment)
@lauxjpn
Copy link
Member Author

lauxjpn commented Dec 2, 2020

@xoniuqe How are you referencing OleDb in the project file?
Is it like <PackageReference Include="System.Data.OleDb" Version="5.0.0" />

Originally posted by @ChrisJollyAU in #34 (comment)

@lauxjpn
Copy link
Member Author

lauxjpn commented Dec 2, 2020

@ChrisJollyAU Yes I did. I will upload a demo project on my github. https://github.com/xoniuqe/Ef-Core-Jet-TypeLoadException

Originally posted by @xoniuqe in #34 (comment)

@lauxjpn
Copy link
Member Author

lauxjpn commented Dec 2, 2020

@xoniuqe Do you specifically need the full .net framework? Changing your demo to use .net core 3.1 is a possible solution

Originally posted by @ChrisJollyAU in #34 (comment)

@lauxjpn
Copy link
Member Author

lauxjpn commented Dec 2, 2020

@ChrisJollyAU I have dependencies to .net Framework assemblies which are not usable in .net core 3.1, so yes.

Originally posted by @xoniuqe in #34 (comment)

@lauxjpn
Copy link
Member Author

lauxjpn commented Dec 2, 2020

@xoniuqe I tried adding a binding redirect but didnt work. I can see in the debug as it loads, it loads the correct version but at runtime the .net framework version keeps on getting picked.

Should probably open a issue specific for this

Originally posted by @ChrisJollyAU in #34 (comment)

@lauxjpn
Copy link
Member Author

lauxjpn commented Dec 2, 2020

image

Here's the problem. The netstandard version includes all the OleDb classes whereas the .net framework version doesn't. It in turn references the normal System.Data

@lauxjpn The OleDb bug that was in the net core version, do you know if that was only in net core? If so on the full framework you can ignore the version check. Tried without the version check and it goes further but I do get a COM error when it tries to create the database using DAO

Originally posted by @ChrisJollyAU in #34 (comment)

@lauxjpn lauxjpn mentioned this issue Dec 2, 2020
Closed
@lauxjpn lauxjpn self-assigned this Dec 2, 2020
@lauxjpn lauxjpn added this to the 3.1.0-alpha.4 milestone Dec 3, 2020
@ChrisJollyAU
Copy link
Member

@xoniuqe I tested my changes and it does work on your test project. It will just use the OleDb classes from the framework's System.Data rather than the nuget package. Also I would recommend changing your connection string to use ACE rather than Jet. Jet is quite old and only supports database formats up to around access 2003. Creating a new database will use DAO which will use the current access runtime installed. If you are using e.g the 2010 or 2016 runtime, it will be created in that format which is not supported when using the Jet connection string.

Should only need to change the Provider to Microsoft.ACE.OLEDB.12.0

Have it working in both x86 and x64

@xoniuqe
Copy link
Contributor

xoniuqe commented Dec 3, 2020

@ChrisJollyAU Thank you for the fast work, unfortunalety I think I have to stick to the Jet driver because we currently have the requirement to work with the old database format. But I give it a try and will give you an update. But this has to wait until monday.

@lauxjpn lauxjpn mentioned this issue Dec 3, 2020
Merged
@lauxjpn
Copy link
Member Author

lauxjpn commented Dec 3, 2020

Okay, the issue is fixed with #84.

I had no serious issue with the test project that @xoniuqe provided when using #84, but only a minor one regarding the double quotes surrounding the Data Source connection string option value, that will lead to a failing path check later on when executing our custom CREATE DATABASE command.
I will open another PR for that fix.

@ChrisJollyAU
Copy link
Member

@xoniuqe Jet should still work. if you already have a database

@ChrisJollyAU
Copy link
Member

@lauxjpn Is it really necessary to do the version check like that versus adding it as a nuget dependency requirement?

@lauxjpn
Copy link
Member Author

lauxjpn commented Dec 3, 2020 •
edited
Loading

@lauxjpn Is it really necessary to do the version check like that versus adding it as a nuget dependency requirement?

@ChrisJollyAU We have the nuget dependency requirement in EntityFrameworkCore.Jet.OleDb. However, people might not install it and just use UseJet() instead of UseJetOleDb() with only referencing EntityFrameworkCore.Jet.

We could make it mandatory for people to install either EntityFrameworkCore.Jet.OleDb or EntityFrameworkCore.Jet.Odbc , and then runtime check that people have indeed added the package.

What we don't want is to add System.Data.OleDb and System.Data.Odbc directly as dependencies to EntityFrameworkCore.Jet, because most people will only use one of them anyway and should not have to depend on the one they don't use.

@ChrisJollyAU
Copy link
Member

@lauxjpn What about removing the UseJet() from EFCore.Jet. Then you only have UseJetOleDb and UseJetOdbc. Then you have to install one of those packages to be able to use it

@lauxjpn
Copy link
Member Author

lauxjpn commented Dec 3, 2020 •
edited
Loading

What about removing the UseJet() from EFCore.Jet. Then you only have UseJetOleDb and UseJetOdbc. Then you have to install one of those packages to be able to use it

We currently have a mechanism that just infers the data access library needed from the connection string used. So if I currently specify DBQ=MyDatabase.accdb then we will use ODBC, and if I specify Data Source=MyDatabase.accdb we will use OLE DB.

There is also the possibility to just specify the filename and nothing else, which will use the default data access provider (currently ODBC), or to specify just the file name and an enum value, that states what data access provider to use.

Up until 3.1, people have been using the UseJet() extension method.

We could get rid of the the inference feature if it is not needed (and adjust our testing system). People would than have to write their own logic to switch between ODBC and OLE DB.

We could also probably change the filename-only feature to check which library, EntityFrameworkCore.Jet.OleDb or EntityFrameworkCore.Jet.Odbc, has been referenced and then just implicitly use that one (or use ODBC if both have been referenced).

If we want to get rid of the UseJet() extension method, than we would need to mark those as obsolete with error, with a message pointing upgrading users in the right direction to reference the appropriate packages and using the proper data access provider specific extension methods instead.

@ChrisJollyAU
Copy link
Member

You actually have some internal usage of UseJet within EFCore.Jet. Specifically within JetRelationalConnection.cs and JetCodeGenerator.cs . And of course they have their own dependencies. In total around 10 files to be moved out out of EFCore.Jet and into both of EFCore.Jet.OleDb and EFCore.Jet.Odbc

@lauxjpn
Copy link
Member Author

lauxjpn commented Dec 3, 2020 •
edited
Loading

@ChrisJollyAU Yes, if we would go forward with this proposal, we would need to adjust the code generation for the UseJet() call as well.

As for dependent files on UseJet(), I would just keep but rename the current extension method implementations and make them internal. Therefore, nothing really changes internally but users cannot call them anymore. We would keep the methods with the signature of the 2.2.0 release around and mark them as obsolete with error and throw.

While it is generally a good idea to move the ODBC and OLE DB dependent code into their own packages (see #45 (comment)), I don't think we should touch it for 3.1 anymore, because of the still not great unit test situation and the fact that the current implementation does work.
Refactoring the code like that should be considered later for 5.0.

@xoniuqe
Copy link
Contributor

xoniuqe commented Dec 9, 2020

Okay, the issue is fixed with #84.

I had no serious issue with the test project that @xoniuqe provided when using #84, but only a minor one regarding the double quotes surrounding the Data Source connection string option value, that will lead to a failing path check later on when executing our custom CREATE DATABASE command.
I will open another PR for that fix.

I could use this strings on the 2.2.X version, is this a newly introduced bug?

@lauxjpn
Copy link
Member Author

lauxjpn commented Dec 9, 2020

@xoniuqe Yes. 3.x is nearly a complete rewrite and now also allows the usage of ODBC (previously only OLE DB).

As a quick workaround, do not use explicit double quotes around your path in the connection string.

@xoniuqe
Copy link
Contributor

xoniuqe commented Dec 9, 2020

@lauxjpn: This worked kind of. I can load the mdb file successfully with the removed quotes, but when I try to migrate I get an OleDBException "Unrecognized database format error"(may not be the exact wording). If I change the provider to Microsoft.ACE.OLEDB.12.0 this works but the migration does not create an database password. is this the same issue as you mentioned before (concerning an upcoming(?) PR)?

@ChrisJollyAU
Copy link
Member

@xoniuqe Not sure about the database password. However I did mention something about that unrecognized database format error earlier in this issue.

In summary the new file is created using DAO from the currently installed access engine (e.g. in the test suite for this project it uses the access 2010 engine). However when trying to read it using OleDb, you are specifying the Jet OleDb driver which is too old to understand the 2010 format. The ACE OleDb driver can understand that format.

As an aside the wikipedia article has a nice table of the drivers and which formats each support. https://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine

@lauxjpn lauxjpn mentioned this issue Dec 9, 2020
Merged
@lauxjpn
Copy link
Member Author

lauxjpn commented Dec 9, 2020

@xoniuqe The connection string quoting issue is fixed with #88.

@lauxjpn
Copy link
Member Author

lauxjpn commented Dec 9, 2020 •
edited
Loading

when I try to migrate I get an OleDBException "Unrecognized database format error"(may not be the exact wording)

In summary the new file is created using DAO from the currently installed access engine (e.g. in the test suite for this project it uses the access 2010 engine). However when trying to read it using OleDb, you are specifying the Jet OleDb driver which is too old to understand the 2010 format. The ACE OleDb driver can understand that format.

We should fix this. We should provide an option to specify the database version that should be used.
There is already support to specify the DatabaseVersion for database creation operations in EFCore.Jet.Data, so this should not be that hard.

I will push a PR for this.

@xoniuqe Regarding the password:

Your sample code uses the Database.EnsureCreated() method, which generates a EFCore.Jet specific CREATE DATABASE statement. This statement currently does not support specifying a password, a collating order or a specific database version.

However, you can just call JetConnection.CreateDatabase() instead:

JetConnection.CreateDatabase("test.mdb", databasePassword: "PASSWORD");

Or together with EnsureDeleted() and EnsureCreated():

context.Database.EnsureDeleted();
JetConnection.CreateDatabase("test.mdb", databasePassword: "PASSWORD");
context.Database.EnsureCreated();

@lauxjpn lauxjpn mentioned this issue Dec 9, 2020
Merged
@lauxjpn
Copy link
Member Author

lauxjpn commented Dec 9, 2020

@xoniuqe The System.Data.OleDb.OleDbException (0x80004005): Unrecognized database format 'test.mdb'. issue is fixed with #90.

@lauxjpn
Copy link
Member Author

lauxjpn commented Dec 10, 2020 •
edited
Loading

If I change the provider to Microsoft.ACE.OLEDB.12.0 this works but the migration does not create an database password.

Your sample code uses the Database.EnsureCreated() method, which generates a EFCore.Jet specific CREATE DATABASE statement. This statement currently does not support specifying a password [...]

@xoniuqe This issue is fixed with #91.

As far as I am aware, we should now have fixed all issues reported by you. So if I missed anything, please remind me again.

@xoniuqe
Copy link
Contributor

xoniuqe commented Dec 14, 2020 •
edited
Loading

@lauxjpn After a short test with my prototype everything seems to work as I would expect it. Thank you for your fast responses and the fixes. Great job! (@ChrisJollyAU thank you too, you were also helpful).

When I first was evaluating to use this provider for our ancient software I was a bit sceptical (at first the project seemed a bit inactive to me, to be honest) but I must admit im really impressed by the handling of my issues here 👍

@jeromv
Copy link

jeromv commented Feb 28, 2021

Hello,

I tried to get the prerelease package working in a net 5 project. Didn't work. Tried downgrading to 3.1 core, also didn't work
I either get typeload exceptions or version conflicts with nuget packages
Is there an example of a working test project?

@ChrisJollyAU
Copy link
Member

Hi @jeromv . Net 5 is not supported yet. It does require a fair number of changes to be built against it.

It should be working for .net core 3.1 . Can you post your project file and the exact error message you're getting

@jeromv
Copy link

jeromv commented Feb 28, 2021 via email

@ChrisJollyAU
Copy link
Member

ChrisJollyAU commented Feb 28, 2021 •
edited
Loading

You need the prerelease. The version you are using is 2.2.0 which is compatible with net core 2

Your project file is not included

If your project file uses packagereferences have this as a line in it

<PackageReference Include="EntityFrameworkCore.Jet" Version="3.1.0-alpha.4" />

@jeromv
Copy link

jeromv commented Feb 28, 2021 via email

@ChrisJollyAU
Copy link
Member

Ah, your version is wong for all those starting with Microsoft.EntityFrameworkCore . Being version 5 they are referencing the net 5 version of those packages

<PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.12" />

3.1.12 is the latest in the 3.1 series. If you have all those change to use that version and have

<PackageReference Include="EntityFrameworkCore.Jet" Version="3.1.0-alpha.4" /> for EFCore it should work

You may want to add either EntityFrameworkCore.Jet.OleDb or EntityFrameworkCore.Jet.Odbc depending on whether you prefer oledb or odbc style connection strings (and behind the scenes access)

Also, it would be best to downgrade the Microsoft.EntityFrameworkCore packages first before going to the correct EFCore version

@jeromv
Copy link

jeromv commented Feb 28, 2021 via email

@jeromv
Copy link

jeromv commented Feb 28, 2021 via email

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@lauxjpn lauxjpn

@ChrisJollyAU ChrisJollyAU

Labels
bug
Projects
None yet
Milestone
3.1.0-alpha.4
4 participants
@xoniuqe @jeromv @lauxjpn @ChrisJollyAU