Custom DB Functions for op_Equality are Ignored

[robert-io]

Custom DB Functions for op_Equality are Ignored

When i register a Translation for a DB Function for the op_Equality method it is not used by EF Core when generating the SQL query.

Given the op_Equality method is static i would expect the Translation function to be used when EF Core generates SQL

The following two Linq Queries should produce the same SQL but only the second works as expected:

var results = (
    from p in db.Person
    where p.BornOn == bornOn
    select p
).ToList();

var results = (
    from p in db.Person
    where DateTimeShell.Equals(p.BornOn, bornOn)
    select p
).ToList();

The DbContext (this code is very much a work in progress, but does produce the SQL i expect):

internal class TestContext : DbContext
{
    public TestContext(DbContextOptions<TestContext> options) : base(options) { }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        _ = (builder
            .Entity<Person>()
            .Property(e => e.BornOn)
            .HasConversion<DateTimeShellConverter>()
        );

        MapEquality(builder, typeof(DateTimeShell).GetMethod("op_Equality", new[] { typeof(DateTimeShell), typeof(DateTimeShell) }));
        MapEquality(builder, typeof(DateTimeShell).GetMethod("Equals", new[] { typeof(DateTimeShell), typeof(DateTimeShell) }));

        base.OnModelCreating(builder);
    }

    private void MapEquality(ModelBuilder builder, MethodInfo method)
    {
        var equals = (builder
            .HasDbFunction(method)
            .HasTranslation(DateTimeShellEqualityTranslation)
        );

        equals.HasParameter("left").Metadata.TypeMapping = new StringTypeMapping("NVARCHAR(200)", DbType.String);
        equals.HasParameter("right").Metadata.TypeMapping = new StringTypeMapping("NVARCHAR(200)", DbType.String);
    }

    private SqlExpression DateTimeShellEqualityTranslation(IReadOnlyList<SqlExpression> args)
    {
        var leftArgument = args.First();
        var rightArgument = ((SqlParameterExpression)args.Skip(1).First()).ApplyTypeMapping(leftArgument.TypeMapping);

        var equals = new SqlBinaryExpression(
            operatorType: ExpressionType.Equal,
            left: ConvertJsonToDateTime(leftArgument),
            right: ConvertJsonToDateTime(rightArgument),
            type: typeof(bool),
            typeMapping: new BoolTypeMapping("BIT", DbType.Boolean)
        );

        return equals;
    }

    private SqlExpression ConvertJsonToDateTime(SqlExpression arg)
    {
        var getCachedUtc = new SqlFunctionExpression(
            functionName: "JSON_VALUE",
            arguments: new SqlExpression[] {
                arg,
                new SqlConstantExpression(
                    Expression.Constant("$.CachedUtc"),
                    new StringTypeMapping("NVARCHAR(20)", DbType.String)
                )
            },
            nullable: true,
            argumentsPropagateNullability: new[] { false, false },
            type: typeof(string),
            typeMapping: new StringTypeMapping("NVARCHAR(4000)", DbType.String)
        );

        var convertToDateTime = new SqlFunctionExpression(
            functionName: "CONVERT",
            arguments: new SqlExpression[] {
                new SqlFragmentExpression("DATETIME2"),
                getCachedUtc,
                new SqlConstantExpression(
                    Expression.Constant(127),
                    new IntTypeMapping("INT", DbType.Int32)
                )
            },
            nullable: true,
            argumentsPropagateNullability: new[] { false, false, false },
            type: typeof(DateTime),
            typeMapping: new DateTimeTypeMapping("DATETIME2", DbType.DateTime2)
        );

        /* CONVERT(DATETIME2, JSON_VALUE([arg],'$.CachedUtc'), 127) */
        return convertToDateTime;
    }

    public DbSet<Person> Person { get; set; }

    private class DateTimeShellConverter : ValueConverter<DateTimeShell, string>
    {
        public DateTimeShellConverter() : base(
            v => StrongTypeConvertToProvider(v),
            v => StrongTypeConvertFromProvider(v)
        ) { }

        private static string StrongTypeConvertToProvider(DateTimeShell value)
        {
            return JsonConvert.SerializeObject(value, Formatting.None);
        }

        private static DateTimeShell StrongTypeConvertFromProvider(string value)
        {
            if (string.IsNullOrWhiteSpace(value))
            {
                return null;
            }

            var result = JsonConvert.DeserializeObject<DateTimeShell>(value) ?? throw new InvalidCastException();

            if(result.Local == DateTime.MinValue || string.IsNullOrEmpty(result.TimeZoneId) || result.CachedUtc == DateTime.MinValue)
            {
                return null;
            }

            return result;
        }

        private static object? WeakTypeConvertToProvider(object? value)
        {
            if(value is null)
            {
                return null;
            }

            if(value is DateTimeShell dateTimeShell)
            {
                return StrongTypeConvertToProvider(dateTimeShell);
            }

            throw new InvalidCastException();
        }

        private static object? WeakTypeConvertFromProvider(object? value)
        {
            if (value is null)
            {
                return null;
            }

            if (value is string dateTimeShell)
            {
                return StrongTypeConvertFromProvider(dateTimeShell);
            }

            throw new InvalidCastException();
        }

        public override Func<object?, object?> ConvertToProvider => WeakTypeConvertToProvider;

        public override Func<object?, object?> ConvertFromProvider => WeakTypeConvertFromProvider;
    }
}

