Map Int128/UInt128 where possible

[ajcvickers]

No description provided.

[roji]

Am not aware of any database which has these as a native type, though a mapping to decimal/numeric (e.g. via a value converter) is possible, if we think there's value in that.

[Timovzl]

A DECIMAL(38,0) gets us 126 out of 128 bits of the way there. 😄 It's useful to me.

I also image that some would want to map to BINARY(16).

In the meantime, an example of how one would manually convert this could be helpful. Would you convert to and from string to get this working?

[roji]

A DECIMAL(38,0) gets us 126 out of 128 bits of the way there. smile It's useful to me.

I don't think we'd want a value conversion that loses information...

I also image that some would want to map to BINARY(16).

Maybe... I'm not sure to what extent that would actually be useful/used.

In the meantime, an example of how one would manually convert this could be helpful. Would you convert to and from string to get this working?

I think the idea here is to implement this in the ADO.NET providers (e.g. Npgsql, Microsoft.Data.SQLite), where the Int128 gets encoded directly to the database wire protocol representation of the database decimal (when that makes sense and doesn't lose data).

Unless I'm mistaken, you cannot losslessly convert Int128 to .NET decimal; although both are 128-bit, the range of .NET decimal (which also has flags/fraction bits) is smaller than the range of Int128, where all bits are used to simply represent the integer value. It's probably possible to do something like an Unsafe cast, but at that point the resulting decimal representation has nothing more to do with the original Int128 value; you might as well be storing it as binary.

Of course, if what you want is to represent an Int128 as textual data in the database, then you can do that as usual by using ToString in the value converter.

[Timovzl]

I don't think we'd want a value conversion that loses information...

I was going with your earlier suggestion: "though a mapping to decimal/numeric (e.g. via a value converter) is possible". Since the title mentions Int128/UInt128, which are .NET types, I'm pretty sure you are talking about the database side when you say decimal/numeric. As it happens, DECIMAL(38, 0) is the closest we can do to UInt128, at least on SQL Server and MySQL.

Such a conversion is useful when you have numbers in .NET that are >96 and <=126 bits, since these won't fit in a .NET decimal, but will fit in a .NET UInt128 and the database's DECIMAL(38, 0).

I'm not sure to what extent [BINARY(16)] would actually be useful/used.

This issue is about being able to map .NET's UInt128 and Int128 to the database, right? This begs the question, which database types can hold such a thing? I can think of BINARY(16) and DECIMAL(38, 0).

Did you have something else in mind?

I think the idea here is to implement this in the ADO.NET providers (e.g. Npgsql, Microsoft.Data.SQLite), where the Int128 gets encoded directly to the database wire protocol representation of the database decimal (when that makes sense and doesn't lose data).

That would be ideal. But until we have that, I think we should have an example of to employ a custom converter to get those UInt128s in and out.

Unless I'm mistaken, you cannot losslessly convert Int128 to .NET decimal

Exactly. I believe that's the point of the issue. .NET has a type that can hold a 128-bit integer; the database has a few types that could do the job... now we want to be able to get those large integers in or out, i.e. (the title) map Int128/UInt128 where possible - and .NET's decimal type is not going to do the trick.

In summary, I'm considering which database types this issue applies to and how we could implement the mapping (until the providers support it out-of-the-box).

[roji]

Such a conversion is useful when you have numbers in .NET that are >96 and <=126 bits, since these won't fit in a .NET decimal, but will fit in a .NET UInt128 and the database's DECIMAL(38, 0).

Sure, but such a limited/lossy conversion isn't something that should just happen transparently out-of-the-box.

Did you have something else in mind?

PostgreSQL decimal can hold it, and on SQLite we already use a string representation (IIRC?) for decimal, so we could do the same for Int128. As above, these would need to be done in the ADO.NET providers, and then also mapped at the EF level.

That would be ideal. But until we have that, I think we should have an example of to employ a custom converter to get those UInt128s in and out.

But there's no way to do that (except for string/byte[]), as we've been discussing; EF value converters simply convert from one (model) .NET value to another (provider) .NET value, and then the 2nd one gets persisted. The only .NET conversions I'm aware of which wouldn't be lossy is byte[] (which I'm not sure anyone actually wants) or string. Neither of these allows any numeric database operation, querying, etc.

But until we have that, I think we should have an example of to employ a custom converter to get those UInt128s in and out.

So again, if you just want to convert to a string, do ToString() in your custom value converter. A conversion to byte[] is a bit more elaborate, but should be pretty trivial as well.

[Timovzl]

Sure, but such a limited/lossy conversion isn't something that should just happen transparently out-of-the-box.

My first thought was to agree, but then I thought of strings. If a VARCHAR is too short to hold a given string, the insert still succeeds. Should we be consistent with that or treat numbers differently because the effect of loss changes their entire meaning?

PostgreSQL decimal can hold it

Postgres wins again. 😄
That's a great feature.

So again, if you just want to convert to a string, do ToString() in your custom value converter.

You've mentioned the cases of wanting to convert to string and wanting textual data in the database. That's all clear. I'm trying to clarify if you mean that this also serves as a workaround to insert a UInt128 into a DECIMAL column.

[roji]

Sure, but such a limited/lossy conversion isn't something that should just happen transparently out-of-the-box.

My first thought was to agree, but then I thought of strings. If a VARCHAR is too short to hold a given string, the insert still succeeds. Should we be consistent with that or treat numbers differently because the effect of loss changes their entire meaning?

I don't think we map to limited-length strings by default, e.g. on SQL Server a string is mapped to NVARCHAR(MAX) by default. That should mean that we never lose data out-of-the-box - unless of course the user explicitly configures the column to be a limited length (but then that's no longer out-of-the-box behavior). Or am I missing something?

That's all clear. I'm trying to clarify if you mean that this also serves as a workaround to insert a UInt128 into a DECIMAL column.

I don't think so, because SqlClient (I think!) wouldn't allow you to insert a .NET string into a SQL Server DECIMAL column, right?

[Timovzl]

I don't think we map to limited-length strings by default

Right, I didn't mean a default mapping, but just the ability to map it in the first place. I see the confusion: I interpreted the title as "make it possible to map to sensible column types", but it can just as sensibly be read as "map by default where lossless".

So the ideal scenario is a database that supports DECIMAL(39), where that type could be made the default for a (U)Int128 property.

Since the (U)Int128 will have to be passed to the provider in some way that works, I'm hopeful that this behavior will also make an explicit call to HasColumnType("DECIMAL(38)") or HasColumnType("VARCHAR(39)") work, i.e. for databases that do not have a perfectly matching column type.

All things considered, I'd understand if HasColumnType("BINARY(16)") would always require a custom converter.

SqlClient (I think!) wouldn't allow you to insert a .NET string into a SQL Server DECIMAL column, right?

I haven't tried it either! I'm hoping it lets you send in most data types as strings, since the language does too. It seems... unnecessary for it to be less forgiving.

[OpenSpacesAndPlaces]

In the meantime - what are the real world concerns with using like:

     var converter = new ValueConverter<BigInteger, String>(
                    model => model.ToString(),
                    provider => BigInteger.Parse(provider));

                entity.Property(p => p.MyProp).HasConversion(converter);

Is there a specific number size where it becomes an issue?

[roji]

@OpenSpacesAndPlaces shouldn't be a problem. Ths strings could get arbitrary long based on the size of your BigInteger instances, and of course you can't query them as numbers.