bubibubi / jetentityframeworkprovider Goto Github PK

No one creates new Jet Databases anymore, the only value this tool has is for working with existing databases. JetEntityFrameworkProvider needs to support DB first, even in a limited capacity. So if there is anything you can do to help in that realm it would be greatly appreciated.

Question 1: Was the schema retriever ever functional at all? even in a limited capacity?

Question 2: Is there a way to force Entity Framework to use existing tables, instead of trying to create new ones? I have tried manually recreating the model in code, however, I get an error: "System.Data.OleDb.OleDbException: 'Table 'Component' already exists.'" for the table model, I have recreated.

Dear @bubibubi ,

I discovered that JetEntityFrameworkProvider has not been creating ON DELETE CASCADE constraints in my Access DB.

I think the best way to illustrate this is by offering you an example, so I created these following classes to demonstrate:

public class TestContext : DbContext 
    {
        public TestContext()
        { }
        public TestContext(DbConnection connection)
            : base(connection, false)
        { }
        public DbSet<Parent> Parents { get; set; }
        public DbSet<Child> Children { get; set; }
    }

    public class Parent
    {
        public int parentId { get; set; }
        public Parent()
        {
            children = new Collection<Child>();
        }

        public ICollection<Child> children { get; set; }
    }

    public class Child
    {
        public int childId { get; set; }

        public int parentId { get; set; }
        public Parent parent { get; set; }
    }

And here's the little helper I used to create the connection, in case that matters:

    public class JetHelper
    {
        public static DbConnection GetConnection(string fullPath)
        {
            JetConnection.DUAL = JetConnection.DUALForAccdb; 
            DbConnection connection = new JetConnection();

            connection.ConnectionString = GetConnectionString(fullPath);

            return connection;
        }

        private static string GetConnectionString(string fullPath)
        {
            OleDbConnectionStringBuilder oleDbConnectionStringBuilder = new OleDbConnectionStringBuilder();

            oleDbConnectionStringBuilder.Provider = "Microsoft.ACE.OLEDB.12.0";

            oleDbConnectionStringBuilder.DataSource = fullPath;
            return oleDbConnectionStringBuilder.ToString();
        }
    }

Adding a new migration generates the following code:

    public partial class migration1 : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "dbo.Children",
                c => new
                    {
                        childId = c.Int(nullable: false, identity: true),
                        parentId = c.Int(nullable: false),
                    })
                .PrimaryKey(t => t.childId)
                .ForeignKey("dbo.Parents", t => t.parentId, cascadeDelete: true)
                .Index(t => t.parentId);
            
            CreateTable(
                "dbo.Parents",
                c => new
                    {
                        parentId = c.Int(nullable: false, identity: true),
                    })
                .PrimaryKey(t => t.parentId);
            
        }
        
        public override void Down()
        {
            DropForeignKey("dbo.Children", "parentId", "dbo.Parents");
            DropIndex("dbo.Children", new[] { "parentId" });
            DropTable("dbo.Parents");
            DropTable("dbo.Children");
        }
    }

We can clearly see, that the Child to Parent FK defines a cascade constraint.

However, when I run the Update-Database -Verbose command I recieve the following SQL script:

CREATE TABLE [Children] (
 [childId] int not null identity(1,1)
, [parentId] int not null
);
ALTER TABLE [Children] ADD CONSTRAINT [PK_Children_102c9742] PRIMARY KEY ([childId])
CREATE INDEX [IX_parentId] ON [Children] ([parentId])
CREATE TABLE [Parents] (
 [parentId] int not null identity(1,1)
);
ALTER TABLE [Parents] ADD CONSTRAINT [PK_Parents_102c9742] PRIMARY KEY ([parentId])
ALTER TABLE [Children] ADD CONSTRAINT [FK_Children_Parents_parentId] FOREIGN KEY ([parentId]) REFERENCES [Parents] ([parentId])
CREATE TABLE [__MigrationHistory] (
 [MigrationId] varchar(150) not null
, [ContextKey] text not null
, [Model] image not null
, [ProductVersion] varchar(32) not null
);
ALTER TABLE [__MigrationHistory] ADD CONSTRAINT [PK___MigrationHistory_102c9742] PRIMARY KEY ([MigrationId], [ContextKey])
insert into [__MigrationHistory]([MigrationId], [ContextKey], [Model], [ProductVersion]) 
[THE REST OF THE CODE REMOVED FOR BREVETY]

I checked the newly created test database (in both mdb and accdb formats) and the constraint was not set.

Switching over to a different provider, like MS-SQL creates a different SQL script that correctly includes the Cascade constraint.

This is a real problem because we are unable to delete parent entities from the db without deleting children entities beforehand.

Has this feature not been implemented in JetEntityFrameworkProvider yet, or have I misinterpreted or implemented something wrong?

I hope you can help me.
Thank you!

PS: Here's the command I used to update the DB (not sure if this matters):

Update-Database -Verbose -ConnectionProviderName JetEntityFrameworkProvider -ConnectionString "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=D:\testdb.accdb" 

Hi,

the test project will not compile for me. It is missing two files:

Maybe theses files were forgotten to be checked in?

The name is something like dbo. and it throws an exeption.
To reproduce the behavior create 2 entities with a relationship then delete the relationship property.

