Cosmos does not translate string concat where at least 1 parameter needs to be converted to a string

[ChrisJollyAU]

From the discussion at #34729

As part of investigating applying the above fix to Cosmos SqlExpressionFactory to keep things in sync (as far as possible) I spotted a deeper problem as part of the query.

The part of the query: x.OrderID + "" is automatically not translated. See comment at #34729 (comment) for initial diagnoses

Ultimately, when the one side is a unary convert, the Concat function that is set up to be called is the one with both parameters as an object. This is specifically handled to return as a not translated.

Additionally, if you ignore that, when visiting the side with the convert, that convert is stripped away and you are left with the operand from the unary expression. In the above mentioned expression this would have type of Int32 and the related type mapping. Naturally later on you have effectively a binary expression with left as Int32 and right as string which doesn't work

Note that there are already tests for this

In NorthwindMiscellaneousQueryCosmosTest

• Concat_int_string
• Concat_constant_string_int
• Concat_string_int
• Concat_parameter_string_int

Above all succeed but only because concatenation occurs in top-level projection which falls back to client side evaluation

In NorthwindWhereQueryCosmosTest

• Where_concat_string_int_comparison1/2/3/4
• Using_same_parameter_twice_in_query_generates_one_sql_parameter

Those are expected to fail currently. The concatenation is not in a top-level projection so doesn't have a fall back

[ChrisJollyAU]

@roji I have a fix for this issue. Do you want that or do you want to merge this with #34982 and do all of the cast/convert in one issue.