Question 88312 in ASP.NET Question posted in
The official documentation can be found here.

I want to convert this SQL query to linq using C# – Asp.net

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')
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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,
  }
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search