OleDbConnectionStringBuilder.TryGetValue() throws when scaffolding as x86 using a System.Data.OleDb version <= 4.7.1 #55

ChrisJollyAU · 2020-08-25T16:38:33Z

Using the 3.1-preview branch I was trying to scaffold a ms access .mdb database.

Using the -verbose option the log would go to

Finding design-time services for provider 'EntityFrameworkCore.Jet'...
Using design-time services from provider 'EntityFrameworkCore.Jet'.
Finding design-time services referenced by assembly 'Assembly'.
No referenced design-time services were found.
Finding IDesignTimeServices implementations in assembly 'Assembly'...
No design-time services were found.

and sit there for however long (15 - 20 at least) until I ended the running process.

In the JetConnection file, if I remove the ExpandDatabaseFilePath call and just use connectionString for the parameter it goes past that point and I get the
Found table with name: .... ,
Found column with table: ....,
Found index for column: ...

for all the tables, columns and indexes

lauxjpn · 2020-08-25T17:19:48Z

I completely reimplemented the schema retrieval and scaffolding code two weeks ago. I will push the PR today.
Please try again once the PR has been merged.

lauxjpn · 2020-08-25T20:18:40Z

@ChrisJollyAU The PR has now been merged into 3.1-preview. Please pull the latest bits, try your issues again and report back. If any issues are still present, we will investigate them further.

ChrisJollyAU · 2020-08-26T14:07:11Z

Tried with the latest bits. It's still freezing after No design-time services were found.

If I end the dotnet task (no matter how long I wait after it gets to that point), the error is shown as follows

No design-time services were found.
Fatal error. Internal CLR error. (0x80131506)
   at System.Runtime.InteropServices.Marshal.PtrToStructureHelper(IntPtr, System.Object, Boolean)
   at System.Data.OleDb.PropertyInfoSet.GetValues()
   at System.Data.OleDb.OleDbConnectionInternal.GetPropertyInfo(System.Guid[])
   at System.Data.OleDb.OleDbConnectionStringBuilder.GetProviderInfo(System.String)
   at System.Data.OleDb.OleDbConnectionStringBuilder.TryGetValue(System.String, System.Object ByRef)
   at System.Data.Jet.DbConnectionStringBuilderExtensions.GetUserId(System.Data.Common.DbConnectionStringBuilder, System.Nullable`1<System.Data.Jet.DataAccessProviderType>)
   at System.Data.Jet.DaoSchema..ctor(System.Data.Jet.JetConnection)
   at System.Data.Jet.DaoSchema..ctor(System.Data.Jet.JetConnection, Boolean)
   at System.Data.Jet.PreciseSchema..ctor(System.Data.Jet.JetConnection)
   at System.Data.Jet.SchemaProvider.CreateInstance(System.Data.Jet.SchemaProviderType, System.Data.Jet.JetConnection)
   at System.Data.Jet.JetStoreSchemaDefinition.JetInformationSchema.GetTables(System.Data.Jet.JetConnection)
   at System.Data.Jet.JetStoreSchemaDefinition.JetInformationSchema.GetDbDataReaderFromSimpleStatement(System.Data.Jet.JetCommand)
   at System.Data.Jet.JetStoreSchemaDefinition.JetInformationSchema.TryGetDataReaderFromInformationSchemaCommand(System.Data.Jet.JetCommand, System.Data.Common.DbDataReader ByRef)
   at System.Data.Jet.JetCommand.ExecuteDbDataReaderCore(System.Data.CommandBehavior)
   at System.Data.Jet.JetCommand.ExecuteDbDataReader(System.Data.CommandBehavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at EntityFrameworkCore.Jet.Scaffolding.Internal.JetDatabaseModelFactory.GetTables(System.Data.Common.DbConnection, System.Func`3<System.String,System.String,Boolean>)
   at EntityFrameworkCore.Jet.Scaffolding.Internal.JetDatabaseModelFactory.Create(System.Data.Common.DbConnection, Microsoft.EntityFrameworkCore.Scaffolding.DatabaseModelFactoryOptions)
   at EntityFrameworkCore.Jet.Scaffolding.Internal.JetDatabaseModelFactory.Create(System.String, Microsoft.EntityFrameworkCore.Scaffolding.DatabaseModelFactoryOptions)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ReverseEngineerScaffolder.ScaffoldModel(System.String, Microsoft.EntityFrameworkCore.Scaffolding.DatabaseModelFactoryOptions, Microsoft.EntityFrameworkCore.Scaffolding.ModelReverseEngineerOptions, Microsoft.EntityFrameworkCore.Scaffolding.ModelCodeGenerationOptions)
   at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldContext(System.String, System.String, System.String, System.String, System.String, System.Collections.Generic.IEnumerable`1<System.String>, System.Collections.Generic.IEnumerable`1<System.String>, Boolean, Boolean, Boolean)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl(System.String, System.String, System.String, System.String, System.String, System.Collections.Generic.IEnumerable`1<System.String>, System.Collections.Generic.IEnumerable`1<System.String>, Boolean, Boolean, Boolean)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor+ScaffoldContext+<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor+OperationBase+<>c__DisplayClass3_0`1[[System.__Canon, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]].<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor+OperationBase.Execute(System.Action)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor+OperationBase.Execute[[System.__Canon, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]](System.Func`1<System.__Canon>)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor+ScaffoldContext..ctor(Microsoft.EntityFrameworkCore.Design.OperationExecutor, Microsoft.EntityFrameworkCore.Design.IOperationResultHandler, System.Collections.IDictionary)
   at System.RuntimeMethodHandle.InvokeMethod(System.Object, System.Object[], System.Signature, Boolean, Boolean)

