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)
.CountAsync();
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 ...
Edit:
Here is the SQL Entity Framework generates ...
SELECT
`GroupBy1`.`A1` AS `C1`
FROM (SELECT
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 6.9.6.0
source to share
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();
source to share
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
.Customers
.Where(x => x.RegisterDate > yesterday)
.ToList()
.Where(x => x.RegisterDate.Date == DateTime.Now.Date)
.Count();
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.
source to share