Support server side value conversions

[ajcvickers]

Value conversions as implemented for #242 work by converting the value after it has come from the database and before it is send back to the database. In some situations it is useful to instead convert in the generated SQL. This enables support for cases where the database provider has no support for some database type, but it can be converted to some other database type that the provider can handle.

[ajcvickers]

Also consider the discussion in #12762 when working on this.

[roji]

Am interested in the reason for punting this - have you guys found a different way of implementing spatial support for SQL Server?

This would be useful for some other uses - PostgreSQL has some types which the Npgsql ADO driver doesn't support. These are mainly exotic/rare types, but server-side conversions would allow converting them to text before fetching. Definitely nothing important that should change the priority of this, though.

[bricelam]

Have you guys found a different way of implementing spatial support for SQL Server?

Yes. We're reading and writing bytes directly based on the serialization format.

[roji]

Oh, OK... Does this mean the .NET Core SqlClient will soon be able to read and write SqlGeometry/Geography? Or is this something you're implementing at the EF Core level?

[ajcvickers]

@roji EF level, unfortunately.

[bricelam]

Well... we've implemented an NTS I/O reader and writer. Anyone could use them directly with SqlDataReader and SqlParameter. But obviously this is a lot different than using SqlGeometry directly on the client.

[roji]

@dpsenner translating operations on field of PostgreSQL composite types (UDTs) is a different thing from what this issue is about, and AFAIK requires Npgsql support rather than anything specific from EF Core.

This issue is about translating done database type to something else in SQL, before it is fetched to the client (and accordingly for sending).

[dpsenner]

Sorry, comment removed.

[tystol]

I'm testing out EF Core right now, and one thing I was hoping for (that was never supported in EF6) is allowing EF models to contain strong typed IDs (eg. so the compiler can enforce preventing the passing of a CompanyId into a method that takes an EmployeeId, when both the underlying ids are just Guids).

I assumed ValueConverters allowed for this. eg:

public class Post
{
    public PostId PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
}
public class PostId
{
    public Guid Id { get; set; }
    // Equality/hashcode overrides omitted for brevity.
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    var converter = new ValueConverter<PostId, Guid>(
        v => v.Id,
        v => new PostId { Id = v });

    modelBuilder
        .Entity<Post>()
        .Property(p => p.PostId)
        .HasConversion(converter);

    base.OnModelCreating(modelBuilder);
}

While this works to generate a migration, even a simple query is evaluated in memory. eg:

var postId = new PostId { Id = 2.ToGuid() };
var post = dbContext.Posts.FirstOrDefault(p => p.PostId == postId);

Do value converters currently prevent ALL server side evaluation? I did see the documentation:

Use of value conversions may impact the ability of EF Core to translate expressions to SQL. A warning will be logged for such cases. Removal of these limitations is being considered for a future release.

but to me the wording implied it may not work in some complex edge cases, where as my test above is pretty much as simple as you can get.

If my understanding above is correct, is this the correct issue to subscribe to for tracking progress/support? And will it support my case above when implemented? And do we have an estimated timeline/version yet?

[ajcvickers]

@smitpatel See comment above, which is essentially about SQL translation with value converters. This is likely a duplicate of something, but I can't find the best fit.

[smitpatel]

@ajcvickers @tystol - Duplicate of #12045

[marchy]

Given #12045 is now closed, does this mean this is as well?

[ajcvickers]

@marchy Nope, this is still in the backlog for consideration.