A field of type 'money' in a SQL Server table mapped to a System.Decimal field gives warning 30000: 'No type was specified for the decimal column ...'

[FransBouma]

Preamble: we generate mappings for EF Core models and at the moment we don't emit a .HasColumnType("..") fragment for System.Decimal typed entity fields. We weren't aware this could lead to warnings of type 30000 'No type was specified for the decimal column...' in logs as they don't show up in unit tests, but do show up in e.g. Sentry logs and when you append a logger to the context.

We're asked by our users to make sure these warnings aren't showing up anymore, but we ran into a problem: for 'decimal' or 'numeric' typed fields in tables this works out fine, but for database types without precision/scale definitions this fails: the warning is still raised. See below.

Details

Using AdventureWorks, I have a table, e.g. Sales.CurrencyRate, which looks like:

CREATE TABLE [Sales].[CurrencyRate](
	[CurrencyRateID] [int] IDENTITY(1,1) NOT NULL,
	[CurrencyRateDate] [datetime] NOT NULL,
	[FromCurrencyCode] [nchar](3) NOT NULL,
	[ToCurrencyCode] [nchar](3) NOT NULL,
	[AverageRate] [money] NOT NULL,
	[EndOfDayRate] [money] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_CurrencyRate_CurrencyRateID] PRIMARY KEY CLUSTERED 
(
	[CurrencyRateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I've mapped an entity on this table using our system LLBLGen Pro: (CommonEntityBase is almost empty, it contains an OnCreated partial method, that's it)

using System.Collections.Generic;

namespace EFCore3.EntityClasses
{
	/// <summary>Class which represents the entity 'Sales.CurrencyRate'.</summary>
	public partial class CurrencyRate : CommonEntityBase
	{
		/// <summary>Method called from the constructor</summary>
		partial void OnCreated();

		/// <summary>Initializes a new instance of the <see cref="CurrencyRate"/> class.</summary>
		public CurrencyRate() : base()
		{
			this.SalesOrderHeaders = new List<SalesOrderHeader>();
			OnCreated();
		}

		/// <summary>Gets or sets the AverageRate field. </summary>
		public System.Decimal AverageRate { get; set; }
		/// <summary>Gets or sets the CurrencyRateDate field. </summary>
		public System.DateTime CurrencyRateDate { get; set; }
		/// <summary>Gets or sets the CurrencyRateId field. </summary>
		public System.Int32 CurrencyRateId { get; set; }
		/// <summary>Gets or sets the EndOfDayRate field. </summary>
		public System.Decimal EndOfDayRate { get; set; }
		/// <summary>Gets or sets the FromCurrencyCode field. </summary>
		public System.String FromCurrencyCode { get; set; }
		/// <summary>Gets or sets the ModifiedDate field. </summary>
		public System.DateTime ModifiedDate { get; set; }
		/// <summary>Gets or sets the ToCurrencyCode field. </summary>
		public System.String ToCurrencyCode { get; set; }
		/// <summary>Represents the navigator which is mapped onto the association 'Sales.CurrencyRate.Currency - Sales.Currency.CurrencyRates (m:1)'</summary>
		public virtual Currency Currency { get; set; }
		/// <summary>Represents the navigator which is mapped onto the association 'Sales.CurrencyRate.Currency1 - Sales.Currency.CurrencyRates1 (m:1)'</summary>
		public virtual Currency Currency1 { get; set; }
		/// <summary>Represents the navigator which is mapped onto the association 'Sales.SalesOrderHeader.CurrencyRate - Sales.CurrencyRate.SalesOrderHeaders (m:1)'</summary>
		public virtual List<SalesOrderHeader> SalesOrderHeaders { get; set; }
	}
}

Using the mappings:

/// <summary>Defines the mapping information for the entity 'Sales.CurrencyRate'</summary>
/// <param name="config">The configuration to modify.</param>
protected virtual void MapCurrencyRate(EntityTypeBuilder<CurrencyRate> config)
{
	config.ToTable("CurrencyRate", "Sales");
	config.HasKey(t => t.CurrencyRateId);
	config.Property(t => t.CurrencyRateId).HasColumnName("CurrencyRateID").ValueGeneratedOnAdd();
	config.Property(t => t.CurrencyRateDate);
	config.Property(t => t.FromCurrencyCode).HasMaxLength(3).IsRequired();
	config.Property(t => t.ToCurrencyCode).HasMaxLength(3).IsRequired();
	config.Property(t => t.AverageRate).HasColumnType("money");
	config.Property(t => t.EndOfDayRate).HasColumnType("money");
	config.Property(t => t.ModifiedDate);
	config.HasOne(t => t.Currency).WithMany(t => t.CurrencyRates).HasForeignKey(t => t.FromCurrencyCode);
	config.HasOne(t => t.Currency1).WithMany(t => t.CurrencyRates1).HasForeignKey(t => t.ToCurrencyCode);
}

This gives the warnings:

warn: Microsoft.EntityFrameworkCore.Model.Validation[30000]
      No type was specified for the decimal column 'AverageRate' on entity type 'CurrencyRate'. This will cause values to be silently truncated if they do not fit in the default precision and scale. Explicitly specify the SQL server column type that can accommodate all the values using 'HasColumnType()'.
warn: Microsoft.EntityFrameworkCore.Model.Validation[30000]
      No type was specified for the decimal column 'EndOfDayRate' on entity type 'CurrencyRate'. This will cause values to be silently truncated if they do not fit in the default precision and scale. Explicitly specify the SQL server column type that can accommodate all the values using 'HasColumnType()'.

These warnings pop up as well when I omit the HasColumnType("money") fragment.
Funnily enough, when I specify .HasColumnType("money(19,4)") the warnings are gone! However 'money(precision,scale)' is a non-existing type. So to 'fix' this we have to emit a type which does accept a precision/scale, e.g. decimal(18, 2) but this isn't the same as 'money'. For an existing database this might be 'ok' as the types specified with HasColumnType won't be used, but for the situation where the database is created at startup time it might be problematic. (Besides it's an ugly workaround, IMHO, as the type isn't decimal/numeric but 'money')

Steps to reproduce

Map any table with a money typed field to EF Core, switch on a logger and you'll see the warnings.

Further technical details

EF Core version:
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.1
Operating system: Windows 10 Ent.
IDE: VS 2019/rider, doesn't matter.

(If I may, why not add a .HasPrecision() and a .HasScale() like there's a .HasMaxLength() to fix this at the model level instead of the provider level? )

[FransBouma]

Sorry about this, we made a mistake with the tests, which used outdated models. Re-running everything reveals it does work. Carry on!

[ajcvickers]

@FransBouma FYI HasPrecision/HasScale is tracked by #11614