The DateTimeShell class:

public class DateTimeShell : IEquatable<DateTimeShell>
{
    public DateTime Local { get; set; }
    public string TimeZoneId { get; set; }
    public DateTime CachedUtc { get; set; }

    public bool Equals(DateTimeShell? ritgh)
    {
        return CachedUtc.Equals(ritgh?.CachedUtc);
    }

    public override bool Equals(object? obj)
    {
        if (obj is DateTimeShell ritgh)
        {
            return Equals(ritgh);
        }

        return false;
    }

    public override int GetHashCode()
    {
        return CachedUtc.GetHashCode();
    }

    public override string ToString()
    {
        return JsonConvert.SerializeObject(this, Formatting.None);
    }

    public static bool Equals(DateTimeShell left, DateTimeShell right) => StaticCompareTo(left?.CachedUtc, right?.CachedUtc) == 0;

    private static int StaticCompareTo(DateTime? left, DateTime? right)
    {
        if (left is null && right is null)
        {
            return 0;
        }

        if (left is null)
        {
            return -1;
        }

        if (right is null)
        {
            return 1;
        }

        return left.Value.CompareTo(right.Value);
    }

    public static bool operator ==(DateTimeShell left, DateTimeShell right) => StaticCompareTo(left?.CachedUtc, right?.CachedUtc) == 0;

    public static bool operator !=(DateTimeShell left, DateTimeShell right) => StaticCompareTo(left?.CachedUtc, right?.CachedUtc) != 0;
}

The Person class:

public class Person
{
    public int PersonId { get; set; }

    public string Name { get; set; }

    public DateTimeShell BornOn { get; set; }
}

The table definition used by the above EF Core code:

CREATE TABLE [dbo].[Person](
    [PersonId]
        INT
        IDENTITY(1,1)
        NOT NULL,

    [Name]
        NVARCHAR(200)
        NOT NULL,

    [BornOn]
        NVARCHAR(200)
        NOT NULL,

    -- 127 = ISO8601 with time zone Z, yyyy-MM-ddThh:mm:ss.fffZ (no spaces)
    [BornOnUtc] AS CONVERT(DATETIME2, JSON_VALUE([BornOn],'$.CachedUtc'), 127) PERSISTED
);

CREATE INDEX [IX_dbo_Persons_BornOn] ON [dbo].[Person] ([BornOnUtc]);

Example JSON for the [BornOn] feild:

{"Local":"1969-04-04T16:50:00","TimeZoneId":"Europe/London","CachedUtc":"1969-04-04T15:50:00Z"}

Provider and Version Information

EF Core version: 6.0.7
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
Operating system: Windows 11
IDE: Visual Studio 2022 17.3.0 preview 2.0

