EntityFramework 6.1.3 and MySQL DbFunctions.TruncateTime doesn't exist?

Attempting to get the count of things entered today using this query.

var today = DateTime.Today;
var unitsPassedToday = await this.db.Sessions
        .Where(x => DbFunctions.TruncateTime(x.FinishTime) == today)


Getting the following exception:

FUNCTION db.TruncateTime does not exist


I swear I've used this feature before. I don't have any references to System.Data.Entity, this is the only solution I found using google search. Is this some kind of bug in MySQL EntityFramework implementation? Obviously, I can just write a stored procedure that returns this information, but I would like to know why this doesn't work?

MySQL server is 5.5.23-enterprise.

Feeling dumb right now, it looks like it should be a simple problem and I'm just missing something simple ...


Here is the SQL Entity Framework generates ...

`GroupBy1`.`A1` AS `C1`
COUNT(1) AS `A1`
FROM `sessions` AS `Extent1`
WHERE (TruncateTime(`Extent1`.`finish_time`)) = @p__linq__0) AS `GroupBy1`


I'm pretty sure TruncateTime is not a valid MySQL function, why call it? I should probably add that I'm using EDMX diagrams first, not code. I added codeConfigurationType to my web.config to set up MySQL though ...

<entityFramework codeConfigurationType="MySql.Data.Entity.MySqlEFConfiguration, MySql.Data.Entity.EF6">


MySql.Data.Entity - Version


source to share

2 answers

I found a solution at codeproject.com

I tested the solution, worked for me! hope this helps

var data1 = context.my_model.Where(x => x.region_name == "Hong Kong" 
                        && x.price_date.Value.Year == dt.Year
                        && x.price_date.Value.Month == dt.Month
                        && x.price_date.Value.Day == dt.Day).ToList();




To solve this problem, people use a stored procedure.

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


So it's very, very messy. But apart from using a stored procedure, I used this option.

var yesterday = DateTime.Now.AddDays(-1);

var newCustomersCount = _context
  .Where(x => x.RegisterDate > yesterday)
  .Where(x => x.RegisterDate.Date == DateTime.Now.Date)


We select all records that were newer than yesterday..ToList () runs the query, and then we filter what we want. This method selects the minimum required records.



All Articles