9.0.0-beta.1

EFCore.Jet Beta 1

Welcome to the first beta release of EFCore.Jet 9. This is targeting EF Core 9 RTM and will run on .Net 8+.

New Features

• Updated to EF Core 9 RTM
• Add Standard Deviation and variance functions to EF.Functions (#269)
• Add translations for Math.Min, Math.Max, Greatest, Least (#270)
• Update the History Repository to use the new locking mechanism

Bug fixes

• Tests: Multiple fixes due to optimization improvements made by EF Core itself
• Don't do any conversion in sql for byte array (#266)
• Some identity properties on a class mapped to json didn't get the correct ValueGenerated value
• Removed the handling of GO in the SQL of SqlOperation migrations. GO is a Sql Server specific command and is irrelevant to Jet

9.0.0-alpha.1

EFCore.Jet Alpha 1

Welcome to the first release of EFCore.Jet 9. This is targeting EF Core 9 preview 7 and will run on .Net 8+.

New Features

• Updated to EF Core 9 #251
• Create sequential GUID following the UUID Version 7 definition #254
• Guids for the primary key are generated client-side now. There is only a way to get a numerical primary key last inserted value in Jet. Hence anything depending on that would never work as it couldn't query the last inserted Guid. #257
• TimeOnly.FromDateTime and TimeOnly.FromTimeSpan is now translated #259
• SearchConditionExpressionVisitor: From improvements made to the SQL Server version, add them to here. #255
  • <> TRUE becomes = FALSE
  • Use bitwise XOR (BXOR in Jet) for multiple optimizations with nullable boolean expressions
  • Thanks @ranma42. Your improvements work nicely here as well
• The sort order for Indexes is now added. Part of #256
  • We now generate SQL code to create the indexes with the respective sort order for each index column.
  • The Database Model is now set properly when reading the details of the database into the model

Bug fixes

• Tests: Multiple fixes due to optimization improvements made by EF Core itself
• Fix shared column validating when using view splitting #258
• Improvements to the migrations sql #256
  • Updating a column has the following improvements
    • Don't generate an unnecessary ALTER TABLE
    • If the column is being made non-nullable (when originally null), create an UPDATE statement to set the value of that column to the default value for those where the current value is NULL
• Null Semantics Tests: A test used the parent BoolTypeMapping and true was translated as 1. Jet uses -1 for true so override default behaviour to produce the sql literal of TRUE. Also adds some further tests to the overrides so we can test the sql #260
• Use the lowest possible value of DateTime (1 Jan 100) when using a FirstOrDefault. A 0 in Jet/OLE Automation is not actually the lowest possible value
• The type mapping for the translation of TimeOfDay was returning a DateTime and not TimeSpan
• When reading a value for the TimeSpan and TimeOnly don't convert the default 0 to the c# 0 like we do for DateTime
• A primary key with type of short can also be mapped to a counter type if it needs to be an auto incrementing key

Full Changelog: v8.0.0...v9.0.0-alpha.1

8.0.0

The RTM of EFCore.Jet 8 is now out.

Key features/Improvements

• Updated to EF Core 8. Requires EF Core 8. Currently this requires .Net 8 to run
• Add support for DateOnly/TimeOnly
• Very limited computed column support. If the computed column value is a simple constant, we can rewrite it to the default value of the column
• Add support for the Math Degrees to Radions and Radians to Degrees. Done by first principle as no inbuilt SQL function
• Very limited support for primitive collections and Json types. The support is only for reading/writing the whole value at once. Querying into it for a specific element is not supported.
• Option to ignore MSys (system) tables when scaffolding. Add IgnoreMsys=YES; to the connection string

Notable bug fixes

• Fix DateTimeOffset
  • Regression in 7.0 series where the DateTimeOffset was being written as Local time and not UTC
  • When reading the value and converting from a DateTime, the Offset value ended up being implicitly set to the systems local time zone offset. A DateTimeOffset from a UTC value should actually have an offset of 0. This has been wrong since the 2.2 series
• Better support for byte arrays (#228). Most functions work now when working on byte arrays except for getting its length. Due to Jet treating it all as unicode strings, the default length function will return the length as multiples of 2. There is a workaround that will work in most cases. Please use the specific EF Function instead: EF.Functions.ByteArrayLength
• Workaround for ValuesExpression (#222). The VALUES clause was able to generate a constant table (and with multiple rows). Use a work around with a SELECT for each row combined with a UNION
• QueryableMethodTranslatingExpressionVisitor: TranslateElementAtOrDefault we do not throw on this. Was thought to be Json related at one point but this is actually for translating the Linq ElementAt. Basically does a Skip(x).Take(1)
• The static Guid.NewGuid is translated client side as there is no equivalent function in Jet
• The default C# DateTime (0001-01-01 00:00L:00) is mapped to the 0 value for Jet (30/12/1899 00:00:00)
• TPC Mapping: EF Core requires all entities in a hierarchy to have a unique value. This is normally set up via sequences in the databases that support it. For those that don't (Sqlite and Jet) the OnAdd value generated strategy is turned to have the Id just a normal integer. The client needs to handle keeping the id's unique. Everything else works
• Add more support for UPDATE and DELETE queries, especially with those referencing multiple tables and joins
• Multiple fixes with UNION queries where we had NULL constant values in the SQL being unioned against numeric columns.. This either gave a data type mismatch or produced weird results (such as a binary type value within a string type output instead of a numerical output). Casting the NULL constant to a variant generally fixed it
• When generating sets (UNION, INTERSECT) it didn't get to the part where it generated the SQL for the Alias
• When translating IndexOf, it used the INSTR Sql method. According to the docs the first parameter is the start index and is optional but found some tests that didn't like that. Including all parameters fixed that

For more detail see the notes for each of the pre-release versions

8.0.0-rc.1

RC 1 is out now

Bug fixes

• Test fixes
• DateTimeOffset: UtcNow passes in the offset from utc acquired in the client so that it can convert Now() to utc
• Option to ignore MSys (system) tables when scaffolding. Add IgnoreMsys=YES; to the connection string
• Better support for byte arrays (#228). Most functions work now when working on byte arrays except for getting its length. Due to Jet treating it all as unicode strings, the default length function will return the length as multiples of 2. There is a workaround that will work in most cases. Please use the specific EF Function instead: EF.Functions.ByteArrayLength
• Use the correct precision in string literal for double (15) and float (7). This matches how they are saved. If passed in with more precision when reading back the saved value, they would not be equal
• Keys are allowed to be case-insensitive (same as sql server). Add a Case Insenstive comparer to the JetStringTypeMapping
• Parameter name was the same when multiple contains/startswith/endswith was used. Hence one of them did not use the correct pattern. See also issue 32432 in efcore
• Workaround for ValuesExpression (#222). The VALUES clause was able to generate a constant table (and with multiple rows). Use a work around with a SELECT for each row combined with a UNION

8.0.0-beta.1

Beta 1 is now out.

This is compatible is dependent on at least 8.0.1 of EF Core and only runs on .Net 8

New features

• Add back in the SkipTake Collapsing feature. We can simplify if the Limit is known to be a 0

Bug fixes

• Tests: Remove unsupported tests
• Tests: More test fixes
• When using the option to use short strings as default is set, the length value (via attribute or fluent api) was ignored when it was over 255. Upgrade the value to a long char in that case
• Fix Any,All,Contains with Many to Many relationships within a Where clause. Some queries have a SELECT 1 inside a WHERE NOT EXISTS clause which doesn't work. Having a column in the projection works
• When generating the update sql, the command to fetch the number of affected rows needs the ResultSetMapping.ResultSetWithRowsAffectedOnly value set as well
• Retrying Execution Strategy: Check the exception.HResult as well. Our test classes can't exactly mimic the exact way and put in into the Errors list
• Handle some more NULL constant in projection Jet quirks
• GetByte also needs hardening to handle some potential cases when the result is of type string
• Handle Skip...Take in its own post processor.
• Test Utilities: Improve the execution of a setup script
• Handle the find and throw of a split query with offset and not order by in our own SkipTake postprocessor rather than its own postprocessor
• DateTime.Today was not translated properly. It is a static member and not under an instance as originally expected
• QueryableMethodTranslatingExpressionVisitor: TranslateElementAtOrDefault we do not throw on this. Was thought to be Json related at one point but this is actually for translating the Linq ElementAt. Basically does a Skip(x).Take(1)
• The static Guid.NewGuid is translated client side as there is no equivalent function in Jet
• The default C# DateTime (0001-01-01 00:00L:00) is mapped to the 0 value for Jet (30/12/1899 00:00:00)
• TPC Mapping: EF Core requires all entities in a hierarchy to have a unique value. This is normally set up via sequences in the databases that support it. For those that don't (Sqlite and Jet) the OnAdd value generated strategy is turned to to have the Id just a normal integer. The client needs to handle keeping the id's unique. Everything else works
• Expand the JetOrderByPostProcessor to be more general and de-duplicate more clauses in the ORDER BY and projection

8.0.0-alpha.2

Alpha 2 is now out.

This is compatible with the RTM version of EF Core 8 and only runs on .Net 8

New features

• Add support for DateOnly/TimeOnly
• Very limited computed column support. If the computed column value is a simple constant, we can rewrite it to the default value of the column
• Add support for the Math Degrees to Radions and Radians to Degrees. Done by first principle as no inbuilt SQL function
• Very limited support for primitive collections and Json types. The support is only for reading/writing the whole value at once. Querying into it for a specific element is not supported.

Bug fixes

• Multiple fixes with UNION queries where we had NULL constant values in the SQL being unioned against numeric columns.. This either gave a data type mismatch or produced weird results (such as a binary type value within a string type output instead of a numerical output). Casting the NULL constant to a variant generally fixed it
• Some string related functions can not accept NULL values for certain arguments. Added protections against those
• Test updates
• Jet does not support having subqueries in the order by clause. Lift these into into the table above so it has its own column that it can reference
• Added in a couple of extra expected warnings to match what the tests expect
• Tag lines and comments are removed from the SQL before executing
• Fixed the OnDelete convention
• Add more support for UPDATE and DELETE queries, especially with those referencing multiple tables and joins
• Fix DateTimeOffset
  • Regression in 7.0 series where the DateTimeOffset was being written as Local time and not UTC
  • When reading the value and converting from a DateTime, the Offset value ended up being implicitly set to the systems local time zone offset. A DateTimeOffset from a UTC value should actually have an offset of 0. This has been wrong since the 2.2 series

8.0.0-alpha.1

With the release of .Net RC 1 and EF Core 8 it is time to start work on a compatible version of EFCore.Jet

Alpha 1 is now out and available for testing. Most things should be working and I don't believe there is any significant regressions from v7

Some notable changes are:

Updated to .Net 8. Note that EFCore requires .Net 8 and is not able to run on a lower version
Updated tests to match. Generally along the lines of different optimizations applied
Uses the new way of doing StartwWith/EndsWith/Contains
Previously used to get a substring the length of the searched for string and compared on that. Now translates into a LIKE pattern
Fix formatting of a single byte in the SQL string literal
Using DISTINCT in the same select as TOP in Jet is mutually exclusive. Hence push the SELECT TOP statement into a subquery
Fix handling some versions of our generated SELECT TOP statement when it had the sum of 2 numeric constants
When generating sets (UNION, INTERSECT) it didn't get to the part where it generated the SQL for the Alias
When translating IndexOf, it used the INSTR Sql method. According to the docs the first parameter is the start index and is optional but found some tests that didn't like that. Including all parameters fixed that
There is still some work to go but some new EF Core 8 features do work. Some notes:

Complex types do work
DateOnly/TimeOnly still need to be implemented
Json support is obviously not working (Jet has no concept). Might be doable to handle this within EFCore.Jet by having the column has a text/memo with the full Json, the select/update statements read/write the full json but within EFCore.Jet we process it to return the required portion (or update).

7.0.3

v7.0.3

7.0.2

Minor update

• Changed some Fluent API methods: UseIdentityColumn(s) -> UseJetIdentityColumn(s) . This is needed so that we don't hit an ambiguous method when referencing Sql Server which has the name API extension

7.0.0

v7 RTM is out!!

Improvements:

• Library now targets net6.0 instead of net6.0-windows . While it still uses a lot of Windows functionality (DAO,ADOX,OleDb) this change allows you to reference it in another multi-platform package and only expose the EFCore.Jet functionality if running on Windows. Thanks @0xced
• Handle a left join that is immediately preceded by a cross join (otherwise known as cartesian product/join). For Jet the cross join needs to be pushed down into its own subquery before doing the left join
• Changed some handling of the Jet conversion functions CDbl, CInt, Clng etc. These functions do not accept NULL values (unlike SQL Server CAST), thus the inside expression needs to be NULL checked. Included some exceptions if we can pick up that the inside function will never produce a NULL then we can simplify and do without the NULL check.
• More test fixes