How to use MySQL function 'Date' in EF Linq

Question

I want to group by Date of datetime cloumn(eg.CreateTime ='20017-01-01 01:01:01') to use Sum aggregate functions.

EntityFramework 6 Code first
NetFramework 4.5

MYSQL Server 6.9.9

 var myData = from certifiedRecord in DbSet
        join shopInfo in DbContext.ShopInfos
        on certifiedRecord.ShopId equals shopInfo.ID into joinedCertify
        from shopCertifiedRecord in joinedCertify
        group shopCertifiedRecord
        by EntityFunctions.Date(certifiedRecord.CreateTime)
        into g
        orderby g.Key
        select new ShopCertifiedDayInfoResponse
        {
            Day = g.Key.Value,
            NormalCount = g.Sum(t => t.CertifiedType.CompareTo(1)),
            VipCount = g.Sum(t => t.CertifiedType.CompareTo(2))
        };

But there is not EntityFunctions.Date function for EF with Mysql.

Is there a way to solve the problem?

Now I just use the CustomQuery to solve the Problem.

I Just want to know how to use EntityFunctions(maybe another thins) to solve the Problem.In another way, I want to using Linq for EF to solve the problem.

There is a DateTime.Parse(string) that will convert a string date to a DateTime object. — jdweng, Jul 14 '17 at 02:09
Based on [this answer](https://stackoverflow.com/a/11754787/6741868) you could either use [DbFunctions.TruncateTime Method](https://msdn.microsoft.com/en-us/library/system.data.entity.dbfunctions.truncatetime(v=vs.113).aspx#M:System.Data.Entity.DbFunctions.TruncateTime(System.Nullable%7BSystem.DateTime%7D)) (EF 6.0+) or [EntityFunctions.TruncateTime Method (Nullable)](https://msdn.microsoft.com/en-us/library/dd395596.aspx) for earlier versions of EF. — Keyur PATEL, Jul 14 '17 at 02:11
I want to `Group By ` a date extract from DateTime Column(eg. CreateTime),so I need to use 'Date' function (Mysql function). — huoxudong125, Jul 14 '17 at 02:13
Which one did you try? (based on your EF version), and also what error does it give. It would be great if you posted the code you tried. — Keyur PATEL, Jul 14 '17 at 02:19
@KeyurPATEL `FUNCTION xxxxxdb.TruncateTime does not exist` the EF throw the error. tow function I have tried, but all failed. — huoxudong125, Jul 14 '17 at 02:24
Why the "xxxxxdb"? Based on your version, it will either be `by System.Data.Entity.DbFunctions.TruncateTime(certifiedRecord.CreateTime)` or `by System.Data.Objects.EntityFunctions.TruncateTime(certifiedRecord.CreateTime)`. I still don't know your EF version. — Keyur PATEL, Jul 14 '17 at 02:29
@KeyurPATEL `DbFunctions.TruncateTime` works in SQLServer but not in MySQL. — huoxudong125, Jul 14 '17 at 06:22

score 0 · Answer 1 · answered Jul 14 '17 at 03:01

0

It is a hacky solution (as suggested here as well), but you could just create the function in your database:

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

And after that your code should work.

answered Jul 14 '17 at 03:01

Keyur PATEL

2,299
1
15
41

score 0 · Answer 2 · answered Jul 14 '17 at 18:06

0

I had a similar problem that I solved by putting .ToList() right before I got the date. Some methods can't operate on unexecuted queries, so materializing the list beforehand could solve the problem.

answered Jul 14 '17 at 18:06

Christian Boyer

48
5

How to use MySQL function 'Date' in EF Linq

2 Answers2