search

dotnet / EntityFramework.Docs

Documentation for Entity Framework Core and Entity Framework 6
https://docs.microsoft.com/ef/
Creative Commons Attribution 4.0 International
1.62k stars 1.96k forks source link

Document the things needed to add an extension with type mappings #3500

Open juchom opened 2 years ago

juchom commented 2 years ago

Based on the excellent work of Shay Rojanski and Steve Rasmusmen to integrate NodaTime, I tried to do the same with Ulid.

The wip repo is here https://github.com/linoafr/UlidIntegration with unit tests if this can help.

Ulid api works almost like Guid api

Ulid.NewUlid() // Produce 01FJ407WVXP0N8JNEM763SW753
Ulid.Empty // Produce 00000000000000000000000000
Ulid.MinValue // Produce 00000000000000000000000000
Ulid.MaxValue // Produce 76EZ91ZPZZZZZZZZZZZZZZZZZZ

Ulid can be stored in a database as char(26) or binary(16).

I think the relevant code is here for char(26).

internal class UlidToStringValueConverter : ValueConverter<Ulid, string>
{
    private static readonly ConverterMappingHints DefaultHints = new(size: 26);

    public UlidToStringValueConverter() : this(null)
    {
    }

    internal UlidToStringValueConverter(ConverterMappingHints? mappingHints = null)
        : base(
                convertToProviderExpression: x => x.ToString(),
                convertFromProviderExpression: x => Ulid.Parse(x),
                mappingHints: DefaultHints.With(mappingHints))
    {
    }
}

internal class UlidToStringTypeMapping : RelationalTypeMapping
{
    private static readonly UlidToStringValueConverter ValueConverter = new();

    public UlidToStringTypeMapping()
        : base(new RelationalTypeMappingParameters(new CoreTypeMappingParameters(
                typeof(Ulid),
                ValueConverter),
                "char",
                StoreTypePostfix.Size,
                System.Data.DbType.AnsiStringFixedLength,
                unicode: false,
                size: 26,
                fixedLength: true))
    {
    }

    protected override RelationalTypeMapping Clone(RelationalTypeMappingParameters parameters)
        => new UlidToStringTypeMapping();
}

internal class UlidToStringTypeMappingSourcePlugin : IRelationalTypeMappingSourcePlugin
{
    public RelationalTypeMapping? FindMapping(in RelationalTypeMappingInfo mappingInfo)
    {
        if (mappingInfo.ClrType == typeof(Ulid))
        {
            return new UlidToStringTypeMapping();
        }

        return null;
    }
}

This query is working (SqlServer and Npgsql)

var myId = Ulid.NewUlid();
var e = db.MyEntity.SingleOrDefaultAsync(p => p.Id == myId);

This queries are not working (SqlServer and Npgsql)

Ulid.NewUlid()

var e = db.MyEntity.SingleOrDefaultAsync(p => p.Id == Ulid.NewUlid());
  Message: 
Npgsql.PostgresException : 42601: syntax error at or near "FJ2B2J3BMN1AVKT6SBA15D8R"

POSITION: 64

  Stack Trace: 
NpgsqlConnector.<ReadMessage>g__ReadMessageLong|215_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
AsyncEnumerator.MoveNextAsync()
ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
UlidTests.Query_with_ulid_new_method() line 45
--- End of stack trace from previous location ---

Ulid.MinValue (Unit test is green for SqlServer but I have a project with this error message "Error converting data type varchar to numeric")

var e = db.MyEntity.SingleOrDefaultAsync(p => p.Id == Ulid.MinValue);
Message: 
Npgsql.PostgresException : 42883: operator does not exist: character = integer

POSITION: 60

  Stack Trace: 
NpgsqlConnector.<ReadMessage>g__ReadMessageLong|215_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
AsyncEnumerator.MoveNextAsync()
ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
UlidTests.Query_with_ulid_minvalue() line 52
--- End of stack trace from previous location ---

Ulid.MaxValue

var e = db.MyEntity.SingleOrDefaultAsync(p => p.Id == Ulid.MaxValue);
  Message: 
Npgsql.PostgresException : 42601: syntax error at or near "EZ91ZPZZZZZZZZZZZZZZZZZZ"

POSITION: 64

  Stack Trace: 
NpgsqlConnector.<ReadMessage>g__ReadMessageLong|215_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
AsyncEnumerator.MoveNextAsync()
ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
UlidTests.Query_with_ulid_maxvalue() line 59
--- End of stack trace from previous location ---

Include provider and version information

EF Core version: 6.0.0-rc.2.21480.5 Database provider: SqlServer & Npgsql Target framework: 6.0.0-rc.2.21480.5 Operating system: Windows 10 IDE: Visual Studio 2022 17.0.0 Preview 5.0

Thanks in advance for your help

juchom commented 2 years ago

After digging in EF Core source code, I think I found the solution in the GuidTypeMapping

I was missing this method in UlidToStringTypeMapping

    protected override string SqlLiteralFormatString
        => "'{0}'";

I leave the issue open, just to make sure if the experts can confirm I made the right fix.