search

CirrusRedOrg / EntityFrameworkCore.Jet

Entity Framework Core provider for Access database
MIT License
138 stars 38 forks source link

Access database cannot create table with default datetime in migration #80

Closed xoniuqe closed 3 years ago

xoniuqe commented 3 years ago

(Copied from StackOverflow: https://stackoverflow.com/q/64663041/12767145) I have some trouble getting the migrations to create default datetime values for my tables. I am using the EF Core Jet provider and need to use MS Access-MDB-Files.

My Connection string is the following: 

Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=4;User ID=Admin; Data Source=<my-mdb-file.mdb>

I want to set the default datetime of an property to the time "15:30". I tried the following things:

And always get the following error message:

System.Data.OleDb.OleDbException (0x80040E14): Syntax error in CREATE TABLE statement.

Then I dug into the source code of EntityFrameworkCore.Jet and found in the following comment in the class JetMigrationsSQLGenerator: // Jet does not support defaults for hh:mm:ss in create table statement

But in our old code base we do exactly this with an ADO-Driver (it is an legacy Delphi application).

So I have the following questions:

Edit: To clarify my steps, I always deleted the Migrations folder after changing the model builder configuration and used the functions Add-Migration <MigrationName>and Update-Databaseto test this.

Edit 2: It seems like the comment from the EF Core provider is not totally wrong. Combined datetimes like #12.30.1899 03:30:00# which get created by using TimeSpan are not supported by Access. But if enter the create statement into Access directly and just use the time (#03:30:00#) it works. But I cannot get EF Core to create the statement like this.

Edit 3: In the cases where I used the function HasDefaultValueSqlthe reason for the wrong syntax seems to be an bug in the EF Jet Provider. When this function is used the created SQL statement ends with DEFAULT ('#<value>#'), the problem with this is that the parenthesis are not accepted. This seems to be a know issue.

Edit 4: There was an issue concerning my applications culture. I changed it and now I can get EF Core to generate DateTimes successfully, but these are missing the time values due to the implementation of the Jet-provider. So the current situation is as follows: If I use

I think that the EF Core Jet Provider is the problem here, but I am not totally convinced.

@lauxjpn: I am the poster of this issue on SO, and you commented I should open up an issue, but personally I think it is tied to #18 because it is possible to set an default time value with the HasDefaultValueSql if I use the proposed change in #18.

lauxjpn commented 3 years ago

There are multiple underlying issues involved here.

First, only the following #value# syntax will work for datetime values in Jet SQL in general (there are minor variations, e.g. a leading 0 can be omitted):

International style:

#1987-12-31 23:59:59#
#1987-12-31#
#23:59:59#
#23:59#

US style:

#12/31/1987 23:59:59#
#12/31/1987#
#23:59:59#
#23:59#

(MS Access does like to show date/time values in the users local format, but this is just how it is displayed.)

I should also mention, that I am only talking about the current 3.1.0-alpha.3 here. With that out of the way, lets take a look at the issues involved.

HasDefaultValueSql() uses incorrect syntax

You are correct in your assessment, that HasDefaultValueSql() should output the DEFAULT clause without wrapping the value statement with parenthesis. So we need to fix that. Once we would have fixed it, the behavior would be as using HasDefaultValue() in this case.

Jet (ADO) does not support time value syntax in DEFAULT clauses

Jet has always been very buggy. One of those bugs is, that while it perfectly supports datetime values with a time component as a column default, its SQL parser responsible for translating the CREATE/ALTER TABLE statements apparently does not.

Our current solution to this issue has been, to not specify any time components at all for datetime default clauses. But of course this is suboptimal.

Jet just uses an 8 byte double value as the underlying data type of datetime, with a value of 1.0 representing exactly one day. So it is perfectly valid SQL (though not very readable) to express a datetime value as a double number. I did some testing and this also works as expected for datetime columns. As a positive side effect, using double values will allow us to save higher date/time precisions than seconds.

The negative side effect is readability. The following statement does work in normal Jet SQL:

#1987-12-31 23:59:59# + 0.000001

However, it does not work as a DEFAULT clause value.

Conclusion

So, here are the steps for us to do:

  1. Fix the HasDefaultValueSql() uses incorrect syntax issue (#18)
  2. Change the type mappings, so they use double values (either only for fractions of a second or for everything)
  3. Change the default value generation code (used for HasDefaultValue()), so it always uses a double value for date/time related type mappings.

Step 1 will only fix the general usage of HasDefaultValueSql(), because once its general syntax is correct, it is the users responsibility to supply a working SQL value.