The specified type member 'DateTime' is not supported in LINQ to Entities

Question

I am using the latest development version of the connector - 6.3.3 beta to connect to a mysql database via the Entity Framework 4 in VS2010.

I have the following Linq statement which uses a TIMESTAMP column - createdDate - in the query. Here is the code:

        int timeThreshold = 5;
        DateTimeOffset cutoffTime = DateTime.Now.AddMinutes(-timeThreshold);

        using (var context = new opusismEntities())
        {
            var unprocessedMessages = from m in context.messages
                                      where m.createdDate <= cutoffTime
                                      select m;
            try
            {
                foreach (var message in unprocessedMessages)
                {
                    int gfff = 5;
                }
            }
            catch (Exception e)
            {
                string exceptionString = e.InnerException.ToString();
            }

        }

The CLR is throwing the following exception:

"MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered during command execution. ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Unable to serialize date/time value.\r\n at MySql.Data.Types.MySqlDateTime.MySql.Data.Types.IMySqlValue.WriteValue(MySqlPacket packet, Boolean binary, Object value, Int32 length)\r\n at MySql.Data.MySqlClient.MySqlParameter.Serialize(MySqlPacket packet, Boolean binary, MySqlConnectionStringBuilder settings)\r\n at MySql.Data.MySqlClient.Statement.SerializeParameter(MySqlParameterCollection parameters, MySqlPacket packet, String parmName)\r\n at MySql.Data.MySqlClient.Statement.InternalBindParameters(String sql, MySqlParameterCollection parameters, MySqlPacket packet)\r\n at MySql.Data.MySqlClient.Statement.BindParameters()\r\n at MySql.Data.MySqlClient.Statement.Execute()\r\n at MySql.Data.MySqlClient.PreparableStatement.Execute()\r\n at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)\r\n at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)\r\n at MySql.Data.Entity.EFMySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)\r\n at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)"

I’ve attempted to follow the advice from the following link: http://bugs.mysql.com/bug.php?id=52550

by declaring a DateTime instead of DateTimeOffset:

DateTime cutoffTime = DateTime.Now.AddMinutes(-timeThreshold);

...
var unprocessedMessages = from m in context.messages
                                      where m.createdDate.DateTime <= cutoffTime
                                      select m;

and use the format .createdDate.DateTime, but Entity Framework doesn’t like it and returns back an exception:

The specified type member 'DateTime' is not supported in LINQ to Entities

---

This was reported as a bug in previous versions of NET/Connector.

Hopefully the GA version 6.3.4 will fix this issue, but it still persists in 6.3.3 beta.

Answer 1

Well this is LINQ to Entities issue..it does not allow you to use properties/methods like that in a query...not only for DateTime but for others too.

Get the value in another variable and use that variable in the query like

DateTime dt=createdDate.DateTime;

Or just remove DateTime...because it's value is the same...that should get rid of exception.But I am not sure what you are trying achieve though...