Scale is not considered when converting decimal to string

[leaderanalytics]

Problem:

COALESCE([table].[column], 0.0)

Returns column value with one decimal place even if the stored value has zero decimal places. COALESCE docs don't say anything about this behavior. It seems odd given the purpose of the function.

Example:

Column is defined on Z table as:

[SerialNumber] [numeric](20, 0) NULL,

Column is defined on the model as:

 entity.Property(e => e.SerialNumber).HasColumnType("numeric(20, 0)");

My query:

var query = from zNode in db.Z
...
join ltr in db.LastReadTimes on new { SN = "G" + (zNode.SerialNumber ?? 0).ToString() } equals new { SN = ltr.MeterId } into lastReads

Generated SQL:

 LEFT JOIN [MDMS].[LastReadTime] AS [l] ON (N'G' + COALESCE(CONVERT(VARCHAR(100), COALESCE([z].[SerialNumber], 0.0)), N'')) = [l].[MeterId]

Working SQL:

 LEFT JOIN [MDMS].[LastReadTime] AS [l] ON (N'G' + COALESCE(CONVERT(VARCHAR(100), COALESCE([z].[SerialNumber], 0)), N'')) = [l].[MeterId]

Version Info: net5 EF Core 5.0.8

Actual data values and desired output:

Edit: added model property

[ajcvickers]

Unassigning myself since I have no idea what the easy-fix is.

[ajcvickers]

Likely needs #4978.