Translation of ISNUMERIC under SF.Functions

[itx-digital]

Hello,
Could you please add a translation to SQL Server's ISNUMERIC under EF.Functions ?
Thank you.
Best regards.

Originally posted by @evannah in #23078 (comment)

[roji]

Until this is done in the provider, you can map this as a DbFunction yourself - see the code sample below.

Using DbFunction

await using var ctx = new BlogContext();
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

_ = await ctx.Blogs.Where(b => ctx.IsNumeric(b.Name)).ToListAsync();

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    static ILoggerFactory ContextLoggerFactory
        => LoggerFactory.Create(b => b.AddConsole().AddFilter("", LogLevel.Information));

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0")
            .EnableSensitiveDataLogging()
            .UseLoggerFactory(ContextLoggerFactory);

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasDbFunction(IsNumericMethodInfo)
            .HasName("ISNUMERIC")
            .IsBuiltIn();
    }

    private static readonly MethodInfo IsNumericMethodInfo = typeof(BlogContext)
        .GetRuntimeMethod(nameof(IsNumeric), new[] { typeof(string) });

    public bool IsNumeric(string s) => throw new NotSupportedException();
}

public class Blog
{
    public int Id { get; set; }
    public string Name { get; set; }
}

[roji]

@smitpatel @maumar note that with the above I get:

SELECT [b].[Id], [b].[Name]
FROM [Blogs] AS [b]
WHERE ISNUMERIC([b].[Name]) = CAST(1 AS bit)

The CAST(1 AS bit) looks like it can go away, some search condition improvement?

[smitpatel]

IsNumeric returns int in T-Sql.
No changes to search conditions required.

[roji]

Ah weird...

[ajcvickers]

Note from triage: If there is an appropriate BCL method to translate, then we should do that instead. However, I don't think we have identified such a method at this time.

[smitpatel]

Design question for @dotnet/efteam
What should be the return type of IsNumeric?
It returns int in T-Sql. In PR we added bool. Looking at ef6 code, it returns int it seems. (Note it should not return int? as it can never be null value.
https://github.com/dotnet/ef6/blob/04a6ae5556f38fb3ba622dcb142a6c6b37101dd9/src/EntityFramework.SqlServer/SqlFunctions.cs#L1607-L1615

[ajcvickers]

@smitpatel Did you get an answer on this?

[smitpatel]

No. I will bring it to next design meeting.

[smitpatel]

Design meeting decision: We decided to keep this as bool return value. It is minor breaking change from ef6 but bool here gives better query writing to customer given the function definition.