ToQueryString parameter values with float (and maybe real?) are wrong

[ErikEJ]

It is important that these values are correct if you rely on ToQueryString (DebugView) as a troubleshooting tool.

CREATE TABLE[dbo].[FloatTable]
(      
     [Id] INT  IDENTITY   NOT NULL,  
     [Data] FLOAT NULL
 );

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using System;
using System.Linq;

namespace ConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            using var nw = new NWContext();

            // Alter this value for each run
            double? testval = 4.5655d;

            nw.FloatTables.Add(new FloatTable
            {
                Data = testval,
            });

            nw.SaveChanges();

            var query = nw.FloatTables.Where(x => x.Data == testval).ToQueryString();

            var found = nw.FloatTables.SingleOrDefault(x => x.Data == testval);
        }
    }

    public partial class FloatTable
    {
        public int Id { get; set; }
        public double? Data { get; set; }
    }

    public partial class NWContext : DbContext
    {
        public NWContext()
        {
        }

        public virtual DbSet<FloatTable> FloatTables { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
                optionsBuilder.UseSqlServer("Data Source=.\\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True", x => x.UseNetTopologySuite())
                    .LogTo(Console.WriteLine)
                    .EnableSensitiveDataLogging();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.ApplyConfiguration(new FloatTableConfiguration());
        }
    }

    public partial class FloatTableConfiguration : IEntityTypeConfiguration<FloatTable>
    {
        public void Configure(EntityTypeBuilder<FloatTable> entity)
        {
            entity.ToTable("FloatTable");
        }
    }
}

ToQueryString:

DECLARE @__testval_0 float = 4.5654000000000003E0;

SELECT[f].[Id], [f].[Data]
        FROM[FloatTable] AS[f]
WHERE[f].[Data] = @__testval_0

EF Core log:

Executing DbCommand[Parameters =[@__testval_0 = '4.5654'(Nullable = true)], CommandType = 'Text', CommandTimeout = '30']

SELECT TOP(2) [f].[Id], [f].[Data]
        FROM[FloatTable] AS[f]
WHERE[f].[Data] = @__testval_0

EF Core version: 6.0.3
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0

[ajcvickers]

@ErikEJ The value in ToQueryString should exactly match the value in the parameter, while the value in the log is just for human consumption. Is the value in ToQueryString not the value in the parameter? Or is there something else wrong with it or its formatting?

[ErikEJ]

If you use the value from the ToQueryString to find the row, the query will return null.

4.5654000000000003E0

[ajcvickers]

Thanks @ErikEJ. I'm guessing SqlClient is truncating the parameter. We will discuss.

[ajcvickers]

@ErikEJ I am not able to reproduce this. Did you perhaps use mismatched query SQL and value?

In the code above, the value in code is double? testval = 4.5655d;, but this produces the following query:

DECLARE @__testval_0 float = 4.5655000000000001E0;

SELECT [f].[Id], [f].[Data]
FROM [FloatTables] AS [f]
WHERE [f].[Data] = @__testval_0

If I change the code to double? testval = 4.5654d;, then the query is:

DECLARE @__testval_0 float = 4.5654000000000003E0;

SELECT [f].[Id], [f].[Data]
FROM [FloatTables] AS [f]
WHERE [f].[Data] = @__testval_0

Both of these queries work when executed by ADO.NET and in SQL Management Studio.

So, while aesthetically, it might be better to truncate, there doesn't appear to be any functional issue here.

[ErikEJ]

Error 40!

[roji]

What's error 40? :)