I want to convert this SQL query to Linq in C#:
select
count(*),
FORMAT (CreatedDate, 'MM') as months,
FORMAT (CreatedDate, 'yyyy') as year
from
ProviderPosts
group by
FORMAT (CreatedDate, 'MM'),
FORMAT (CreatedDate, 'yyyy')
CodePudding user response:
The following code is an almost exact translation of your query, however depending on the LINQ provider it may not be able to translate it to SQL.
from pp in ProviderPosts
group pp by new DateTime(pp.CreatedDate.Year, pp.CreatedDate.Month, 1) into g
select new {
Count = g.Count(),
months = g.Key.ToString("MM"),
year = g.Key.ToString("yyyy"),
}
In SQL, it is more efficient to group by
EOMONTH
orDATEFROMPARTS
rather thanFORMAT
CodePudding user response:
Try to do not convert to anything. Use .Year
and .Month
var query =
from pp in ProviderPosts
group pp by new { pp.CreatedDate.Year, pp.CreatedDate.Month } into g
select new
{
Count = g.Count(),
g.Key.Month,
g.Key.Year,
}