Scaffolding does not Work - COMException when calling EntityFrameworkCore.Jet.Data.ComObject.TryGetMember

[LuckyGeorge1975]

I've just started a simple project to test if i can read an access db with EF and the Jet Provider. But the scaffolding leads to a COM Exception 0x800A0CB3.

What i have done

1. Downloaded the latest northwind.accdb
2. Opened the DB in Access and VS 2022 Server Explorer to ensure that i can read the file
3. Setup a simple project and a corresponding test project

Project:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>net8.0</TargetFramework>
    <PlatformTarget>AnyCPU</PlatformTarget>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="EntityFrameworkCore.Jet" Version="8.0.0-alpha.2" />
    <PackageReference Include="EntityFrameworkCore.Jet.Data" Version="8.0.0-alpha.2" />
    <PackageReference Include="EntityFrameworkCore.Jet.Odbc" Version="8.0.0-alpha.2" />
    <PackageReference Include="EntityFrameworkCore.Jet.OleDb" Version="8.0.0-alpha.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="8.0.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="8.0.1">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="8.0.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="8.0.1">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="System.Data.OleDb" Version="8.0.0" />
  </ItemGroup>

</Project>

DBContext:

public class JetTestContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseJet(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<PATH>\northwind.accdb;");
    }
}

Test:

[Test]
public void JetConnection()
{
    using (JetTestContext context = new JetTestContext())
    {
        context.Database.OpenConnection();
        context.Database.CloseConnection();
        Assert.IsTrue(context.Database.CanConnect());
    }
}

The Unit Test is successful.

After that i tried to scaffold the DB Context:

Scaffold-DbContext -Connection "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<PATH>\northwind.accdb;" -Provider EntityFrameworkCore.Jet -OutputDir Models

This results in the Exception:

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.
 ---> System.Runtime.InteropServices.COMException (0x800A0CB3): Das Objekt oder der Provider kann den angeforderten Vorgang nicht ausführen.
   --- End of inner exception stack trace ---
   at System.RuntimeType.InvokeDispMethod(String name, BindingFlags invokeAttr, Object target, Object[] args, Boolean[] byrefModifiers, Int32 culture, String[] namedParameters)
   at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
   at EntityFrameworkCore.Jet.Data.ComObject.TryGetMember(GetMemberBinder binder, Object& result)
   at CallSite.Target(Closure, CallSite, Object)
   at EntityFrameworkCore.Jet.Data.AdoxSchema.GetColumns()
   at EntityFrameworkCore.Jet.Data.PreciseSchema.GetColumns()
   at EntityFrameworkCore.Jet.Data.JetStoreSchemaDefinition.JetInformationSchema.GetColumns(JetConnection connection)
   at EntityFrameworkCore.Jet.Data.JetStoreSchemaDefinition.JetInformationSchema.GetDbDataReaderFromSimpleStatement(JetCommand command)
   at EntityFrameworkCore.Jet.Data.JetStoreSchemaDefinition.JetInformationSchema.TryGetDataReaderFromInformationSchemaCommand(JetCommand command, DbDataReader& dataReader)
   at EntityFrameworkCore.Jet.Data.JetCommand.ExecuteDbDataReaderCore(CommandBehavior behavior)
   at EntityFrameworkCore.Jet.Data.JetCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at EntityFrameworkCore.Jet.Scaffolding.Internal.JetDatabaseModelFactory.GetColumns(DbConnection connection, IReadOnlyList`1 tables)
   at EntityFrameworkCore.Jet.Scaffolding.Internal.JetDatabaseModelFactory.GetTables(DbConnection connection, DatabaseModel databaseModel, Func`3 filter)
   at EntityFrameworkCore.Jet.Scaffolding.Internal.JetDatabaseModelFactory.Create(DbConnection connection, DatabaseModelFactoryOptions options)
   at EntityFrameworkCore.Jet.Scaffolding.Internal.JetDatabaseModelFactory.Create(String connectionString, DatabaseModelFactoryOptions options)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ReverseEngineerScaffolder.ScaffoldModel(String connectionString, DatabaseModelFactoryOptions databaseOptions, ModelReverseEngineerOptions modelOptions, ModelCodeGenerationOptions codeOptions)
   at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldContext(String provider, String connectionString, String outputDir, String outputContextDir, String dbContextClassName, IEnumerable`1 schemas, IEnumerable`1 tables, String modelNamespace, String contextNamespace, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames, Boolean suppressOnConfiguring, Boolean noPluralize)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl(String provider, String connectionString, String outputDir, String outputDbContextDir, String dbContextClassName, IEnumerable`1 schemaFilters, IEnumerable`1 tableFilters, String modelNamespace, String contextNamespace, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames, Boolean suppressOnConfiguring, Boolean noPluralize)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)

