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)?

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, ...)