CODEWITHSUNDEEP
c#mysqlasp.net-coreentity-framework-corepomelo-entityframeworkcore-mysql
Ahmed Mohamed
Ahmed Mohamed

Reputation: 19

Entity Framework core can't truncate DateTime using MySql

I'm using the Pomelo.EntityFrameworkCore.MySQL provider.
When I try to execute this Linq query:

MeetingUsers.Where(m => m.Meeting.FromDate >= DateTime.Now.AddYears(-2) && m.Meeting.FromDate <= DateTime.Now.AddYears(2)).Include(m => m.Meeting)
    .GroupBy(m => m.Meeting.FromDate.Date)
    .Select(g => new
    {
        Name = g.Key.Date.ToString("dd-MM-yyyy"),
        Count = g.Count(),
        Responses = g.GroupBy(x => x.Response).Select(x => new { Response = x.Key, Count = x.Count() }).ToList()
    });

I get this error:

InvalidOperationException: Unable to translate a collection subquery in a projection since either parent or the subquery doesn't project necessary information required to uniquely identify it and correctly generate results on the client side. This can happen when trying to correlate on keyless entity type. This can also happen for some cases of projection before 'Distinct' or some shapes of grouping key in case of 'GroupBy'. These should either contain all key properties of the entity that the operation is applied on, or only contain simple property access expressions.

I want to group the result by date only not by DateTime.

When I tried to remove the Date from the group by It works perfectly as following:

MeetingUsers.Where(m => m.Meeting.FromDate >= DateTime.Now.AddYears(-2) && m.Meeting.FromDate <= DateTime.Now.AddYears(2)).Include(m => m.Meeting)
    .GroupBy(m => m.Meeting.FromDate)
    .Select(g => new
    {
        Name = g.Key.Date.ToString("dd-MM-yyyy"),
        Count = g.Count(),
        Responses = g.GroupBy(x => x.Response).Select(x => new { Response = x.Key, Count = x.Count() }).ToList()
    });

But now It Groupby date and time. So how I can group by date only using EF Core with MySql?

Upvotes: -1

Views: 308

Answers (1)

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27436

I would suggest in this case, do grouping on the client side.

var minimal = await MeetingUsers
    .Where(m => m.Meeting.FromDate >= DateTime.Now.AddYears(-2) && m.Meeting.FromDate <= DateTime.Now.AddYears(2))
    .Select(m => new { m.Meeting.FromDate, m.Response })
    .ToListAsync();

var result = minimal
    .GroupBy(m => m.FromDate.Date)
    .Select(g => new
    {
        Name = g.Key.ToString("dd-MM-yyyy"),
        Count = g.Count(),
        Responses = g.GroupBy(x => x.Response).Select(x => new { Response = x.Key, Count = x.Count() }).ToList()
    });

Upvotes: 1

Related Questions