Entity Framework Core DbFunction for square root

Question

I'm porting my site over from .NET Framework / MVC 5 / EF6 to .NET Core 3.1 MVC / EF Core. MSSQL Server for the database.

EF6 has SqlFunctions of which one method is SquareRoot, which translates to using SQRT in sql queries against MSSQL Server.

EFCore does not have the SqlFunctions class. It does have an equivalent DbFunctions class but this is missing lots of methods.

After some googling about I've come up with the following for translating the LINQ query to sql:

MyDbFunctions.cs

public static class MyDbFunctions
{
 public static double? SquareRoot(double? arg) => throw new Exception();
}

OnModelCreating method in DbContext

var sqrtMethodInfo = typeof(MyDbFunctions).GetMethod(nameof(MyDbFunctions.SquareRoot));
modelBuilder
 .HasDbFunction(sqrtMethodInfo)
 .HasTranslation(args => SqlFunctionExpression.Create("SQRT", args, typeof(double?), null));

Usage:

from a in context.Posts
let sqrt = MyDbFunctions.SquareRoot(a.someColumnWithDoubleValue)
where sqrt < 1337
select new MyViewModel
{
 Sqrt = sqrt
 ...
};

This seems to work nicely. I can see the use of SQRT within the generated query (using SQL Server Profiler) and the where clause being applied. It also looks near identical to what EF6 generates.

I'm wondering if any EF Core pros can see anything wrong/pitfalls with the implementation (or improve on it)?

• Ewan: would it not be more in vogue to make this an extension method of DBFunctions?
• Peter Csala: @suraj How about using Math.Pow(a.someColumnWithDoubleValue, 0.5)
• suraj: Out of curiosity I've just discovered that even Math.Sqrt is correctly translated to use SQRT in the generated query - so there's not even any need to use this custom function in EF Core.
• Peter Csala: @suraj No I don't think so. Simply just not all of the Math functions have mapper. Current translators, 5.0's translators. Related github issue
• suraj: @PeterCsala not at all, anything that helps :)

Answer 1

Here I capture the main essence of the comments.

Facts

• As the OP has mentioned the Sqrt functionality is no exposed on the EF.Functions in EFCore
  • EF 6 exposed it through SqlFunctions
• Math.XYZ functions can be used in Linq2Entities
  • Not all of the functions have translator to the corresponding SQL function, current state
  • As a part of this issue more function translators will in v5

Solutions

• Math.Pow can be used as well, like this: Math.Pow(a.someColumnWithDoubleValue, 0.5)
  • This will be translated into POWER
• Math.Sqrt can be used as well, like this: Math.Pow(a.someColumnWithDoubleValue)
  • This will be translated into SQRT
  • If the data type would be decimal then explicit cast is needed:
    • Math.Pow((double)a.someColumnWithDecimalValue)
    • Translated into SQRT(CONVERT(Float, ...)