isdate() Sql Function

[myblindy]

I need the sql isdate() function in relation to short-circuiting certain further conditions. Something like (obviously not working code):

var mydate = new DateTime(2017, 1, 15);
var query = context.Table.Where(w => DateTime.IsDate(w.DateField) && Convert.ToDateTime(w.DateField) > mydate);

Where DateTime.IsDate could be an extension method used only for compiling EF queries (though a full implementation would be useful throughout the framework, no idea why it was never implemented). The query generated should look like:

select *
from table
where isdate(datefield) = 1 and convert(datetime, datefield) > @mydateparam

Since the database I'm provided has dates stored as strings (and not all valid), I'm kind of stuck on this. I have no other way of testing against dates if I can't first test if a date is valid.

Further technical details

EF Core version: 2 Preview, though the exact version shouldn't matter
Database Provider: Microsoft.EntityFrameworkCore.SqlServer

[smitpatel]

Related to #7368

[pmiddleton]

#7368 will support this. I am currently reviewing my code for a new PR and will make sure this scenario works.

[myblindy]

@pmiddleton so I would create an UDF that wraps isdate() if I understand correctly, right? That would work.

[ajcvickers]

The PR referenced above will allow this to be done. We would consider a PR for this specific function.

[pmiddleton]

Would we want to add this as an extension method of Datetime or DbFunctions? DbFunctions was added as the extension point for provider specific functions, but this one makes sense to hang off of DateTime.

[divega]

@pmiddleton in general we avoid defining extension methods for common types in EF Core or EF. Those can be become confusing especially if the implementation is specific to our stack. Longer term they can become even more confusing if a new member with similar name or semantics is added to the type in question. Having something on DbFunctions would be preferred. Incidentally the argument for IsDate() is of type string, not DateTime.

[ralmsdeveloper]

@divega can we still implement this in EF.Functions?

[divega]

@ralmsdeveloper Sure, it seems reasonable, although I think the original post is a bit misleading. There would be little value in a function that validates that something that was created as a DateTime represents a date or time.

As I said in my previous comment, ISDATE on SQL Server is for strings.

The name of the function isn't great either given that it works for strings that represent time and Date and Time are now separate types in SQL Server.

I haven't looked around, but it is likely similar functionality exists on other databases as well.

[ralmsdeveloper]

@myblindy I do not believe it is something so important to be implemented here in EFCore since other databases do not have the same resource, so I created an example that you can use, so you will have your own function that will be translated into SQL Server, with a simple touch of magic we can extract the maximum of EF, follows an example:

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query.Expressions;
using System;
using System.Linq;

namespace IsDateFunction
{
    public class Program
    {
        static void Main(string[] args)
        {
            using (var context = new IssueContext())
            {
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();
                
                // Case 1 - Bad Date
                context
                    .Set<Issue8488>()
                    .FirstOrDefault(p => EFCore.IsDate("01/00/2018") &&  p.Date > DateTime.Now);

                // Case 2
                context
                    .Set<Issue8488>()
                    .FirstOrDefault(p => EFCore.IsDate(p.DateStr) && p.Date > DateTime.Now);
            }
        }
    }

    public class Issue8488
    {
        public int Id { get; set; }
        public string DateStr { get; set; }
        public DateTime Date { get; set; }
    }

    public class IssueContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Server=.\SQL2017;Database=Issue8488;Trusted_Connection=True;");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Issue8488>();

            modelBuilder.HasDbFunction(typeof(EFCore)
                .GetMethod(nameof(EFCore.IsDate)))
                .HasTranslation(args =>
                {
                    return new SqlFunctionExpression(
                        "ISDATE",
                        typeof(bool),
                        args);
                });
        }
    }

    public static class EFCore
    {
        public static bool IsDate(string Data) => DateTime.TryParse(Data, out _);
    }
}

@divega @ajcvickers
If you believe this is really necessary, I will send a PR, otherwise I believe that the example will satisfy the needs of @myblindy .

[divega]

@ralmsdeveloper Nevertheless, having this readily available in EF.Functions would be nice. The same applies to any database built-in functions that don’t duplicate functionality already available on common BCL methods.

We haven’t been more proactive about doing this because we have other things that are important and because we wanted to hear what customers wanted. But this issue is an instance of that.

[ralmsdeveloper]

I'll get this.