I also installed the latest Access Driver Engine but the result is the same.

What am i doing wrong?

[ChrisJollyAU]

My first thought is that this is more likely a bitness problem. You need everything to match for Jet to work.

Can you confirm a couple of things

1. Microsoft Access. Is it x86 or x64
2. The driver engine that you installed. Which version (2010/2016 etc) and is it x86 or x64?
3. Can you post the result of dotnet --info

The scaffolding is run under the dotnet program and not your own program. Thus, that needs to be matching on the same bitness as the engine you have installed. Your own program is working fine by the looks of it. The COM Exception is occurring due to it not being able to find the required COM classes for AdoX etc

[LuckyGeorge1975]

Thank you for your answer. I think it is all x64 but maybe i'm wrong.

1. Access Version 16.0.17231.20182 (Latest from Office 365, x64)
2. northwind.accdb (Access 2007 - 2016 Format)
3. Database Engine 16.0.5044 (x64)
4. dotnet --info:

.NET SDK:
 Version:           8.0.101
 Commit:            6eceda187b
 Workload version:  8.0.100-manifests.56f9c534

Laufzeitumgebung:
 OS Name:     Windows
 OS Version:  10.0.22631
 OS Platform: Windows
 RID:         win-x64
 Base Path:   C:\Program Files\dotnet\sdk\8.0.101\

Installierte .NET-Workloads:
 Workload version: 8.0.100-manifests.56f9c534
 [android]
   Installationsquelle: SDK 8.0.100, VS 17.8.34408.163
   Manifestversion:    34.0.52/8.0.100
   Manifestpfad:       C:\Program Files\dotnet\sdk-manifests\8.0.100\microsoft.net.sdk.android\34.0.52\WorkloadManifest.json
   Installationstyp:              Msi

 [ios]
   Installationsquelle: SDK 8.0.100, VS 17.8.34408.163
   Manifestversion:    17.0.8490/8.0.100
   Manifestpfad:       C:\Program Files\dotnet\sdk-manifests\8.0.100\microsoft.net.sdk.ios\17.0.8490\WorkloadManifest.json
   Installationstyp:              Msi

 [maccatalyst]
   Installationsquelle: SDK 8.0.100, VS 17.8.34408.163
   Manifestversion:    17.0.8490/8.0.100
   Manifestpfad:       C:\Program Files\dotnet\sdk-manifests\8.0.100\microsoft.net.sdk.maccatalyst\17.0.8490\WorkloadManifest.json
   Installationstyp:              Msi

 [maui]
   Installationsquelle: SDK 8.0.100
   Manifestversion:    8.0.3/8.0.100
   Manifestpfad:       C:\Program Files\dotnet\sdk-manifests\8.0.100\microsoft.net.sdk.maui\8.0.3\WorkloadManifest.json
   Installationstyp:              Msi

 [tizen]
   Installationsquelle: SDK 8.0.100
   Manifestversion:    8.0.130/8.0.100
   Manifestpfad:       C:\Program Files\dotnet\sdk-manifests\8.0.100\samsung.net.sdk.tizen\WorkloadManifest.json
   Installationstyp:              Msi

 [wasm-tools]
   Installationsquelle: SDK 8.0.100, VS 17.8.34408.163
   Manifestversion:    8.0.1/8.0.100
   Manifestpfad:       C:\Program Files\dotnet\sdk-manifests\8.0.100\microsoft.net.workload.mono.toolchain.current\8.0.1\WorkloadManifest.json
   Installationstyp:              Msi

 [maui-windows]
   Installationsquelle: VS 17.8.34408.163
   Manifestversion:    8.0.3/8.0.100
   Manifestpfad:       C:\Program Files\dotnet\sdk-manifests\8.0.100\microsoft.net.sdk.maui\8.0.3\WorkloadManifest.json
   Installationstyp:              Msi

 [wasm-tools-net7]
   Installationsquelle: VS 17.8.34408.163
   Manifestversion:    8.0.1/8.0.100
   Manifestpfad:       C:\Program Files\dotnet\sdk-manifests\8.0.100\microsoft.net.workload.mono.toolchain.net7\8.0.1\WorkloadManifest.json
   Installationstyp:              Msi