Sample initializer

        public DeliveryManagerContext(DbConnection connection)
            : base(connection, false)
        {
            //Database.SetInitializer<DeliveryManagerContext>(null);
            //return;

            DbMigrationsConfiguration<DeliveryManagerContext> migrationConfiguration = new DbMigrationsConfiguration<DeliveryManagerContext>()
            {
                AutomaticMigrationsEnabled = true,
                AutomaticMigrationDataLossAllowed = true
            };

            Database.SetInitializer(new MigrateDatabaseToLatestVersion<DeliveryManagerContext, DbMigrationsConfiguration<DeliveryManagerContext>>(true, migrationConfiguration));
        }

Using this code, we can choose explicitly the name of a foreign key:

public virtual Standard Standard { get; set; } // Standard_StandardId

[ForeignKey("StandardId")]
public virtual Standard Standard { get; set; }
public int StandardId { get; set; }

It works fine, but the Required attribute no longer works then. Any solution?

Hey @bubibubi,

Love your work. Thanks for all you've done.

Now on to my question, I seem to remember in an old version of the documentation you had listed some limitations of the framework. I can't seem to find where those limitations are listed (was it CrossTab and/or timestamp related I don't remember??). Either you fixed them were or I just can't find where you had it. Reason I ask is that I wanted to use Pivot operations EF and for some reason I thought it was limited. I will probably go with this approach anyway but I thought I would ask just the same.

By the way, I'd be glad to help document the answer if you like.

Thanks again.

I've built JetEntityFrameworkProvider on x86,rebooting visual studio several times and i want know how to set connection string.Please help !

I am trying to use your provider to work with access database (version Microsoft.Jet.OLEDB.4.0). Select and update operations work very well but when i try to insert an element i have an error when i call the SaveChanges() methods...

the error is : Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=472540 for information on understanding and handling optimistic concurrency exceptions.

The mdb database is a model with existing tables and no records...

Could you help me ?

                using (var mdbContext = new MdbContext(MDBTools.GetMdbConnectionString(tmpMdbPath)))
                {
                    var bac = new BacEntity();
                    mdbContext.BacEntities.Add(bac);

                    mdbContext.SaveChanges();
                }

    [Table("Bac")]
    public class BacEntity
    {
        [Key]
        [Column("idBac")]
        public int IdBac { get; set; }
        [Column("idScanner")]
        public int IdScanner { get; set; }
        [Column("bacNo")]
        public short BacNo { get; set; }
    }

I'm attempting to use GraphDiff with an MS Access DB and one of my entities uses a Guid. I get Primitive type kind Guid is not supported by the Jet Provider when running GraphDiff.UpdateGraph(...) on this particular entity.

The line it occurs on is here. I notice the Guid case goes to this exception.

Why isn't .ToString() just called on the Guid? I don't fully understand how GraphDiff does things to end up with the Guid as a constant expression. Is there a reason JetEntityFrameworkProvider doesn't allow that or is this just a bug?

I'm getting the next exception when i published my web api solution over IIS, when i was working with visual studio it works very well, the execption showed up when i published the site and browsed it..

Exception Details: System.BadImageFormatException: Could not load file or assembly 'JetEntityFrameworkProvider' or one of its dependencies. An attempt was made to load a program with an incorrect format.

i followed the instruction to install .dll using GAC and change the machine.config (32bits and 64bits paths)
when run the utility gacutil.exe /l JetEntityFrameworkProvider:

Microsoft (R) .NET Global Assembly Cache Utility. Version 4.0.30319.0
Copyright (c) Microsoft Corporation. All rights reserved.
The Global Assembly Cache contains the following assemblies:
JetEntityFrameworkProvider, Version=1.2.10.0, Culture=neutral, PublicKeyToken=756cf6beb8fe7b41, processorArchitecture=MSIL

and the machine.config file has the below lines

 <system.data><DbProviderFactories>
		<add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>

		<add name="JetEntityFrameworkProvider" invariant="JetEntityFrameworkProvider" description="JetEntityFrameworkProvider" type="JetEntityFrameworkProvider.JetProviderFactory, JetEntityFrameworkProvider, Version=1.2.10.0, Culture=neutral, PublicKeyToken=756cf6beb8fe7b41" />

		</DbProviderFactories>
    </system.data>

NOTICE: i kept the version and publickeytoken all the same in all config files.

web.config lines:

 <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlCeConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="System.Data.SqlServerCe.4.0" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="JetEntityFrameworkProvider" type="JetEntityFrameworkProvider.JetProviderServices, JetEntityFrameworkProvider" />
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
      <provider invariantName="System.Data.SqlServerCe.4.0" type="System.Data.Entity.SqlServerCompact.SqlCeProviderServices, EntityFramework.SqlServerCompact" />
    </providers>
  </entityFramework>
  <system.data>
    <DbProviderFactories>
      <remove invariant="JetEntityFrameworkProvider" />
      <add invariant="JetEntityFrameworkProvider" name="Jet Entity Framework Provider" description="Jet Entity Framework Provider" type="JetEntityFrameworkProvider.JetProviderFactory, JetEntityFrameworkProvider" />
      <remove invariant="System.Data.SqlServerCe.4.0" />
      <add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
    </DbProviderFactories>
  </system.data>

this is the output messages when publish the solution

