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:
- Define another list
- Make a for loop after "monthlySalesList" and make query from the list.
- 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)
{
}
}