Host:
  Version:      8.0.1
  Architecture: x64
  Commit:       bf5e279d92

.NET SDKs installed:
  7.0.405 [C:\Program Files\dotnet\sdk]
  8.0.101 [C:\Program Files\dotnet\sdk]

.NET runtimes installed:
  Microsoft.AspNetCore.All 2.1.30 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.All]
  Microsoft.AspNetCore.App 2.1.30 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 3.1.32 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 6.0.26 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 7.0.15 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 8.0.1 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.NETCore.App 2.1.30 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 3.1.32 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 6.0.26 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 7.0.15 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 8.0.1 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.WindowsDesktop.App 3.1.32 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]
  Microsoft.WindowsDesktop.App 6.0.26 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]
  Microsoft.WindowsDesktop.App 7.0.15 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]
  Microsoft.WindowsDesktop.App 8.0.1 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]

Other architectures found:
  x86   [C:\Program Files (x86)\dotnet]
    registered at [HKLM\SOFTWARE\dotnet\Setup\InstalledVersions\x86\InstallLocation]

Environment variables:
  Not set

global.json file:
  Not found

I also tried with .NET 7.0 and EntityFrameWorkCore.Jet 7.03 - the result is the same.

[ChrisJollyAU]

It all looks like it should work. Almost all the time COM Exception tend to be when it can't find the COM class that it needs to create. Generally either because it is not installed or its looking for x86 when you have x64 installed or vice versa.

This is the links for the 2016 installer used with the testing system. x64 https://download.microsoft.com/download/3/5/C/35C84C36-661A-44E6-9324-8786B8DBE231/AccessDatabaseEngine_X64.exe x86 https://download.microsoft.com/download/3/5/C/35C84C36-661A-44E6-9324-8786B8DBE231/AccessDatabaseEngine.exe

Have a go at both. You will soon know which one installs as it doesn't like having different bit architecture installed at the same time Just tried on my pc.

1. Created a new Empty Web Application
2. Added in the correct references and made sure it compiled
3. Without doing anything else, in the package manager console, executed Scaffold-DbContext -Connection "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\dev\northwind.accdb;" -Provider EntityFrameworkCore.Jet -OutputDir Models
4. It scaffolded without any errors

My project file is

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>net8.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="EntityFrameworkCore.Jet" Version="8.0.0-alpha.2" />
    <PackageReference Include="EntityFrameworkCore.Jet.Data" Version="8.0.0-alpha.2" />
    <PackageReference Include="EntityFrameworkCore.Jet.Odbc" Version="8.0.0-alpha.2" />
    <PackageReference Include="EntityFrameworkCore.Jet.OleDb" Version="8.0.0-alpha.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="8.0.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="8.0.1">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="8.0.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="8.0.1">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="System.Data.OleDb" Version="8.0.0" />
  </ItemGroup>
</Project>

For the record, my environment has the 2010 x86 engine and Office 365 for x64. Doing the whole suite of tests is a bit more stable in x86

[LuckyGeorge1975]

Yesterday i tried the following:

1. Installed a brand new Windows 11 Environment in Hyper-V (english)
2. Installed the x64 Access Database Driver 2016 (english)
3. Installed the .net 8.0 x64 SDK
4. Installed VS Code (english)
5. Copied the project and the database
6. Ensured the dotnet environment is x64 via "dotnet --info"
7. Scaffold

--> Same Exception

After that i created a new project in the Hyper-V Machine, compiled it and tried to scaffold

--> Same Exception

I thought, that maybe when using the default language (english) and a brand new machine it will work, but it does not. I'm afraid i have to build the models for the access db manually or try to get it work in a pure x86 environment. Hopefully this will work.

[ChrisJollyAU]

