Home > Blockchain >  Getting all months in an Year from Entity Framework core query
Getting all months in an Year from Entity Framework core query

Time:11-18

I have done following EF.core(3.1/3.2) query

var monthlySalesList = context.Bids.Include(r => r.AllRequest).ThenInclude(r => r.Category).Where(b => b.UID== UID && (b.Status == MyStatus.Awarded || b.Status == MyStatus.Completed))
                    .GroupBy(a => new { Month =a.InsertedDate.Month })

                  .Select(g => new MyServiceList()
                  {
                      Key = g.Key.ToString(),
                      Month = g.Key.Month.ToString(),
                      Total= g.Sum(s => s.totalBudget)

                  }).ToList();

I am not getting all months in an year instead it displays only 2 months say ( 10,11) with total.In above query Mystatus is an ENUM class and MyserviceList Model class contains get & set such as key,month,sum and total .

I am getting only

-----------------
Months total
------------------
10   1234
11  1212

How can I get remaining months with zero value.

-----------------
Months total
------------------
1    0 
2    0
3    0
4    0
5    0
6    0
7    0
8    0
9    0
10   1234
11  1212
12  0 

CodePudding user response:

  1. Define another list
  2. Make a for loop after "monthlySalesList" and make query from the list.
  3. if you did not get data, set 0 on new list, otherwise set value .

for example :

Class : public class ReportData { public int month { get; set; } public int total { get; set; } }

Code :

List reportData = new List();

        for (int i = 1; i <= 12; i  )
        {
            try
            {
                Bids bids = new Bids();

                bids = monthlySalesList.Where(t => t.month == i).FirstOrDefault();


                if(bids == null)
                {
                    reportData.Add(new ReportData() {
                    month = i,
                    total = 0
                    });
                }
                else
                {
                    reportData.Add(new ReportData()
                    {
                        month = i,
                        total = bids.Month
                    });
                }
            }
            catch(Exception ex)
            {

            }


        }
  • Related