[ajcvickers]

@robert-io We discussed this and we don't think this is a great idea. First, EF Core assumes that it understands the semantics of equality and this influences the shapes of queries generated. That is, equality is not just any other operation/function mapping, it is special. This is not something we will change since it would result in very poor generated queries if we cannot make this kind of assumption to simplify cases. EF Core also assumes equality semantics in change tracking and fixup.

Beyond this, there doesn't seem much value in explicitly attempting to target the equality operator, as opposed to the Equals method. However, this could probably be done by re-writing the expression tree before it is processed--see Interceptors for expression trees.

Overall, the most reliable approach here would probably to create a user-defined function mapping in EF that could then be used by EF Core without it making any assumptions about the semantics of this function.

[robert-io]

@ajcvickers thanks for the response, i'll take a look at interceptors, with any luck they will provide what i need.

For some background i am creating a custom data type and want to enable persisting to a data store like SQL Server, PostgreSQL, MongoDB etc whilst maintaining the same equality and comparison semantics in EF Core as we get in Linq to Objects.

So finding a method that works transparently would be ideal. If to get the benefit consumers of this data type need to use a set of custom methods it makes my data type a second class citizen that requires special knowledge to benefit from it.

What i'm thinking this would look like in the data store is a JSON object and a surrogate / computed field (using a type the data store understands) that provides equivalent equality and comparison semantics.

[roji]

@robert-io if you're simply representing some arbitrary .NET type as a database JSON type, then the LINQ equality operator simply gets translated as a SQL equality operator, which the database applies to the JSON values involved; I'm not sure why you'd need a custom equailty method here.

Also note the work planned for JSON support in EF 7.0, which should make it possible to easily map any .NET type to JSON without any special work.

More context on exactly why you think you need custom equailty may allow us to help you further.

[robert-io]

@roji the standard SQL equality is not going to work, it does not take into account the semantics of the data type, this will be a basic string comparison of the JSON and if the JSON fields are ordered different then quality will fail.

My problem is more a limitation on data stores like SQL Server than a problem with EF Core, for most of the use cases i have the default equality is perfectly suitable.

In my specific case i have created a type to represent Date & Time. The built in dotnet DateTime & DateTimeOffset types are not suitable and whilst very close to my requirement NodaTime does not quite cover it without a bit work, so i have encapsulated that behaviour.

Now i want to be able to use my type as if it is supported directly by the underline data store e.g. SQL Server, MongoDB etc.

This means queries like the following should just work

var results = (
    from p in db.Person
    where p.BornOn == bornOn
    select p
).ToList();

var results = (
    from p in db.Person
    where p.BornOn < someDateTime
    select p
).ToList();

var results = (
    from p in db.Person
    where p.BornOn > someDateTime
    select p
).ToList();

i don't want to use queries like:

var results = (
    from p in db.Person
    where MyDateTimeType.Equals(p.BornOn, bornOn)
    select p
).ToList();

var results = (
    from p in db.Person
    where MyDateTimeType.Compare(p.BornOn, someDateTime) < 0
    select p
).ToList();

var results = (
    from p in db.Person
    where MyDateTimeType.Compare(p.BornOn, someDateTime) > 0
    select p
).ToList();

When i build data access layers i make sure any IQueryable<T> exposed generates the same result as an in memory List<T> would for the same expression. This is not normally too difficult but some custom types take a lot of work.

And thanks for the JSON support in EF 7.0 heads up, i'll take a look.

[smitpatel]

If database equality operation is not supported on a given type which is mapped to database type, then it is not "native" support in database. It is by default becomes second-class citizen in database.

[roji]

To add to @smitpatel's comment above...

the standard SQL equality is not going to work, it does not take into account the semantics of the data type, this will be a basic string comparison of the JSON and if the JSON fields are ordered different then quality will fail.

That's certainly true, but what would you do instead? You could define a function which somehow reorders the entire JSON document string in the database, but that would likely be very expensive and cause database indexes to not get used.