It appears to be in DaoSchema.

A quick hack in the constructor

var dataSource = csb.GetDataSource();
                var userId = "admin"; ;// csb.GetUserId();
                var password = "";// csb.GetPassword();
                var systemDatabase = "Jet OLEDB:System Database";// csb.GetSystemDatabase();

and it works fine after that. Not quite sure what is going on to make it do that

lauxjpn · 2020-08-26T18:34:59Z

What is the version of System.Data.OleDb you are referencing in your .csproj file? Also, what is the exact connection string you are using in your scaffolding command?

It might also help, if you can provide us with the database you are trying to scaffold.

There are some bugs in System.Data.OleDb versions, where method calls are not properly mapped to the actual OLE DB implementation (COM). See dotnet/runtime#33899 and #43. At least most of these should be fixed in very recent builds of System.Data.OleDb. Our tests e.g. run against recent 5.0.0-preview versions of System.Data.OleDb.

You can also just try to switch to ODBC instead (or only for scaffolding, if you like), which has none of those issues. This should be as easy as using a connection string like DBQ=MyDatabasePath in the scaffolding command.

If this turns out to be an issue that has not been fixed yet in the newer versions of System.Data.OleDb, we could consider to parse the connection string via Regex instead of using the connection string builder or just using the DbConnectionStringBuilder instance, which might be sufficient as well.

Otherwise, we should probably check that the System.Data.OleDb version is new enough when OLE DB is being used or throw if it is not.

ChrisJollyAU · 2020-08-27T11:08:32Z

System.Data.OleDb: 4.7.1
.Net Core SDK: 3.1.401 (x86)
.Net Core runtime: 3.1.7
Scaffold command: Scaffold-DbContext -Connection "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='z:\Folder Path\recent.mdb';" -Provider EntityFrameworkCore.Jet -OutputDir RMModel -verbose

lauxjpn · 2020-08-27T12:57:50Z

@ChrisJollyAU You are running the command on a 32 bit system?

ChrisJollyAU · 2020-08-27T13:44:17Z

64 bit. But the SDK, runtime, Jet driver and program are all set for 32 bit

lauxjpn · 2020-08-27T13:57:17Z

Alright, I am able to replicate this issue on a 32 bit Windows installation.
As a quick workaround while I track this down, use ODBC as suggested above, which works without issues:

This should be as easy as using a connection string like DBQ=MyDatabasePath in the scaffolding command.

