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 or DATEFROMPARTS rather than FORMAT

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,
  }