Canonical Function "EntityFunctions.TruncateTime" does not exist in MYSQL

Question

I'm trying to run this query:

DateTime DDate=DateTime.Today; //Today's date without Time
var v= db.measurements.Where(m => EntityFunctions.TruncateTime(m.InDate) == DDate);

It just returns objects where those two dates are equal, ignoring the time part.

But I receive:

{"FUNCTION [database].TruncateTime does not exist"}

StackTrace:

at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.Entity.EFMySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

I'm using:

• C# Visual Studio 2010
• EntityFramework 4 database first
• NetFramework 4
• MYSQL Server 5.6

The version of MySQL.Data and MySQL.Data.Entity is 6.6.5.0

TruncateTime is supported by MySQL.

Same thing happened to this person.

Answer 1

I couldn't resolve it, so I just created a Function named "TruncateTime" in the database.

Create FUNCTION TruncateTime(dateValue DateTime) RETURNS date
return Date(dateValue);

And it works, but I don't like it.

These people did similar things:

Alternative to EntityFunctions.AddSeconds for MySQL

CurrentUtcDateTime does not exist - Entity Framework and MySql

So now I think that might be unnecessary and I can just call it directly from the database and still get entities, something like this:

var x = db.ExecuteStoreQuery<Measurement>(@"SELECT field1,field2
FROM   Measurements
WHERE  Date(InDate) = {0}", DDate);

And that's all.

Answer 2

The approach in A Torres' response works, but it felt a bit hacky for me, so I have found another approach (This works for EF6, for EF5 a similar approach exists, but I can't test it):

1. Create class DatabaseFunctions and add the following code to it:

   [Function(FunctionType.BuiltInFunction, "Date")]
   public static DateTime? Date(DateTime? dateValue) 
       => Function.CallNotSupported<DateTime>();
   

2. Add the following line to OnModelCreating in your DbContext

   protected override void OnModelCreating(DbModelBuilder modelBuilder){
       modelBuilder.Conventions.Add(new FunctionConvention<DatabaseFunctions>())
       // ...
   }
   

3. Use DatabaseFunctions.Date instead of EntityFunctions.TruncateTime.