Hi. I'm implementing asp.net core 3.1. I have a column in SQLServer called ApiRequestDate which is of type nvarchar and in my model is of type string. The data is stored in that column in the format yyyy/mm/dd. Now I want to group by year and the month in order to find some related data for that time. For doing so, I wrote query like the following:
var total = from t1 in _context.Apiapp
let tg = new
{
t1.ApiRequestDate.Value.Year,
t1.ApiRequestDate.Value.Month
}
group tg by new { t1.ApiRequestDate.Value.Year, t1.ApiRequestDate.Value.Month } into newGroup
Select new
{
Month = newGroup.Select(n => n.ApiRequestDate.Value.Month).First(),
ReqPerMonthCount = newGroup.Count()
};
var totalCount = total.ToList();
But it doesn't work and it has errors. One of the errors is 'Value" is unknown. And the other on is, the name Select does not exist in the current context.
I appreciate if anyone can suggest me a solution for it.
What I have tried:
var total = from t1 in _context.Apiapp
let tg = new
{
t1.ApiRequestDate.Value.Year,
t1.ApiRequestDate.Value.Month
}
group tg by new { t1.ApiRequestDate.Value.Year, t1.ApiRequestDate.Value.Month } into newGroup
Select new
{
Month = newGroup.Select(n => n.ApiRequestDate.Value.Month).First(),
ReqPerMonthCount = newGroup.Count()
};
var totalCount = total.ToList();