GroupBy date portion of DateTime in Linq2db.MySQL C#

Question

I am trying to group by the date portion of a DateTime in Linq2db.MySQL.

My first attempt is:

using (var db = MySqlTools.CreateDataConnection(connectionString))
      {
         {
            var query = from t in db.GetTable<Change_log>()
                        .Where(t => t.Action == 15)
                        .GroupBy(t => new { t.Change_date.Date, t.Reference_no }).Where(grp => grp.Count() > 1)
                        .SelectMany(t => t).ToList()
                        .OrderBy(t => t.Change_date)
                        select t;

But DateTime.Date isn’t recognized. I have tried installing MySQL.Data.Entity and Entity Framework and changing the GroupBy to

.GroupBy(t => new { dd = DbFunctions.TruncateTime(t.Change_date), t.Reference_no }).Where(grp => grp.Count() > 1)

but then I get the error:

‘TruncateTime (convert(selectParm.Change_date))’ Cannot be converted to SQL

Is this even possible in Linq2db?

Edit:

A proposed solution in another question was to create a function in the database which I have now done(fTruncateTime), returning Date(DateTime), but I don't know how to use it in my GroupBy clause.

Possible duplicate of [Canonical Function "EntityFunctions.TruncateTime" does not exist in MYSQL](https://stackoverflow.com/questions/19714022/canonical-function-entityfunctions-truncatetime-does-not-exist-in-mysql) — mjwills, Oct 22 '17 at 08:42

score 0 · Accepted Answer · answered Oct 23 '17 at 09:12

I finally gave up on one query and split it up into two queries. This gave me what I wanted.

var query = from t in db.GetTable<Change_log>()
            .Where(t => t.Action == 15)
            .ToList()
            .OrderBy(t => t.Change_date)
             select new
             {
                dd = t.Change_date.ToShortDateString(),
                rr = t.Reference_no,
             };

var query2 = from q in query
             .GroupBy(q => new { q.dd, q.rr })
             .Where(grp => grp.Count() > 1)
             .Select(q => q.First())  //  = select distinct
             .OrderBy(q => q.rr)
             .ToList()
             select q;

GroupBy date portion of DateTime in Linq2db.MySQL C#

1 Answers1