1

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:

  • entity.Property(e => e.EndTime).HasDefaultValueSql("#01.01.1900 15:30:00#");
  • entity.Property(e => e.EndTime).HasDefaultValueSql("#01.01.1900 15:30#");
  • entity.Property(e => e.EndTime).HasDefaultValueSql("#15:30:00#");
  • entity.Property(e => e.EndTime).HasDefaultValueSql("#15:30#");
  • entity.Property(e => e.EndTime).HasDefaultValueSql("#01/01/1900 03:30:00#");
  • entity.Property(e => e.EndTime).HasDefaultValueSql("#01/01/1900 03:30#");
  • entity.Property(e => e.EndTime).HasDefaultValueSql("#03:30:00#");
  • entity.Property(e => e.EndTime).HasDefaultValueSql("#03:30#");
  • entity.Property(e => e.EndTime).HasDefaultValue(new TimeSpan(15,30,0));
  • entity.Property(e => e.EndTime).HasDefaultValue(new TimeSpan(3,0,0));

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:

  • Am I wrong with my assertion that this is possible in general?
  • Is the behaviour implemented in the EF-Core Jet provider wrong?
  • And most importantly: How can I get this working?

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

  • DateTime I can indeed call Update-Database successfully, but have no time values, only date
  • TimeSpan the Update-Database call fails (I suspect that Access does not like dates with time)
  • HasDefaultValueSql("#03:30:00#") I also get an exception when calling UpdateDatabase

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

Tobias
  • 78
  • 7
  • You're certainly not wrong on that it's possible. I don't have an EF Core project ready, but try assigning a numeric value. Access expresses dates as whole days + fractions for hours, minutes and seconds, so 15:30 corresponds to 15.5/24 = 0.6458333 – Erik A Nov 03 '20 at 12:51
  • `#12.30.1899 03:30:00#` is not a valid syntax for Access. Try: `#12/30/1899 03:30:00#`. – Gustav Nov 03 '20 at 13:41
  • 1
    @Gustav thank you for pointing that out, the CultureInfo of my application seemed to be the wrong one (but I think the EF Core Jet should not operate on my CultureInfo...). But seemingly the time 03:30:00 can also not be parsed and raises an syntax error exception. If I test this with MsAccess I get the same error messages. But in there using #03:30:00# works atleast. – Tobias Nov 03 '20 at 15:41
  • 1
    @Tobias Please open an issue on our [repository](https://github.com/bubibubi/EntityFrameworkCore.Jet) about it (just copy the markdown from here). I'll take it from there. Thanks! – lauxjpn Nov 28 '20 at 22:52

3 Answers3

0

The one, I would believe it could be, is:

entity.Property(e => e.EndTime).HasDefaultValue(new DateTime(1899, 12, 30, 15, 30, 0));

as it should be a DateTime value, not a TimeSpan.

But it all comes down to, if the OLEDB driver supports it, which I don't know.

Gustav
  • 53,498
  • 7
  • 29
  • 55
  • 1
    This works if I change my culture to an invariant but I am still missing the time values in the default. (This is due to the EF Core Jet implementation) – Tobias Nov 04 '20 at 07:41
0

I think the provider might be wrong.

I'm not sure how to apply this to your code, but something to consider/try, if possible: when I add a date I use ToString().

...
// ToString() removes the milliseconds which must be done to accomodate Access db.
cmd.Parameters.AddWithValue("MyField", DateTime.Now.ToString()); 
...

It's not the same as yours because I'm using DateTime.Now but maybe adding it would help??

wazz
  • 4,953
  • 5
  • 20
  • 34
0

The problem was indeed a combination of multiple factors, including an issue in the provider which was fixed after a report in the github repository.

Tobias
  • 78
  • 7