lauxjpn · 2020-08-27T14:15:38Z

There are some bugs in System.Data.OleDb versions, where method calls are not properly mapped to the actual OLE DB implementation (COM). See dotnet/runtime#33899 and #43. At least most of these should be fixed in very recent builds of System.Data.OleDb. Our tests e.g. run against recent 5.0.0-preview versions of System.Data.OleDb.

This is the underlying issue here. We are using the OleDbConnectionStringBuilder.TryGetValue() method before scaffolding starts, to extract connection string options, so we can use them in subsequent DAO and ADOX calls to retrieve the database schema information (which are needed to navigate around a number of other bugs and missing implementation details) which then is used by the scaffolder.

We could just not use the TryGetValue() method, but if this version of System.Data.OleDb would be used when running EF Core queries, other unrecoverable bugs would surface, so we will not implement a fix for this. Instead, a version of System.Data.OleDb where this bugs have been fixed needs to be used.

Looks like the PR by @FreddyD-GH (dotnet/runtime#33899) still hasn't made it into a public release.

@saurabh500 @danmosemsft Shouldn't have this gone into a servicing release months ago?

(From dotnet/runtime#33899 (comment))

Thanks for the validations. I will start on the servicing PR.

@FreddyD-GH already asked about this on April 15:

@saurabh500 Did this ever PR (and #32207) ever make it to servicing? I didn't see it in the other repo. Sounds like it may have missed the April 10-15 deadline, which would be unfortunate.

lauxjpn · 2020-08-27T14:24:10Z

@ChrisJollyAU If you want to use OLE DB as the data access method, make sure to use one of the preview releases of System.Data.OleDb. One of the earliest that had the fix was 5.0.0-preview.3.20178.1, while the current one is 5.0.0-preview.8.20407.11.

Otherwise, use ODBC, which seems to be much more stable than OLE DB when accessed by .NET and when accessing a Jet database, due to a variety of bugs between System.Data.OleDb <--> OLE DB, and OLE DB <--> Jet/ACE OLE DB Provider.

ChrisJollyAU · 2020-08-27T14:59:10Z

It's only having a problem with User Id, Password and possibly System Database

Workaround 1: Specify a non-empty value in the connection string

If you just specify User Id=admin; in the connection string it gets passed that but hangs at the same place for the password

If you add User Id=admin;Password=a; in the connection string it gets passed both but does error on the system database with the following error

Cannot start your application. The workgroup information file is missing or opened exclusively by another user.

So it is getting passed all of them in the end

ChrisJollyAU · 2020-08-27T15:05:53Z

Workaround 2: Update the GetUserId function with the follwoing

        if (builder.ContainsKey("user id"))
        {
            return builder.TryGetValue("user id", out var value)
            ? (string)value
            : null;
        }

        if (builder.ContainsKey("uid"))
        {
            return builder.TryGetValue("uid", out var value)
                ? (string)value
                : null;
        }
        return null;

Same thing with Password and System database and it works perfectly without specifying the values in the connection string

lauxjpn · 2020-08-27T15:19:07Z

@ChrisJollyAU See my comment from above for why we will not fix this issue:

We could just not use the TryGetValue() method, but if this version of System.Data.OleDb would be used when running EF Core queries, other unrecoverable bugs would surface, so we will not implement a fix for this. Instead, a version of System.Data.OleDb where th[e] bugs have been fixed needs to be used.

The correct approach is to either use a current preview version of System.Data.OleDb, or to use ODBC instead.

We should however make ODBC the default, so that anybody who just uses the file path as the connection string (which we support) and does not reference a fixed version of System.Data.OleDb, does not unnecessarily run into this issue.

We will also publish two packages, EntityFrameworkCore.Jet.Odbc and EntityFrameworkCore.Jet.OleDb, that will reference the supported versions.

ChrisJollyAU · 2020-08-27T15:47:46Z

5.0.0-preview.8.20407.11 does work fine for that problem. Looks like you're going to need to make 5.0.0 the min required version for your packages

lauxjpn self-assigned this Aug 25, 2020

lauxjpn added this to the 3.1.x milestone Aug 25, 2020

This was referenced Aug 25, 2020

Unknown system table #56

Closed

Scaffolding complex table indexes #57

Closed

Scaffolding is converting to the wrong data type #58

Closed

Reimplement schema retrieval and scaffolding #59

Merged

ChrisJollyAU mentioned this issue Aug 27, 2020

Fix hang when trying to get empty userid and password in connection string #60

Closed

lauxjpn changed the title ~~ExpandDatabaseFilePath freezes scaffolding~~ OleDbConnectionStringBuilder.TryGetValue() throws when scaffolding as x86 using a System.Data.OleDb version <= 4.7.1 Aug 27, 2020

lauxjpn added the question label Aug 27, 2020

lauxjpn mentioned this issue Oct 20, 2020

Fix x86 packing issues in System.Data.OleDb dotnet/runtime#33899

Merged

lauxjpn added bug and removed question labels Nov 28, 2020

lauxjpn mentioned this issue Nov 28, 2020

Add ODBC and OLE DB specific packages #74

Merged

lauxjpn modified the milestones: 3.1.x, 3.1.0-alpha.1 Nov 28, 2020

lauxjpn closed this as completed in #74 Nov 28, 2020

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

OleDbConnectionStringBuilder.TryGetValue() throws when scaffolding as x86 using a System.Data.OleDb version <= 4.7.1 #55

OleDbConnectionStringBuilder.TryGetValue() throws when scaffolding as x86 using a System.Data.OleDb version <= 4.7.1 #55

ChrisJollyAU commented Aug 25, 2020

lauxjpn commented Aug 25, 2020 •

edited

lauxjpn commented Aug 25, 2020 •

edited

ChrisJollyAU commented Aug 26, 2020 •

edited by lauxjpn

lauxjpn commented Aug 26, 2020 •

edited

ChrisJollyAU commented Aug 27, 2020

lauxjpn commented Aug 27, 2020

ChrisJollyAU commented Aug 27, 2020

lauxjpn commented Aug 27, 2020 •

edited

lauxjpn commented Aug 27, 2020 •

edited

lauxjpn commented Aug 27, 2020 •

edited

ChrisJollyAU commented Aug 27, 2020

ChrisJollyAU commented Aug 27, 2020 •

edited

lauxjpn commented Aug 27, 2020 •

edited

ChrisJollyAU commented Aug 27, 2020

OleDbConnectionStringBuilder.TryGetValue() throws when scaffolding as x86 using a System.Data.OleDb version <= 4.7.1 #55

OleDbConnectionStringBuilder.TryGetValue() throws when scaffolding as x86 using a System.Data.OleDb version <= 4.7.1 #55

Comments

ChrisJollyAU commented Aug 25, 2020

lauxjpn commented Aug 25, 2020 • edited

lauxjpn commented Aug 25, 2020 • edited

ChrisJollyAU commented Aug 26, 2020 • edited by lauxjpn

lauxjpn commented Aug 26, 2020 • edited

ChrisJollyAU commented Aug 27, 2020

lauxjpn commented Aug 27, 2020

ChrisJollyAU commented Aug 27, 2020

lauxjpn commented Aug 27, 2020 • edited

lauxjpn commented Aug 27, 2020 • edited

lauxjpn commented Aug 27, 2020 • edited

ChrisJollyAU commented Aug 27, 2020

ChrisJollyAU commented Aug 27, 2020 • edited

lauxjpn commented Aug 27, 2020 • edited

ChrisJollyAU commented Aug 27, 2020

lauxjpn commented Aug 25, 2020 •

edited

lauxjpn commented Aug 25, 2020 •

edited

ChrisJollyAU commented Aug 26, 2020 •

edited by lauxjpn

lauxjpn commented Aug 26, 2020 •

edited

lauxjpn commented Aug 27, 2020 •

edited

lauxjpn commented Aug 27, 2020 •

edited

lauxjpn commented Aug 27, 2020 •

edited

ChrisJollyAU commented Aug 27, 2020 •

edited

lauxjpn commented Aug 27, 2020 •

edited