CODEWITHSUNDEEP
c#linq
Parshant Dutta
Parshant Dutta

Reputation: 500

How to get the records monthly using linq for current year

I have to get the records count Monthly using linq when I have dates in created on. I need a list with 12 months of records count. If there are no records for a particular month then I have to get 0 instead of null else I need the original count of that month. I have same thing done in sql please review the query:-

SELECT 
      Count(JobId)
     ,MONTHNAME(createdOn) 
FROM bec_dev.jobs 
WHERE year(createdOn) = year(now()) 
GROUP BY month(createdOn);

I want same thing in linq

Upvotes: 0

Views: 1916

Answers (2)

Ravi Kumar
Ravi Kumar

Reputation: 73

Please try this. I think this will work for you. :)

     var months = Enumerable.Range(1, 12)
            .Select(x => new {
                year = DateTime.Now.Year,
                month = x
            });

var data = months.GroupJoin(
        ObjContext.yourtable.Where(j => j.ClientId == jobsFilterRequest.ClientId 
        && j.CreatedOn.Year == DateTime.Now.Year),
        m => new { month = m.month, year = m.year },
        revision => new {
            month = revision.CreatedOn.Month,
            year = revision.CreatedOn.Year
        },
        (p, g) => new ResponseMonthlyCount
        {
            MonthName = MonthNames[p.month -1],
            TotalJobs = g.Count()
        }).ToList();

Upvotes: 1

schlonzo
schlonzo

Reputation: 1404

var result = <DataContext>.<YourTable>
.Where(p => p.createdOn.Year == DateTime.Now.Year)
.GroupBy(p => p.createdOn.Month)
.Select(p => new {Month = p.Key, Count = p.Count()});

After that use the Globalization functions that the Framework offers, to resolve the month number to a month name, e.g.

System.Globalization.DateTimeFormatInfo dtfi = new 
System.Globalization.DateTimeFormatInfo();
string strMonthName = dtfi.GetMonthName(monthNumber).ToString();

Upvotes: 3

Related Questions