EF Core 8 Support

[ChrisJollyAU]

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).

[ChrisJollyAU]

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

[ChrisJollyAU]

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