In my specific case i have created a type to represent Date & Time. The built in dotnet DateTime & DateTimeOffset types are not suitable and whilst very close to my requirement NodaTime does not quite cover it without a bit work, so i have encapsulated that behaviour.

I'd be interested in hearing the exact problem you have with DateTime/DateTimeOffset, and especially with NodaTime (I've worked quite a lot on mapping these).

But in general, it's not really possible to define your own .NET type - including its own operations and semantics - and then expect that type to be mappable directly to your database... The database is a completely different environment, which knows nothing about either your type or its semantics. In other words, you generally have to adapt to the database's types and constraints, rather than expecting the database to adapt to yours.

When i build data access layers i make sure any IQueryable exposed generates the same result as an in memory List would for the same expression. This is not normally too difficult but some custom types take a lot of work.

This is specifically not something users should typically try to do. EF does attempt to translate LINQ queries so that they return the same results from the database as they would on in-memory collections, but there are some very important exceptions. One standard example is case-sensitive in string comparisons: while C# does case-sensitive matches, SQL Server is case-insensitive by default, and case-sensitivity is controlled via the column's collation rather than through the comparison operators/function as in .NET (the StringComparison parameter to string.Equals). There are some very good reasons for this discrepancy - databases typically manage lots of data, and indexes are crucial to querying speed, requiring the collation/case-sensitivity to be known in advance.

To summarize, I'd recommend re-examining your approach to how you're trying to use your database, and to not attempt to impose 100% .NET-compliant behavior on your database. That's not possible in many cases, and in many cases where it is, would result in very significant performance problems.

[robert-io]

@roji well thats a few good questions, i'll try to answer what i can and i'll stick with SQL Server as the data store.

What would i do instead of using DATETIME, DATETIME2 or DATETIMEOFFSET.

As with the example in my question I would use JSON and a persisted computed column + and index on the computed column.
This will give me what i need from an equality and ordering point of view.

The following SQL is what i'm aiming to get EF Core to generate (the WHERE and ORDER BY parts) and it uses the index so you don't get the performance hit

DECLARE @BornOn NVARCHAR(200) = '{"Local":"1966-07-07T16:50:00","TimeZoneId":"Europe/London","CachedUtc":"1966-07-07T15:50:00Z"}'

SELECT *
FROM [dbo].[Person]
WHERE CONVERT(DATETIME2, JSON_VALUE([BornOn],'$.CachedUtc'), 127) > CONVERT(DATETIME2, JSON_VALUE(@BornOn,'$.CachedUtc'), 127)
ORDER BY CONVERT(DATETIME2, JSON_VALUE([BornOn],'$.CachedUtc'), 127)

Whats wrong with DateTime/DateTimeOffset and NodaTime

This is will take far too long so i'm not going to dig into this one. I would like to say my type wraps the NodaTime ZonedDateTime adds IComparable and a few other behaviours that NodaTime make a choice for the consumer. So NodaTime does what i want with a bit of work, so i wrapped it in a new type. Long Live OOP.

IQueryable<T> and List<T> Compatibility

Whilst i don't see a question from you on this i would like to respond to your statement.

Yes i agree this is not typical and without care and attention can be a performance issue. I focus on getting it right then making it fast and sometimes its just not possible so a pragmatic approach is required.

Taking the example of a string and the differences between how SQL Server and dotnet compare and sort them.

• I create a type that matches the collation by wrapping a string, CultureInfo and CompareOptions, this means that the SQL query results are the same as i get in memory.
• I am aware that no all collations can be matched in dotnet.
• As this type is a thin wrapper around a string and EF Core can map this to SQL Servers =, >, < it has no performance impact on the SQL Query and you only pay the OOP cost in memory to ensure the correct behaviour is used.

Whilst my target is to make the in memory and data store behaviour the same its not always possible so i take a pragmatic decision at that point.

Wapping up

This is a research project for me and the result can be its not possible, but if i don't investigate i'll never know and never benefit from improving my code.

[roji]

@robert-io thanks for the added details, here are a few comments.

