For MSSQL Parameters are created with length based on their value not the underlying model length this causes plan reuse issues

[simonsabin]

Currently string and binary parameters are added without a length meaning you get multiple queries and plans causing bloat and is impossible to manage from a query store plan perspective as one, you end up with 100s of queries with the same hash, but are different queries and also differing plans. Query is just varying by the length of the definition of the parameter. (See below)

The DBConnectionParam needs a length and the creation of params should use length where possible.

This could obtain the length of the underlying model as it does to get the type of the underlying model column

data-api-builder/src/Core/Resolvers/BaseQueryStructure.cs

Line 120 in af09e10

public virtual string MakeDbConnectionParam(object? value, string? paramName = null)

[simonsabin]

The underlying issue is that to get plan reuse you really want to have the same query passed. If you have varying length parameters then you end up with multiple plans. This not only uses up the plan cache, but also makes tools like query store very noisy and hard to use because of multiple queries for the "same query".
Query store forcing of plans works at queryId if you have multiple queries that again becomes harder and features like parameter sensitive plans don't work as well as you get so many plans.

It further makes reviewing workloads harder as aggregating numbers up durations, executions only qorks if you look at query hash, rather than query_id.