Default Size behavior in RelationalTypeMapping may be incorrect

[roji]

Originally raised in npgsql/efcore.pg#357.

The default behavior in RelationalTypeMapping is to set the parameter's Size to the mapping's Size, if one is specified. Since DbParameter.Size means truncating the value, that means that by default EF Core tells the underlying ADO.NET provider to perform client-side truncation based on the mapping's size. This behavior isn't overridden in StringTypeMapping.

However, the SqlServer provider does override this behavior for strings and for byte arrays. Both seem to set DbParameter.Size to either maxSpecificSize (if the value length is smaller than then max column length) or to -1. This way, the parameter data is never truncated and the backend returns an error as expected.

If this is the behavior that you're looking for across providers (and I see no reason for this to vary), then I think you may want to change RelationalTypeMapping to no longer truncate by default.

[ajcvickers]

@roji The code for setting the size in SQL Server is there for a number of reasons:

• Avoid fragmentation of query plans on the server
• Make sure data is not truncated when the real size of the column is not known
• Still allow the server to throw if the data does not fit

The key part about the first point is to use a small number of size values for parameters and to use the correct size whenever possible. Therefore:

• If the size of the column has been specified, then use this for the parameter size
• Otherwise use the max size that can typically be handled outside of a (max) column
• Unless in either of these cases value being saved wouldn't fit, in which case use -1 for size to stop SQLClient from doing its own size inference.

On the other hand, the default behavior for parameters is to set size only if the size has been specified, otherwise do nothing. This seemed like a good default behavior given that different ADO.NET providers can behave differently and have different requirements for size.

So my question is, based on your experience, which parts of the SQL Server algorithm do you think are common across providers? For example, does Postgres ever require the size to be set? If so, what is the impact of setting the size?

[ajcvickers]

@roji Any thoughts on this?

[roji]

It'll take me a few more days to respond, sorry...

[roji]

So my question is, based on your experience, which parts of the SQL Server algorithm do you think are common across providers? For example, does Postgres ever require the size to be set? If so, what is the impact of setting the size?

On the PostgreSQL side, there's not a whole lot of usage for length. Unless I'm mistaken, the only column types where length is allowed are text and bitstring. Actually specifying the size isn't required: varchar without parentheses is legal, and means that any length is supported (much like text). The same is true of bit varying, which is an arbitrary-length bitstring. In addition, the docs for string types seem to explicitly say that there's no perf impact for specifying the length. In other words, length seems to be an optional feature that's only there to allow database validation of data, and has no other consequence.

On the client side, as I wrote before, setting DbParameter.Size seems to mean "truncate my value to this size when sending to the database". I understand that the actual behavior may vary across different providers, and that it's conceivable that some provider out there requires you to set DbParameter.Size to the same value as your column's configured size - but I haven't seen anything like this. It's definitely not the case for PostgreSQL, and I think it would be against the behavior described in docs.

To summarize, if you don't want to stand in the way of server validation of length (which is how your SQL Server provider works at the moment), then it may be a good idea to stop setting DbParameter.Size. The mapping's size would of course still be used when creating the column etc., but would no longer impact how parameter data is actually sent by the ADO.NET provider. On the other hand, if you have reason to believe that some providers out there treat DbParameter.Size as something other than client truncation, then the current behavior may be fine.

[ajcvickers]

Thanks @roji. We decided in triage that we will make the change in 2.2 to never set Size by default. SQL Server will continue to override for its additional logic. Currently Oracle, MySQL and SqlCompact also all override and so will not be impacted by the change. It will cause Postgres and SQLite to stop silently truncating, which could be considered a breaking change, but we are choosing to view as a bug fix. You may want to override in the Postgres provider for 2.1--it's a safer change to make there at this point than it is to make it in the core code.