First, as I wrote above, work is underway on JSON support in EF 7.0; this should allow you to map a .NET POCO as a JSON document (e.g. to a SQL Server varchar column), and then generate queries with JSON_VALUE as you access members of that POCO in a LINQ query. I'm not 100% about datetime2 and CONVERT specifically, but that's definitely the general direction.

But stepping back, I'm a little bit confused here... The above simply shows you putting a string representation of a datetime2 into a JSON document, but then converting that into a datetime2 whenever you're actually accessing that value (e.g. in WHERE/ORDER BY clauses). In other words, you're just using the standard, native database's datetime2 type, but rather than storing it directly as a column, you want to embed it in a JSON document. There's nothing wrong with that, but you certainly haven't "created a type to represent Date & Time", as you've written above. In other words, the database use its regular semantics and behavior around ordering, comparison, equality checks for datetime2 - there's no custom behavior here.

I create a type that matches the collation by wrapping a string, CultureInfo and CompareOptions, this means that the SQL query results are the same as i get in memory.

Collations in the database may vary in a very significant way from the way things work in .NET. SQL Server may happen to be a bit closer (not sure), but if you move to other databases things would probably become even more different.

Whilst my target is to make the in memory and data store behaviour the same [...]

So it seems that you're departing from regular .NET ways of doing things so that you can more closely model the database behavior, i.e. replace plain string properties with some other custom type just so it can sort of represent the database collation concept.

I'm simply not sure why someone would go to great lengths for this, to the point of doing quite unnatural things on the .NET side. The point of LINQ is to allow expressing queries in a strongly-typed, C#-native way, not to have the same LINQ query execute 100% in exactly the same way across all possible LINQ providers. In short, I'd reconsider why exactly this is a goal for you; we usually see people going in this direction because they've decided to use in-memory testing and want the in-memory behavior correspond exactly to their database behavior. That is simply not possible to do for all cases, and we discourage this kind of testing approach (see docs). Other than testing, I really don't see why 100% identical behavior between in-memory and SQL behavior would be an absolute goal.

[robert-io]

Your right its not a new type in SQL Server but in Linq it'll be exposed as such, this is a pragmatic approach. I know i can't get exactly what i want because SQL Server doesn't support it. Once upon a time in SQL Server i could create CLR types but because that is not supported in AzureSQL (for reasonable security concerns) i'm not able to use them. Additionally its not an option for other data stores so i'm looking at ways to cover that gap.

The goal of any ORM like EF Core is to reduce the gap between the underlying data store and the native programming experience, in this case C#, VB.Net and F#.

With Date & Time essentially i need to store a 3 part tuple where the key data is the Local Time and Time Zone because UTC is a moving target relative to Local Time. I use the UTC value for filtering and ordering because that provides a consistent result accounting for different Time Zones. In a way i'm replicating the a STABLE function from PostgreSQL.

Multiple times a year the Time Zone rules somewhere on Earth change and as a result the UTC time for a given Local Time is changed and when it does i have a service to correct the values in the DB.

There are a few things that just won't map from my date & time type to some SQL expression such as functions that add/subtract time, convert to another time zone etc.

Also i am in no way departing from the .NET way of doing things, C# is a general purpose Object Orientated programming language with the .NET Runtime providing key services. The .NET way is the Object Orientated way and defining types to encapsulate the behaviour you need is the Object Orientated way and is the .NET way.

I like C# and for the most part i don't have a need to define new Value Types or similar because the .NET CLR/CTS provide almost everything i need / want in a well thought out way.

[robert-io]

For reference the TIMESTAMP WITH TIME ZONE in PostgreSQL is so very close to what i want but instead of keeping the Local Time stable PostgreSQL keeps the UTC time stable meaning when the rules change the Local Time retrieved from PostgreSQL will be different from the value inserted.

[roji]

The goal of any ORM like EF Core is to reduce the gap between the underlying data store and the native programming experience, in this case C#, VB.Net and F#.

We're very clear about EF not trying to make LINQ queries behave in exactly the same way across databases, or across your database and .NET. We of course do this by default where it makes sense, but reaching 100% is a very explicit non-goal which I believe is a misguided goal.

