Support sproc input/output parameters on non-concurrency-token properties

[roji]

In some scenarios, users may want to provide a value for some property, but also read back the property. This can be useful when the user-provided value is somehow transformed in the database, and then we want to propagate the transformed value back into the entity. With regular SQL this can be achieved via triggers, or by wrapping a sproc and having an input/output parameter for the property.

See conversation in #28553 (comment)

[ErikEJ]

Not sure if it is relevant, but I ended up mapping all OUTPUT parameters as InputOutput (except the return value).

See related discussion here: ErikEJ/EFCorePowerTools#1069

[roji]

This covers two things:

• The same command both writing a value and reading it in the same command
• Have a store-generated column which is written explicitly in one command, and read from another.

[roji]

Some outputs from a discussion with @ajcvickers...

Problem 1

For the "same command" scenario, we're discussing writing the value when updating the entity, but also reading it back (if the property is ValueGeneratedOnUpdate).

However, there's also the case of inserts; just like a sproc/trigger/whatever may change the incoming value for an UPDATE, it may do so for an INSERT. In other words, to be consistent here, we'd need to read back ValueGeneratedOnAdd properties even when a value is provided by the user (since it may be modified db-side). We could start doing this, but that means we'd start sending back all IDs from the database even when they're explicitly given by the user, just to support this exotic scenario - which doesn't seem right.

Technically, the same is true of ValueGeneratedOnUpdate: the fact that a property is ValueGeneratedOnUpdate doesn't necessarily tell us if it's only generated/manipulated when no value is provided by the user, or also manipulated when the user provides a value. If we just start reading back values whenever the property is ValueGeneratedOnUpdate, we'd potentially be needlessly sending back values when we don't need to. This is admittedly really exotic, but there may be a case here for consistency with add.

In other words, to implement this we may be lacking additional metadata, which tells us to read back the property value on insert/update even when the user provides it.

Problem 2

For the non-same-command scenario, i.e. just allow the value to be either written or read (in different commands), there's still a problem with sprocs. Unlike regular SQL, sprocs require us to always provide all the parameters, making it difficult to do optional parameters (i.e. you can provide the value, but if you don't it'll get generated). The user could tell us about a sentinel value which we'd set when a property isn't set/changed by the user, and write their sprocs to recognize that value and act accordingly. Or there could be another flag parameter to say whether the parameter is set or not. But these seem like quite advanced features which we probably don't want to include for 7.0.

I'll bring this back to design, @AndriySvyryd if I've completely misunderstood things let me know.

[AndriySvyryd]

We could start doing this, but that means we'd start sending back all IDs from the database even when they're explicitly given by the user, just to support this exotic scenario - which doesn't seem right.

It would only happen for InputOutput parameters, I don't think we need to do it for non-sproc mapping

Unlike regular SQL, sprocs require us to always provide all the parameters, making it difficult to do optional parameters (i.e. you can provide the value, but if you don't it'll get generated). The user could tell us about a sentinel value which we'd set when a property isn't set/changed by the user, and write their sprocs to recognize that value and act accordingly.

Currently the sentinel would be the CLR default value equivalent, I think this is part of the more general issue of lack of optional parameter support

[roji]

Decision: when a parameter is defined with Direction=InputOutput, that's an opt-in to always write the value, and always read it back (and propagate it).