EF Core 2.1 Float Number Conversion Issue

Question

I ran into an issue with query generation in Entity Framework Core 2.1.

I have a query

var q = KostenSchweissen.Where(k => k.Grenze >= ewd)
  .OrderBy(k => k.Grenze)
  .FirstOrDefault();

with KostenSchweissen.Grenze being an integer, but ewd being a float.

So this looks for the first line with a "Grenze" greater or equal the value that is passed down. This ran fine previously (EF Core 1.x, not sure about 2.0), but now it's generating the query (from SQL Server Profiler):

exec sp_executesql N'SELECT TOP(1) [k].[ID], [k].[Grenze], [k].[PreisEdelstahl], [k].[PreisNormalstahl]
FROM [KostenSchweissen] AS [k]
WHERE [k].[Grenze] >= @__ewd_0
ORDER BY [k].[Grenze]',N'@__ewd_0 smallint',@__ewd_0=17

where ewd was 17.1. So this completely ignores that ewd is a float, obviously returning wrong costs. Casting to a double or float in the query doesn't work, the only workaround that I could come up with is

    var q = KostenSchweissen.Where(k => k.Grenze * 1.0 >= ewd)
      .OrderBy(k => k.Grenze)
      .FirstOrDefault();

which is obviously not nice since it involves DB-side calculation. The query is not performance critical in my case, but I'm still wondering: Is this a bug, or considered a feature? If so, why? Isn't this at least a breaking change that should have been announced somewhere? Or am I just to blind to find it?

I have tried to find any hint in the EF documentation - release notes, breaking changes, forums... but couldn't find anything.

I would be thankful for any hint as to relevant documentation, best practices or whatever else could be helpful for this problem.

EDIT: the EF core team has accepted this as a bug that's going to be fixed in 3.0.0 (https://github.com/aspnet/EntityFrameworkCore/issues/13908). Nice! In the meantime, does anyone have any ideas what I can do as a workaround? Do I manually have to look for all queries where this might happen?

Answer 1

It looks as though this may be a bug within EF Core -

if you notice .Where(k => k.Grenze >= 17.1) will cause it to generate the sql query using the value 17.1 as it doesnt generate it into a parameterised query - when working with local variables though it seems to be converting them into the same type as the attribute being compared to is mapped, this is causing rounding issues.

This is mostly intended behaviour from what I can tell to ensure consist types across queries, allowing for better indexing etc, though in the case of float,double and decimal it causes potential rounding issues.

Would suggest raising an issue for this on GitHub as it could (and in your case does) cause incorrect query results.