I'm not surprised Entity Framework doesn't know how to convert that to a SQL query! Aside from the fact that the
GetPersianDaysDiffDate
function probably isn't mapped to a SQL function, it's not clear which dates within the group you want to compare.
For example, if you have five records grouped into January 2020, which single record are you going to take the dates from? You haven't specified an
OrderBy
on the
FirstOrDefault
query, so there's no way to know.
Perhaps something like this might work:
var query = from t1 in _context.Apiapp
join t2 in _context.ApiAppHistory on t1.Id equals t2.ApiAppId
join t3 in _context.EntityType on t2.LastReqStatus equals t3.Id
where t1.IsDeleted == false && t1.LastRequestStatus == t2.Id && t3.Name == "granted"
let tg = new
{
Year = t1.ApiRequestDate.Substring(0, 4),
Month = t1.ApiRequestDate.Substring(5, 2),
t2.LastReqStatus,
t1.ApiRequestDate,
t2.Date
}
group tg by new { tg.Year, tg.Month, tg.LastReqStatus } into g
select new
{
g.Key.Year,
g.Key.Month,
ApiRequestDate = g.Min(i => i.ApiRequestDate),
Date = g.Max(i => i.Date)
};
var avgDateDiff = query.AsEnumerable()
.GroupBy(x => new { x.Year, x.Month }, (key, g) => new
{
key.Year,
key.Month,
Avg = g.Average(y => GetPersianDaysDiffDate(y.ApiRequestDate, y.Date))
})
.ToList();