Odd. I'm going to try install a VM and give it a try.

I have an empty test project that I will link with a database. Are you willing to give it a test.

In your cli just run dotnet ef dbcontext scaffold "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=database3.accdb;" EntityFrameworkCore.Jet --output-dir db4

There is already a db4 folder from a test run. You can either delete it or change the output dir name

Another thing to try is in the project file to ensure the platform target is for x64 <PlatformTarget>AnyCPU</PlatformTarget> (this line)

[ChrisJollyAU]

Installed the Windows 11 dev environment from Hyper V. Came with .Net 8.0.1 x64. Installed Access Engine x64 Installed EF Core tools (dotnet tool install --global dotnet-ef) Ran the scaffold script as mentioned in the previous comment. It worked. Attached is the test project you can try with.

WebApplication1.zip

I'm starting to wonder if there is a difference with the Northwind database you are testing on. Can you link that so I can look at it?

[LuckyGeorge1975]

Hmm, it looks like it really is the northwind.accdb.

I run your WebApplication1 - everything works fine and the models are created. After that i just copied your db3 to my project folder (the one with the northwind.accdb), run scaffolding and it also works. So it seems the issue is with the northwind.accdb. Here is the file:

https://github.com/LuckyGeorge1975/Share/blob/main/northwind.accdb

EDIT

The northwind db was downloaded via Access and not directly from Microsoft. I followed the instructions here: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/downloading-sample-databases#northwind_access

[ChrisJollyAU]

Definitely something interesting going on here.

The problem is occurring when it is trying to get the column count of a table. Specifically it is only on some (but not all) system tables (those tables hidden and start with MSys

To confuse things, the database I linked also has those same tables, same name and it works fine.

Assuming most people aren't interested in scaffolding system tables, I worked on ignoring them.

Ran into another problem when I encountered a relation/constraint that referenced a column that was not a column in that table. After a bit of research this appears to be a rather undocumented feature for complex columns / multi valued field. Still not quite sure how it works except for that the column defintion is not stored in the table, it is stored in its own system table. Values look to be stored in another system table

Not sure yet on how this would even work in scaffolding. Definitely would not work with a CREATE TABLE SQL. That issue you would only encounter if the database table used complex columns AND that column was an index/constraint. Otherwise EFCore.Jet will not pick it up.

[yepeekai]

Is there a workaround? I am having this issue with a database.

Thanks

[yepeekai]

In my case, It failed for an external/linked table. I am no expert about that, I don't know if it would make sense to simply ignore them... When I specify the tables I want to scaffold, it still go through all tables at that place in the code and fails

[ChrisJollyAU]

@yepeekai Interesting. Don't think we actually have code that knows how to deal with linked tables. May have to have a look and try replicate

[yepeekai]

Some context, I received a copy of that access database so the linked table are obviously not working since I do not have the other database.

I deleted all linked tables since I did not need them. I compact/repair and then I get the same error at the same place for table : MSysACEs

[yepeekai]

Adding the following lines in AdoxSchema.cs solves it for me.

if (tableName.StartsWith("MSys", StringComparison.OrdinalIgnoreCase))
{
    continue;
}

Workaround (complicated): clone this repository add a console app add the nuget package entityframeworkcore.tools add a reference to EFCore.Jet and EFCore.Jet.Data Add the 3 lines mentionned above after all occurences of for (var i = 0; i < tableCount; i++) There are 6 places.

execute scaffold-dbcontext in package manager console for the new console app.

[yepeekai]

I think it would be nice to have some kind of error handling in there to also support linked table no longer reachable and maybe log some warning instead of crashing.

[ChrisJollyAU]

@yepeekai @LuckyGeorge1975 Just added a commit to fix the main issue with the MSys tables.

You can now optionally ignore the MSys tables. Using dotnet ef scaffold or Scaffold-DbContext doesn't allow any extra custom options to be passed in so that left doing it through the connection string.

If you add IgnoreMSys=YES; to the connection string e.g. "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=northwind.accdb;IgnoreMsys=YES;" it will set the option to ignore the MSys tables when scaffolding.

You can try this out by referencing the daily builds

@yepeekai Regarding the linked tables, skipping/ignoring them would only work if they were stand alone. If there was any other foreign key/reference to them you would run into issues