1>  zkaccess_webapi -> C:\Users\...\zkaccess_webapi\bin\zaccess_webapi.dll
2>------ Publish started: Project: zaccess_webapi, Configuration: Release Any CPU ------
2>Connecting to http://localhost/zaccessapi...
2>Transformed Web.config using C:\Users\...\zaccess_webapi\Web.Release.config into obj\Release\TransformWebConfig\transformed\Web.config.
2>Copying all files to temporary location below for package/publish:
2>obj\Release\AspnetCompileMerge\Source.
2>C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\aspnet_compiler.exe -v / -p C:\Users\...\zaccess_webapi\obj\Release\AspnetCompileMerge\Source -u -c C:\Users\...\zaccess_webapi\obj\Release\AspnetCompileMerge\TempBuildDir 
2>Copying all files to temporary location below for package/publish:
2>obj\Release\Package\PackageTmp.
2>Deleting existing files...
2>Publishing folder /...
2>Publishing folder Areas...
2>Publishing folder Areas/HelpPage...
2>Publishing folder Areas/HelpPage/Views...
2>Publishing folder Areas/HelpPage/Views/Help...
2>Publishing folder Areas/HelpPage/Views/Help/DisplayTemplates...
2>Publishing folder Areas/HelpPage/Views/Shared...
2>Publishing folder bin...
2>Publishing folder bin/amd64...
2>Publishing folder bin/amd64/Microsoft.VC90.CRT...
2>Publishing folder bin/roslyn...
2>Publishing folder bin/x86...
2>Publishing folder bin/x86/Microsoft.VC90.CRT...
2>Publishing folder Content...
2>Publishing folder fonts...
2>Publishing folder Scripts...
2>Publishing folder Views...
2>Publishing folder Views/Home...
2>Publishing folder Views/Shared...
2>Web App was published successfully http://localhost/zaccessapi
2>
========== Build: 1 succeeded, 0 failed, 0 up-to-date, 0 skipped ==========
========== Publish: 1 succeeded, 0 failed, 0 skipped ==========

what am i missing?

thank you!

Dear sir,
Below query fails with MS Access but runs well with SQL Server

var frequentItems = context.Items.Where(x => x.IsService == false)
.OrderByDescending(y => y.SaleDetails.Count).Take(25).ToList();

Pls refer attached exception details.
Exception.txt

Hello,

I could not make work the method IncludeOptimized from Nuget Z.EntityFramework.Plus.QueryIncludeOptimiz (v1.7.11) on an Access DB using the EF provider jetentityframeworkprovider (v6.1.5rc2).

