Support client evaluation when store evaluation is not appropriate

[ajcvickers]

This has come up a lot with discussion of value conversions (#242) but is also an issue with the SQLite ADO.NET provider (e.g. #10249 and #10198) and will be an issue for Oracle since it has a limited number of column types just like SQLite.

Things to consider:

• Developers may want:
  • The sort order to be as it would be if run in .NET on the client
  • The sort order to be as it would be in the server, understanding that this is different due to
    • Conversion of values or value format
    • Different semantics on the server (e.g. collation)
  • Any sort order as long as it is deterministic
    • Even if it is less common for developers to want this, EF still often needs this
• Forcing sorting on the client can have a big perf impact
• Anything done to change this by default would be a breaking change--especially important for SQLite cases
• In some cases it is possible to change the conversions to be order-preserving, but this often results in unnatural data in the database
• Even if it cannot be guaranteed that order will be preserved, it could be that ordering is preserved for the subset of values actually used--consider positive/negative number spaces when converting to strings or byte arrays.

Things we could do:

• Warn when this may be an issue
• Allow developers to pick type mappings/conversions that preserve order
  • Decide whether this is a must or a preference. That is, if EF can't ensure order is preserved, then either:
    • Continue with a warning and use non-order preserving mapping with store ordering
    • Continue with a warning, but force ordering to be done client side
    • Throw and require that the developer make an explicit choice of how to proceed by configuring differently
• Allow developers to use client-side ordering. This could be
  • Opt-in for any ordering
  • Automatically opted in when EF can't guarantee that order is preserved, but with a way to opt-out

[ajcvickers]

Additional note from #10198: .NET ordering of GUIDs is different from SQL Server ordering of GUIDs.

[ajcvickers]

Notes from triage:

• Since there are already cases of order not being preserved even without any value conversions, it means that this is not an issue limited to value conversion and likely needs a solution in general type mapping rather than just in value conversion.
• There are different things we could do, but it's not clear exactly when we should:
  • Force client eval
  • Use unnatural but order-preserving converters
  • Use store-side conversions, similar to what has been discussed for spatial types
  • Generate warnings or errors
• There are other similar issues such as equality comparisons which may need similar treatment

Base on this, we will not build any of this explicitly into value converters and we are moving this issue to the backlog to consider it holistically in a future release.

[ajcvickers]

Also consider #10534.

[divega]

Note for triage: TL;DR: Maybe it is just me, but it seems this issue has become too general and it includes some things we should keep in mind for 3.0.

Removing it from the backlog milestone so that we can char about it in triage.

Some of the things I have seen linked to this issue:

• Inconsistent order comparability between server and client, mostly caused by value conversions (what the issue was originally about)

• Types and operations for which SQLite returns incorrect results (already fixed with client evaluation) SQLite Query: Should we be translating decimal/TimeSpan operations? #10534

• Warnings for order comparisons of enums converted to ints are spurious, but we closed by-design, and then we promised we will do something better in 3.0 because having a global way to override the warning is far from ideal, but I am not sure what the thing that we will do in 3.0 is 😄 Enum inequality comparison triggers query warning #12507

• Apparently a "remove RemoveConvert" case for SQLite Where(x => x.Price > -1) comparisons with decimal Price yields incorrect results, still open for 3.0 SQLite Query: Cast from decimal to double not translated #10642

[ajcvickers]

Added reference to this issue from guiding principles (#12795) issue.

[smitpatel]

This would require from input from TypeMapping on ordering, (which should also take care of converters). For now, I believe query should treat them as if they are correct ordering with warning perhaps.

[ajcvickers]

Notes from planning:

• We will add ordering metadata to value conversions, which will then be available on the type mapping
• If conversion is not order-preserving, then we throw if an order-based operation is used.
  • For example less than, greater than, min, max.
  • This could include order-by, but note, that commonly any fixed ordering can fine.
• Add API to allow these operations anyway

[ajcvickers]

Closing this in favor of #15979 and #15978 since we are not doing client evaluation (except in final projection) in 3.0