Named parameters (parameter placeholders) and Parameter vs PropertyParameter

[MaxG117]

Our data store does not support named parameters (a.k.a. parameter placeholders) -- we use question marks ("?") instead in DbCommand.CommandText, and we expect the DbCommand.Parameters collection to contain all the parameters in order.

This presents some challenges for implementing an EntityFrameworkCore provider. First we had to allow for duplicate parameters via #12347. Second, it looks like CommandText is generated independently in multiple places (DefaultQuerySqlGenerator, UpdateSqlGenerator), so the best place I found where I can rearrange command parameters and update command text is by overriding the RelationalCommand.CreateCommand method.

Does this make sense, or is there a better way to solve this?

Assuming that the above makes sense, consider the SimpleQueryTestBase.Where_poco_closure() test case:

            var customer = new Customer
            {
                CustomerID = "ALFKI",
            };

            await AssertQuery<Customer>(
                isAsync,
                cs => cs
                    .Where(c => c.Equals(customer)
                    .Select(c => c.CustomerID));

It's the only test case that fails when using my CreateCommand override method. It fails because I assumed that parameterValues.Keys can be easily matched up with parameter placeholders, but this is not true for PropertyParameters. In the example above, the Key is "__customer_0", while the placeholder is "__customer_0_CustomerId".

I'd rather not go through each TypeMappedRelationalParameter to look at its Name, but I can't think of another way to do it. And I suspect there may be other parameter types with the same problem. Is there an easier solution to this?

[MaxG117]

#14645 is related in that it also can't easily be fixed without looking at the Name.

[ajcvickers]

@AndriySvyryd @smitpatel @roji to look at this as part of 3.0 query (but also impacts update pipeline)

@MaxG117 Which database provider are you working on?

[smitpatel]

@MaxG117 - Can you tell where is propertyValues.Keys lives?

[MaxG117]

@ajcvickers We haven't publicly announced yet, so I'm not at liberty to disclose at this time.

@smitpatel apologies, I meant parameterValues.Keys, not propertyValues.Keys. parameterValues is a dictionary passed in to RelationalCommand.Execute methods. I'm still figuring out how it gets generated.

[smitpatel]

Thanks for additional info. I can see why those keys being mismatched. In 3.0 pipeline I will see any other way to improve what you want to achieve but it is likely to be on the lines of doing something with PropertyParameter rather than RelationalCommand.

@AndriySvyryd - Can you comment if modifying RelationalCommand as mentioned in first post is a good way to go or there is a better way to achieve this (w.r.t. Update pipeline).

[AndriySvyryd]

It would also be possible to override ReaderModificationCommandBatch.CreateStoreCommand() if that's the base class this provider uses. But there's no easy way to make EF generate the expected command text and present the parameters in correct order.

[roji]

Note: moved this to the 3.0.0 milestone.