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.

score 13 · Accepted Answer · edited May 23 '17 at 12:00

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.

I never realized it is not there in database. Thanks. – Mukesh Modhvadiya Apr 05 '18 at 12:19 — Mukesh Modhvadiya, Apr 05 '18 at 12:19

score 1 · Answer 2 · edited May 23 '17 at 12:15

1

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

Create class DatabaseFunctions and add the following code to it:

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

Add the following line to OnModelCreating in your DbContext

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

Use DatabaseFunctions.Date instead of EntityFunctions.TruncateTime.

edited May 23 '17 at 12:15

Community

1
1

answered Aug 22 '16 at 11:26

Svarog

2,188
15
21

This should be the answer. The query optimizer won't be able to optimize any query with a custom function so built in functions should be used – Drakarah Sep 21 '17 at 10:12
I'm in a situation where A Torres's answer won't work in my case. This answer looks extremely promising, but seems incomplete or incorrect. For instance, my EDI doesn't understand FunctionConvention. Can you explain your code a bit more completely? – Goose Oct 03 '17 at 20:58
@Goose - I'm not sure what EDI means, but if it doesn't understand FunctionConvention it looks like you are using a very old version of Entity Framework. Do you know which version are you using? – Svarog Oct 08 '17 at 11:54
"Integrated Development Environment". I have a fresh install of Visual Studio 2015 so I assume it's pretty recent. – Goose Oct 08 '17 at 14:16
1

Oh, you ment IDE. Visual Studio version is not necessarily connected to the version of Entity Framework you are using. Anyway, make sure you have a reference to Entity Framework 6 and that you added "using EntityFramework.Functions" to the top of your file (You might also need to add reference to EntityFramework.Functions, I don't remember for sure). – Svarog Oct 08 '17 at 14:47

score 0 · Answer 3 · answered Feb 13 '23 at 15:42

0

no need i delete it (EntityFunctions.TruncateTime) work with me

answered Feb 13 '23 at 15:42

Ahmed Nada

1
1

2

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Feb 16 '23 at 22:41

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

3 Answers3

Linked