Re your date & time, I don't disagree with storing local time, time zone, and UTC (see this post I wrote on this). The aspect I find problematic is trying to enclose the 3 values in a single .NET type, and then expecting to be able to seamlessly map that type to your database somehow, with all its semantic behavior. The easy, recommended way would simply to have three properties on your EF entity type - a local timestamp, a UTC timestamp, and a timezone string (note that the UTC timestamp can also be a computed column based on the other two). You can have a single POCO type on the .NET side that's constructed from these elements (e.g. via an unmapped property that's backed by the 3 mapped properties), but I don't think it's viable to try to map that POCO type directly to the database.

[...] i am in no way departing from the .NET way of doing things [...]

Of course that's a somewhat subjective matter. If you really are considering replacing your string properties with some other custom type in order to include a collation, so that they sort of correspond to a database textual column, I personally think that's departing from simple, standard .NET programming (and will complicate things). But of course if that works for you, then by all means.

[roji]

For reference the TIMESTAMP WITH TIME ZONE in PostgreSQL is so very close to what i want [...]

I'd advise looking again. PostgreSQL TIMESTAMP WITH TIME ZONE is nothing more than a timestamp that's UTC by convention, no actual time zone is stored in the database. If you need to store a timezone, you must have a separate string column to hold it.

[robert-io]

We're very clear about EF not trying to make LINQ queries behave in exactly the same way across databases, or across your database and .NET. We of course do this by default where it makes sense, but reaching 100% is a very explicit non-goal which I believe is a misguided goal.

As a rule i agree, but this is a research project for me so i'm looking to find out how close i can get. The saying "perfect is the enemy of the good" is appropriate here. For any projects i have deliverables beyond my own curiosity i take a much more pragmatic approach and would not be trying this.

Re your date & time, I don't disagree with storing local time, time zone, and UTC (see this post I wrote on this).

I like your post, it very much covers the issue i'm working on.

The aspect I find problematic is trying to enclose the 3 values in a single .NET type, and then expecting to be able to seamlessly map that type to your database somehow, with all its semantic behavior.

The .NET type only contains a ZonedDateTime, the values to persist are derived from that. These 3 value map exactly to the 3 values in your blog post where the UTC value is there to order the rows by their occurrence on the global timeline. When the data is read only the Local Time and Time Zone will be used to build the .NET type. Unlink in your blog post i'm looking to move recalculating UTC out of the DB.

I choose JSON to represent the data so it could be seamlessly mapped between the DB and the .NET type. This works where the value is not part of the WHERE or ORDER BY clauses.

This isn't a problem with ADO.Net and Dapper as the complexity can be abstracted away, but when you have an IQueryable<T> where T has a property with my Date & Time type i can't see how.

The easy, recommended way would simply to have three properties on your EF entity type - a local timestamp, a UTC timestamp, and a timezone string (note that the UTC timestamp can also be a computed column based on the other two). You can have a single POCO type on the .NET side that's constructed from these elements (e.g. via an unmapped property that's backed by the 3 mapped properties), but I don't think it's viable to try to map that POCO type directly to the database.

If it was easy it wouldn't be worth doing. Broadly i think my goal is not achievable but it has been a great opportunity to learn about NodaTime and EF Core.

I'd advise looking again. PostgreSQL TIMESTAMP WITH TIME ZONE is nothing more than a timestamp that's UTC by convention, no actual time zone is stored in the database. If you need to store a timezone, you must have a separate string column to hold it.

Good point, it still has the same issue though reading after the rules have changed and converting it to Local Time will not give you the correct result.

[roji]

For a research project, indeed it makes sense to see where one can go and explore atypical settings, and if you learned a lot from it, that's already a great thing.

I'll just stress again that for a real/production system, I wouldn't go in this direction. I also wouldn't represent a complex type in JSON on the database side just so that it could be mapped to a single POCO on the .NET side - mapping to JSON introduce complexities and limitations, and I don't believe there's a justification for them for this kind of thing. It's really easy to do a classical mapping to 3 .NET properties; it may not be 100% how we'd like to model things OOP-wise, but that's the thing to do in the general case.