Support for custom type mapping and data store to CLR type conversions

[rowanmiller]

There is a continuum of scenarios that can be supported here:

• Allow for simple hard coded conversions between types that are related in a well-know manner to types that are supported. E.g.:
  • char can map to the database exactly as a string of size 1 (see Remove SqlServer TypeMapping for CLR type char #8656)
  • byte can map to the database exactly the same as a byte[]
  • signed or unsigned small integers can fit in the nearest wider signed or unsigned integer
• Allow providers to supply their own additional type mappings for types they don't support, e.g. if a database engine doesn't have native support for bool, it can decide to use a small integer representation
• Allow specific well known scenarios that are commonly demanded, like mapping enum types to strings
• Allow for conversions to be performed on the server (vs. only on the client) for cases in which there isn't a viable CLR representation for the server type - see Support different SQL translation when store representation has changed due to value conversion #10434 and Support server side value conversions #10861
• Allow for user provided conversions

They all probably require extending the reach of the type mapper to be able to participate of the generation of:

• Parameters
• Expressions for value access in materialization
• Expressions for equality and inequality comparisons - see Support client evaluation when store evaluation is not appropriate #10265
  1. Equality on the server should be equivalent to equality on the client as long as the conversion is deterministic and bijective.
• Order comparisons and sorting - see Support client evaluation when store evaluation is not appropriate #10265
  1. Comparisons and sorting are equivalent as long as the conversion is a monotonic function (although reversed if it is decreasing)
  2. For non order-preserving conversions, we may need to differentiate the scenarios in which we introduce sorting just to get the order of two or more results to be deterministic so we can zip them together, vs. explicit ordering requested by user, which we may need to evaluate on the client after the conversion is performed.
• Operator and function translation - see Support different SQL translation when store representation has changed due to value conversion #10434

[mattjohnsonpint]

Just checking - Is this the work item tracking this uservoice item? Thanks.

[mattjohnsonpint]

Any movement on this? Or hint of direction, proposed API, anything? Thanks.

[rowanmiller]

@mj1856 nothing yet. We know we want to do this (and that has influenced how things are architected internally) but we're currently planning to work on lighting this feature up after our initial RTM of EF7.

[mattjohnsonpint]

Thanks!

[smitpatel]

Details:
At present we are making changes to our property discovery so that all the types supported by the provider can be mapped as primitive properties. In the addition to provider supported types, user may want to use some other type as a primitive by providing a custom typemapping through which it can be mapped to one of the known types of the provider and subsequently be used as a primitive property.
Also see #2588

[ilmax]

Does this allow me to map a custom type to whatever filed in the storage? e.g. create a custom type for entity.Id (which only wraps an int) instead of using int or map enum to char?

[Rseding91]

This is exactly what I was recently looking to do. It would be an extremely useful to have this.

[Marusyk]

I need the same possibility as mentioned by @ilmax:

Does this allow me to map a custom type to whatever filed in the storage? e.g. create a custom type
for entity.Id (which only wraps an int) instead of using int or map enum to char?

Does this allow me to do that?

[ajcvickers]

@glucaci Looks like a bug--can you file a new issue for it?

[glucaci]

Sure, I've just created one #10765

I don't know how the conversion should work internally but I realize that by ignoring the property everything is working (column created, saving to db, queering the db), but it seams that it's not the proper way 😏

[ajcvickers]

Note for triage: Additional work to be done here:

• Allowing query translation to use type-mapping/conversion info.
• Mechanism/API to specify a default conversion for any property of a given type in the model.

[ajcvickers]

Closing this as done for 2.1. Additional related work is tracked by #10784, #10434 #10265

[tystol]

When this was first created back in 2014 (based on a uservoice suggestion from 2012) the intent was obviously for it to be a feature of legacy EF(ie.1-6). So while it is great that EFCore supports this, where does this leave the vast majority of us with existing apps on EF6 (that can't update until EFCore is at feature parity with EF6 - ie. GROUP BY!)

Is there any plans to backport this to EF6?

[ajcvickers]

@tystol This issue is not something that our team is planning to address in the EF6.x code base. This does not mean that we would not consider a community contribution to address this issue. However, the nature of the EF6 code and the EDM type system makes it non-trivial to implement. Being able to do things like this in a reasonable way is one of the reasons for EF Core being a new codebase that is not a front for EDM.

[essmd]

As already mentioned from @Marusyk and @ilmax:

I need the same possibility as mentioned by @ilmax:

Does this allow me to map a custom type to whatever filed in the storage? e.g. create a custom type
for entity.Id (which only wraps an int) instead of using int or map enum to char?

Does this allow me to do that?

I already tried ValueConverters and those are awesome features! But today i tried to use a custom type for Identities (primary and foreign keys). In DDD where you want to express your values with more context - an int (primary key and foreign key) is just an int and its value could be anything.

It would be so much value for EF if it would be possible to consider value conversations for primary keys and foreign keys. Is something like this planned or will this issue resolve parts of this?

public class CustomerId
{
    public int Value { get; }

    public CustomerId(int value)
    {
        if (value <= 0) throw new ArgumentOutOfRangeException(nameof(value));
        Value = value;
    }
}

public class Customer
{
    public CustomerId Id { get; private set; }
    public CustomerId ReferredById { get; private set; }
}

public class CustomerEntityConfiguration : IEntityTypeConfiguration<Customer>
{
    public void Configure(EntityTypeBuilder<Customer> builder)
    {
        builder.ToTable("Customers");
        builder.HasKey(x => x.Id);

        // works already fine and maps client type CustomerId to storage type int and reverse
        builder.Property(x => x.Id).HasConversation(id => id.Value, value => new CustomerId(value);

        // not possible (Exception) because "Id" must be an int to support value generation
        // but since there is an value conversation, it should use the storage type which is/results in an int?
        builder.Property(x => x.Id).UseSqlServerIdentityColumn();
    }
}

[ajcvickers]

@essmd To clarify, the only part of this that isn't working for you is that the key cannot be store-generated?

[essmd]

@ajcvickers Correct! As i can see here in the code, converters are not allowed.

My expectation from this setup was, that the add migration would create an migration with an INT IDENTITY column, not throwing an exception.

Later when SaveChangesAsync is called, EF executes the INSERT INTO sql query and get the identity/id back from store (INT) and when it comes to map the INT from store to the property, it will use the configured conversation.

I tested again with foreign key too, and its also not possible to map foreign keys with properties using custom types and configured converters:

public class Order
{
    public CustomerId CustomerId { get; private set; }
}

public class OrderEntityConfiguration : IEntityTypeConfiguration<Order>
{
    public void Configure(EntityTypeBuilder<Order> builder)
    {
        builder.ToTable("Orders");

        // works fine and maps client type CustomerId to storage type int and reverse
        builder.Property(x => x.CustomerId)
            .HasConversation(id => id.Value, value => new CustomerId(value);

        // not working
        builder.HasOne<Customer>()
            .WithMany()
            .HasForeignKey(x => x.CustomerId);
    }
}