This method IncludeOptimized is supposed to provide performance enhancement on nested EF "Include" statements (http://entityframework-plus.net/query-include-optimized).

The error message: Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'
Error number: 2147217900

Thanks for your support

I'm receiving this error On SaveChanges()

OleDbException: The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

this is my code

var pagesList = projectDB.Pages.OrderBy(x => x.PageNumber).ToList();

for(int i = 0; i < pagesList.Count(); i++)
{
    pagesList[i].BookNumber = i+30;
}

foreach(var blah2 in pagesList.OrderBy(x => x.Id))
{
    System.Console.WriteLine($" {{ID:{blah2.Id}, BN:{blah2.BookNumber}, PN:{blah2.PageNumber}}}");

}

projectDB.SaveChanges();

This is my output, as you can see I have no duplicates.

{ID:2, BN:58, PN:5}
{ID:3, BN:59, PN:6}
{ID:6, BN:62, PN:11}
{ID:7, BN:65, PN:21}
{ID:20, BN:64, PN:13}
{ID:21, BN:66, PN:22}
{ID:25, BN:67, PN:23}
{ID:29, BN:68, PN:24}
{ID:78, BN:35, PN:1}
{ID:79, BN:45, PN:2}
{ID:108, BN:34, PN:1}
{ID:132, BN:73, PN:41}
{ID:177, BN:33, PN:1}
{ID:291, BN:74, PN:42}
{ID:318, BN:32, PN:1}
{ID:319, BN:42, PN:2}
{ID:320, BN:48, PN:3}
{ID:340, BN:31, PN:1}
{ID:341, BN:41, PN:2}
{ID:342, BN:50, PN:3}
{ID:343, BN:53, PN:4}
{ID:344, BN:55, PN:5}
{ID:345, BN:60, PN:6}
{ID:346, BN:61, PN:7}
{ID:452, BN:71, PN:32}
{ID:469, BN:63, PN:12}
{ID:510, BN:72, PN:39}
{ID:520, BN:43, PN:2}
{ID:524, BN:75, PN:43}
{ID:533, BN:70, PN:31}
{ID:539, BN:69, PN:25}
{ID:610, BN:30, PN:1}
{ID:611, BN:36, PN:1}
{ID:612, BN:46, PN:2}
{ID:613, BN:37, PN:1}
{ID:614, BN:38, PN:1}
{ID:615, BN:44, PN:2}
{ID:616, BN:51, PN:3}
{ID:617, BN:52, PN:4}
{ID:618, BN:56, PN:5}
{ID:619, BN:40, PN:1}
{ID:620, BN:39, PN:1}
{ID:621, BN:47, PN:2}
{ID:622, BN:49, PN:3}
{ID:623, BN:54, PN:4}
{ID:624, BN:57, PN:5}

See Stack Overflow question

I just downloaded the JetEntityFrameworkProvider solution and it was missing the key.snk file in the build directory. Could someone please post a correct copy for download?

I'm trying a self generated key to get the projects to biuld, but it will probably cause issues.

DbFirst model generation only was able to generate models for about 20% of my db objects. The rest of the tables and views were simply missing from the model, although I had checked them in the generation wizard.

I attached a sample file of two tables where that issue occurs.

TEST1.zip

When changing the type of a column from int to int? the migration below is generated, however when I apply the migration the column is not marked as nullable (i.e. is still marked as required in Access). If I create an initial migration it does create the column with the correct settings.

public partial class MakeNullable : DbMigration
    {
        public override void Up()
        {
            AlterColumn("dbo.Items", "SomeProp", c => c.Int());
        }
        
        public override void Down()
        {
            AlterColumn("dbo.Items", "SomeProp", c => c.Int(nullable: false));
        }
    }

I usually use only single or double numbers, which has worked perfectly, but recently I built a system using decimal values ​​and saw that they are saved by ignoring the separator, that is, 4.9 is saved as 49. The same system works normally with decimal using SQL Server or switching from decimal to float using MSAccess. I believe it is a bug.

First of all, thanks for this library.
Secondly I've found the steps for installing on VS 2015 but there is no steps for VS 2019 so I've tried to do it on VS 2019 making the same steps but no luck.
I want to install it on VS 2019 and activate DB first mode.
Thanks in advance.

Hi,

When using table.skip(skipX).take(takeX) I run into empty datasets when skipX > 999.

I tracked it to JetCommand.cs line 288:
string stringTopCount = _WrappedCommand.CommandText.Substring(indexOfTop + 4, indexOfTopEnd - indexOfTop).Trim();

The SubString is off by one. Up to 3 digits it is fine but 4 digits gets truncated to 3, so 1100 becomes 110. My fix is:

string stringTopCount = _WrappedCommand.CommandText.Substring(indexOfTop + 5, indexOfTopEnd - indexOfTop).Trim();

becasue " top " is 5 chars long.

I was surprised to not get the same error with SKIP but I see you handle that SubString differently. On line 300 you have

string stringSkipCount = _WrappedCommand.CommandText.Substring(indexOfSkip + 5).Trim();

which I think should be a 6 for the same reason:

string stringSkipCount = _WrappedCommand.CommandText.Substring(indexOfSkip + 6).Trim();

Just to be consistent. It doesn't show the same problem but maybe some edge case will trip it up.

Hope that helps. In a selfish note, any chance of getting this in an RC2? I can't use the source, have to rely on the Nuget package.

Issue: I'm trying to use "Code first from database". Upon transitioning from "Choose Your Data Connection" to "Choose Your database objects and settings" by clicking "Next", the Entity Data Model Wizard suddenly closes itself without any error.

I've been following your video tutorial "Entity Framework DbFirst with JetEntityFrameworkProvider" and everything worked properly up to the point which is mentioned above.
The connection with Jet DDEX provider for JetEntityFrameworkProvider is successful, even tried multiple accdb's.
JetEntityFrameworkProvider is added to the GAC successfully.

I'm using:
JetEntityNetworkProvider, source project and nuget 6.1.3 (also tried 6.1.5-rc2).
EntityFramework 6.1.3
Target .NET framework 4.5.2
Visual Studio Professional 2015 Version 14.0 (SOFTWARE\Microsoft\VisualStudio\14.0)
In machine.config provider Version=6.0.0.0

What could be causing this?

Right now the NuGet package is compiled as x86. When targeting my solution as AnyCPU I get the warning message below. Can you compile the NuGet package as AnyCPU, or does the JetProvider not support x64?

C:\Program Files (x86)\Microsoft Visual Studio\2017\Community\MSBuild\15.0\Bin\Microsoft.Common.CurrentVersion.targets(1987,5): warning MSB3270: There was a mismatch between the processor architecture of the project being built "MSIL" and the processor architecture of the reference "C:\Users\[User]\.nuget\packages\jetentityframeworkprovider\1.2.11\lib\JetEntityFrameworkProvider.dll", "x86".
This mismatch may cause runtime failures. Please consider changing the targeted processor architecture of your project through the Configuration Manager so as to align the processor architectures between your project and references, or take a dependency on references with a processor architecture that matches the targeted processor architecture of your project.

I used the latest commit 594f231b to compile everything with VS2017. To do that, I had to update the build events and load the independent registry hive of VS2017 to make the ddex provider register correctly.

After i ensured, that the ddex provider and the actual jet provider are correctly registered and configured, I attempted to create a code first model of an existing database by using the wizard as shown in your linked youtube video (DbFirst).

When I supply all necessary parameters for the choosen jet ddex provider and click next to choose the tables I want to generate classes for, the window just closes and I never get to see any tables to choose from. As a result, nothing gets generated.

With another instance of VS attached to the VS instance that has the target project loaded, I can observe that the jet ddex provider and the jet ef provider are getting loaded. When I click next to select tables, a JetConnection get's created and closed, but it seems that neither any JetCommand is created, nor any JetStoreSchemaDefinition object is being accessed or GetStoreSchemaDescription and GetStoreSchemaMapping method is being called.

This issue can be reproduced with any access database. I tested it with a new database, where I created a single table containing an ID (PK, Auto, int) and a string column.

The following query:
context.Items.OrderBy(x => x.IsActive);
..returns items with IsActive == true first then those with IsActive == false (should be the opposite).

Similarily, the following query:
context.Items.OrderByDescending(x => x.IsActive);
..returns items with IsActive == false first then those with IsActive == true (should be the other way around).

This seems to be a bug in JetEntityFrameworkProvider because it works the right way with SQL Server and, of course, with any other collection. As a workaround, I'm currently using context.Items.ToList().OrderBy....

Hi,

First of all, I want to say it's a great library and it works just fine when I code manually the DbContext and the POCO classes.
However I have spent already one day trying to make it work for EF code first from Database, I have seen the video, github issues section and read the .md file several times, but I ran out of ideas how to fix my environment. I will comment all the steps and changes so it may help someone in the future.

I have VS2017 and downloaded version 6.1.4. My first impresion is many compilation errors, and can be fixed easily with this:

1. Key.snk is missing in the latest, so I got it from a previous commit.
2. Swapping vsvars32.bat to vsdevcmd.bat in pre and post build events.

This fixes the compilation errors. About the additional manual changes to make it work:

1. The registry file (JetDdexProvider.reg), I have tested two flavors, none of them work (15.0 is for vs2017 and 9.0 for vs2012, which I can confirm I have version 15.0 of VisualStudio.Data.Framework installed):
   1.1 - Microsoft.VisualStudio.Data.Framework, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
   1.2 - Microsoft.VisualStudio.Data.Framework, Version=15.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a

2. The REGROOT variable in Install.cmd, for which I have 4 entries in my registry, not sure which one is correct:

   • SET REGROOT=SOFTWARE\Microsoft\VisualStudio\15.0
   • SET REGROOT=SOFTWARE\Microsoft\VisualStudio\15.0_2212f647
   • SET REGROOT=SOFTWARE\Microsoft\VisualStudio\15.0_26def8c4
   • SET REGROOT=SOFTWARE\Microsoft\VisualStudio\15.0_d0e23bfb
     After some research, many people have the same multiple entries in the registry, so I will need some clarification which one to use, or at least how to verify which one is the right one.
     In the meantime I have compiled against all of them.

After succesfull compilation, I can verify the library has been registered in the GAC, also the registry entries have been created.

Next the machine.config, where I added this line:
<add name="JetEntityFrameworkProvider" invariant="JetEntityFrameworkProvider" description="JetEntityFrameworkProvider" type="JetEntityFrameworkProvider.JetProviderFactory, JetEntityFrameworkProvider, Version=6.0.0.0, Culture=neutral, PublicKeyToken=756cf6beb8fe7b41" />
(I am not sure if the version should be 6.1.4 or 6.0.0, but as shown in the .md file and other users it seems to be 6.0.0 the right one)

About my dummy project:

• It targets .NET Framework 4.5 (same as the library)
• I referenced JetEntityFrameworkProvider 6.1.4 from nuget
• Set x86 as target build.
• Rebuild and nothing, still not shown when I go to the wizard.

Any ideas what I am missing?

I followed the steps in your walkthrough for configuring DbFirst.

The dll is registered in GAC:

The file C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\Config\machine.config shows this:

    <system.data>
        <DbProviderFactories>
		<add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
		<add name="JetEntityFrameworkProvider" invariant="JetEntityFrameworkProvider" description="JetEntityFrameworkProvider" type="JetEntityFrameworkProvider.JetProviderFactory, JetEntityFrameworkProvider, Version=1.2.10.0, Culture=neutral, PublicKeyToken=756cf6beb8fe7b41" />
	</DbProviderFactories>
    </system.data>

Then, I created a new project and added EF 6.1.3 via NuGet. Finally, I opened the new item dialog and chose "ADO.NET Entity Data Model" and then "Code First from Database".

However, the dialog does not show the entry:

I've tried with VS 2015 (registry 14.0_Config) and VS 2012 (registry 12.0_Config) and even VS 2017. I've also tried on another computer with VS 2015. For the latter I've attached the registry items DataProviders and DataSources:
HKEY_CURRENT_USER-SOFTWARE-Microsoft-VisualStudio-14.0_Config-DataProviders.reg.txt
HKEY_CURRENT_USER-SOFTWARE-Microsoft-VisualStudio-14.0_Config-DataSources.reg.txt

When running a DbFirst on an existing .accdb, I got the message

| datatype currently not supported for the targeted entity-framework version. Column [colname] in table [tablename] was excluded.

Is this just an issue with DbFirst or will JetEntityFramework not work at all with those datatypes?

I'm trying to generate pre-generated mapping views to save a lot of time loading.
When I choose the "Generate Views" of EF Power Tools, I get the following error:
Unable to determint the provider name for provider factory of type JetEntityFrameworkProvider make sure that the ado.net provider is installed or registered in the application config.
Is there a solution or there is another way to do it?

I am raising this issue. I encountered this issue when using type Decimal. Non-issue for type Double.

storeDb.Carts.Where(x => x.CartId == ShoppingCartId).Sum(t => t.Quantity * t.Item.UnitPrice);

InvalidOperationException: invalid PrimitiveTypeKind for cast(): cannot handle type Decimal with Jet

From Keops31

After lots of time of investigation, I could not manage successfully a multiple INCLUDE query to eager load a mother class including its 2 linked classes.

        var query = _context.MotherClass
            .Include(o => o.LinkedClass1)
            .Include(o => o.LinkedClass2);
        var result = query.ToList();

In fact it fails on data mismatch issue on the object of type LinkedClass2.
The ORM detect a STRING whereas the Class Property as well as the DB field are both DOUBLE.

Why?
It seems it is because the SQL generated is split in 2 parts joined with a UNION ALL and in the FIRST part of the request the field from the LinkedClass2 table are set to (null).
Indeed, the engine is unable to detect a field type if in the FIRST part of the UNION ALL, the field value is NULL and NOT CASTED explicitly.

We have:

SELECT (null) FROM ...
 UNION ALL
 SELECT 123.45 FROM ...

Note that, as a SQL query result, some strange (Chinese?) characters are returned (the table is nevertheless displayed properly while opened MS Access) => data mismatch

In standard EF, we would have rather (working):

SELECT CAST(null as Double) FROM ...
 UNION ALL
 SELECT 123.45 FROM ...

Note that the reverse query below allows the ORM to detect the proper field type (Double) if the NULL is on the SECOND part

SELECT 123.45 FROM ...
 UNION ALL
 SELECT (null) FROM ...

I would recommend to force the CAST of any NULL value in JET SQL Generation mechanism.

I need to use a 64-bit version of the library for integration in a database synchronisation server, but the nuget package is x86 only. I've downloaded the code and compiled/tested with x64 using "Microsoft.ACE.OLEDB.16.0"; and works fine with the 64-bit OLEDB provider. There is no change other than targeting AnyCPU and referencing Microsoft.ACE.OLEDB.16.0 in JetEntityFrameworkProvider.Test.Helpers.GetJetConnectionString()
I'm happy to checkin (if you like), but would need the nuget package updated.

Excellent libary

Severity Code Description Project File Line Suppression Status
Error The command ""% ProgramFiles% \ Microsoft SDKs \ Windows \ v8.0A \ Bin \ NETFX 4.0 Tools \ gacutil.exe "/ u" JetEntityFrameworkProvider "" came out with the code 3. JetEntityFrameworkProvider

Please Help me

Is there a way of passing the connection string to the DbContext without having to create it in the app.config file?

Hi,

I am currently using jetEntityFrameworkProvider 6.1.3 DB First with EF 6.1.3 (I also tried 6.2).
When I update or create an edmx from the Access database. I have the following warning (translated from french):

• Error 6005 : data type "byte" is not currently supported by the selected Entity Framework. The column .... has been excluded.
  In the Access DB, the column is of type numeric, byte sized.

I have the same issue with a column type numeric, simple real.

How can I read theses columns?

Thanks.

Hello i remark that after an operation on mdb (OLEDB.4.0) the ldb is not removed so i suppose the connection is not correctly disposed...

I find this code in your library

                if (oldConnectionState != ConnectionState.Open)
                    connection.Close();

it should not be
if (oldConnectionState == ConnectionState.Open)
connection.Close();

thanks

Generating a model from my db seemed to take indefinitely. After 30mins (with VS using 100% of one CPU core) I killed the process. Next try I debugged the process and found the cause:

The function StoreSchemaDefinitionRetrieve.GetFieldRow executes an inefficient statement for all(?) tables and views in the db: select * from tbl where 1=2

The issue is that for views this will evaluate all rows. I found that doing this instead is much faster: select top 1 * from tbl (so top instead of where)

I tried to generate again and now it takes less than one minute.

The other concern of course is, why would it even execute that statement for an object I have not chosen for model generation... However, with the improved speed this is not of a concern to me.

It is an excellent library, I only have one question, in my access database I have defined views which I use for more rapids instead of defining the query, but it generates an error when processing the model since the views have no primary key and it does not matter to the model.

could you help me with this problem please, thank you

this is the error

Severity Code Description Project File Line Suppression Status
Warning Error 6013: The table or view 'Jet.Jet.Consulta1' has not defined a primary key and it is not possible to infer any valid primary key. This table or view has been excluded. To use the entity, you will need to review the schema, add the correct keys and remove your comments. dbfirs_sample d: \ documents \ visual studio 2015 \ Projects \ dbfirs_sample \ dbfirs_sample \ Model1.edmx 1

The Linq Function "DefaultIfEmpty" does not work with JetEntityFrameworkProvider. Upon investigation, the reason is that EntityFramework uses "LEFT OUTER JOIN" in the expression, which is not supported in Access.

For Example:

//Attempts to use "LEFT OUTER JOIN"
//Will throw Exception "Join Expression not supported"
Entity.Table.DefaultIfEmpty(null).FirstOrDefault(e => e.Id = 1)

As a caveat, this is a problem with Access, not JetEntityFrameworkProvider. The workaround I am using is the following extension method:

namespace System.Linq
{
    public static class LinqExtensions
    {
        /// 

        /// Workaround for 
        /// not working with JetEntity
        /// 
        /// First Value, otherwise default()
        public static TSource FirstOrEmpty(this IQueryable source, Expressions.Expression> predicate)
        {
            IQueryable result = source.Where(predicate);
            if (result.Count() == 0)
                return default(TSource);
            return result.First();
        }
    }
}

This code should work to determine if a column is nullable.
Check also EFCore.Jet for more info.

        private static bool GetIsNullable(IDbConnection connection, DataRow rowColumn)
        {
            DataRow fieldRow = GetFieldRow(connection, (string)rowColumn["TABLE_NAME"], (string)rowColumn["COLUMN_NAME"]);

            if (fieldRow == null)
                return Convert.ToBoolean(rowColumn["IS_NULLABLE"]);

            return (bool) fieldRow["AllowDBNull"] && Convert.ToBoolean(rowColumn["IS_NULLABLE"]);
        }

Thank you for developing.

Can you advice me on how we can add support for
EntityFramework.SharpDevelop 6.0.2
https://www.nuget.org/packages/EntityFramework.SharpDevelop/6.0.2

Thank you.

Hello,

After lots of time of investigation, I could not manage successfully a multiple INCLUDE query to eager load a mother class including its 2 linked classes.

        var query = _context.MotherClass
            .Include(o => o.LinkedClass1)
            .Include(o => o.LinkedClass2);
        var result = query.ToList();

In fact it fails on data mismatch issue on the object of type LinkedClass2.
The ORM detect a STRING whereas the Class Property as well as the DB field are both DOUBLE.

Why?
It seems it is because the SQL generated is split in 2 parts joined with a UNION ALL and in the FIRST part of the request the field from the LinkedClass2 table are set to (null).
Indeed, the engine is unable to detect a field type if in the FIRST part of the UNION ALL, the field value is NULL and NOT CASTED explicitly.

We have:
SELECT (null) FROM ...
UNION ALL
SELECT 123.45 FROM ...

Note that, as a SQL query result, some strange (Chinese?) characters are returned (the table is nevertheless displayed properly while opened MS Access) => data mismatch

In standard EF, we would have rather (working):
SELECT CAST(null as Double) FROM ...
UNION ALL
SELECT 123.45 FROM ...

Note that the reverse query below allows the ORM to detect the proper field type (Double) if the NULL is on the SECOND part
SELECT 123.45 FROM ...
UNION ALL
SELECT (null) FROM ...

I would recommend to force the CAST of any NULL value in JET SQL Generation mechanism.

Mickael

Need to check the conversion where are used

I have used this framework successfully with c#, however when attempting to replicate with vb.net. I'm unable. Is this framework only compatible with c#?

The link in the readme.md in docs that is titled Enabling DB First links to https://github.com/bubibubi/JetEntityFrameworkProvider/blob/master/docs/Enabling_DB_First.md

should be
https://github.com/bubibubi/JetEntityFrameworkProvider/blob/master/docs/Enabling_db_first.md

var pr = (from UserLog u in context.UserLogs
where u.UserId == UserId &&
DbFunctions.TruncateTime(u.LogInDate) == DateTime.Today.Date &&
u.LogOutDate == null
select u).ToList()

This query fails raising exception message "Undefined function 'TruncateTime' in expression."
But this query works well with MS Sql database

Exception details.txt
Inner Exception details.txt

It's the first time I use this EF provider to work with access databases and I'm trying to configure the database first mode, creating the edmx model from a database file which has always been used as a reference for application development and test.
I suppose the access file I'm working with was created with a fairly old version of office: it has MDB extension.
The model generated from this file contains a table whose boolean fields have been marked as primary key even though they aren't part of primary key of the table in the database.
So I tried editing the table removing some fields, and I got another strange result generating again the model: the boolean fields are not part of primary key anymore, but the fields that are part of the primary key are not defined as such in the model, only one of four fields has been marked as primary key.
I am not able to understand why there is this behaviour, so I attach here the two database file and the screenshots of the models generated from them.
I used Microsoft JET 4.0 as a provider.

test_db_files.zip

Hi,
thank you for this great peace of Software, I was able to use für EF 6 in .Net Core.
Now I need to scaffold an old Access DB. For this I tried to install the SW according to docs and #31. But no success.
I installed the keys using the attached file:
jetframeworkVS.reg.txt

To load the private registry I followed this advice: https://visualstudioextensions.vlasovstudio.com/2017/06/29/changing-visual-studio-2017-private-registry-settings/
But VS 2019 crashed during startup complaining about the private registry
ActivityLog.xml.txt

I found this which may help to develop a solution for VS2019: https://www.powershellgallery.com/packages/WintellectPowerShell/4.0.0.1/Content/WintellectPowerShellHelper%5CWintellectPowerShellHelper%5CPrivateRegistry.cs

Hope someone can give me some glue how to handle that.
regards
Josef

There is a solution here for Entity Framework:
https://stackoverflow.com/questions/38996810/in-ef-code-first-is-there-a-data-annotation-for-description-field

But the query (sp_addextendedproperty) is not valid for MS-Access. Any solution?

I'm using .NET API and have tested writes to Access using the Siege tool. I kept getting update failures due to locks on the access mdb file. the access MDB registry shows 500 retry counts setup. I also looked at setting mode to force record level locking and optimistic locking but that is a bit tricky unless I go right to the registry and even then It was not going to be deterministic so I put in code to do locking at the .Net level and it worked fine. Even then it had performance challanges as you can see from the numbers.

The code is basically doing a direct write of 15 inserts into a table.

Here's are some 500 errors. I did not get them all the time, but more often than not.

`
Macs-MBP-2:TabletTests ray$ siege --rep=2 --concurrent=2 --content-type="text/plain" "http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post POST < RankedResults_1.asc"
[alert] Zip encoding disabled; siege requires zlib support to enable it
** SIEGE 4.0.2
** Preparing 2 concurrent users for battle.
The server is now under siege...
HTTP/1.1 500 120.37 secs: 36 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post
HTTP/1.1 200 120.40 secs: 0 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post
HTTP/1.1 500 63.13 secs: 36 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post
HTTP/1.1 200 63.42 secs: 0 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post

Transactions: 2 hits
Availability: 50.00 %
Elapsed time: 184.16 secs
Data transferred: 0.00 MB
Response time: 183.66 secs
Transaction rate: 0.01 trans/sec
Throughput: 0.00 MB/sec
Concurrency: 1.99
Successful transactions: 2
Failed transactions: 2
Longest transaction: 120.40
Shortest transaction: 63.13
`
After I put in the following code:

'
lock (_scoredEventLock)
{
if (!_saveScoredEventsDone)
{
showData.SaveChanges();
_saveScoredEventsDone = true;
}

            }

'

It worked, but with some interesting times on the start where it took 2+ seconds for the first write to the db for each user, then only .12 for the second.

CORRECTION: It didn't work!!! See my comment below on the alternate approach I took and am taking.

`
Macs-MBP-2:TabletTests ray$ siege --rep=2 --concurrent=2 --content-type="text/plain" "http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post POST < RankedResults_1.asc"
[alert] Zip encoding disabled; siege requires zlib support to enable it
** SIEGE 4.0.2
** Preparing 2 concurrent users for battle.
The server is now under siege...
HTTP/1.1 200 2.19 secs: 0 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post
HTTP/1.1 200 2.19 secs: 0 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post
HTTP/1.1 200 0.12 secs: 0 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post
HTTP/1.1 200 0.12 secs: 0 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post

Transactions: 4 hits
Availability: 100.00 %
Elapsed time: 2.81 secs
Data transferred: 0.00 MB
Response time: 1.15 secs
Transaction rate: 1.42 trans/sec
Throughput: 0.00 MB/sec
Concurrency: 1.64
Successful transactions: 4
Failed transactions: 0
Longest transaction: 2.19
Shortest transaction: 0.12
`

Upped it to ten users

`
Macs-MBP-2:TabletTests ray$ siege --rep=2 --concurrent=10 --content-type="text/plain" "http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post POST < RankedResults_1.asc"
[alert] Zip encoding disabled; siege requires zlib support to enable it
** SIEGE 4.0.2
** Preparing 10 concurrent users for battle.
The server is now under siege...
HTTP/1.1 200 0.25 secs: 0 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post
HTTP/1.1 200 0.28 secs: 0 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post
HTTP/1.1 200 0.33 secs: 0 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post
HTTP/1.1 200 0.51 secs: 0 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post
HTTP/1.1 200 0.59 secs: 0 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post
HTTP/1.1 200 0.60 secs: 0 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post
HTTP/1.1 200 0.71 secs: 0 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post
HTTP/1.1 200 0.88 secs: 0 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post
HTTP/1.1 200 0.63 secs: 0 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post
HTTP/1.1 200 0.55 secs: 0 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post
HTTP/1.1 200 0.73 secs: 0 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post
HTTP/1.1 200 1.20 secs: 0 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post
HTTP/1.1 200 1.62 secs: 0 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post
HTTP/1.1 200 1.29 secs: 0 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post
HTTP/1.1 200 0.88 secs: 0 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post
HTTP/1.1 200 1.07 secs: 0 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post
HTTP/1.1 200 63.31 secs: 0 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post
HTTP/1.1 200 63.60 secs: 0 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post
HTTP/1.1 200 64.35 secs: 0 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post
HTTP/1.1 200 63.15 secs: 0 bytes ==> POST http://192.168.1.52:52017/EquusEdgeHandhelds/rest/Results/post

Transactions: 20 hits
Availability: 100.00 %
Elapsed time: 65.40 secs
Data transferred: 0.00 MB
Response time: 13.33 secs
Transaction rate: 0.31 trans/sec
Throughput: 0.00 MB/sec
Concurrency: 4.08
Successful transactions: 20
Failed transactions: 0
Longest transaction: 64.35
Shortest transaction: 0.25
`

When I do a stopwatch on the the time to SaveResults I get:

2017-08-28 11:15:40.5592|INFO|GlobalFWServerLog|Save Results Elapsed Time: 373 ms
2017-08-28 11:15:40.5592|INFO|GlobalFWServerLog|Save Results Elapsed Time: 363 ms
2017-08-28 11:15:40.5782|INFO|GlobalFWServerLog|Save Results Elapsed Time: 392 ms
2017-08-28 11:15:40.5782|INFO|GlobalFWServerLog|Save Results Elapsed Time: 110 ms
2017-08-28 11:15:40.6072|INFO|GlobalFWServerLog|Save Results Elapsed Time: 169 ms

I can't seem to find references to pooling in the code. I was wondering if connection pooling was supported by the jet provider. According to Programming Entity Framework book pg 565 and [MS Pooling Referece](https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling EF)
It's my understanding that the dealing with of connection creation is left to connection pooling support in the provider.

Thanks in Advance,
Ray Trask