CODEWITHSUNDEEP
c#entity-framework
Raj
Raj

Reputation: 343

Return row count per day for current week from Sunday to Saturday Entity Framework C#

I am trying to return number of records per day for a week. My code returns me only records for the day that has records, I want to return 0 if no records were inserted that day. Following is my code.

//Desired Result Sun to Sat = [0,3,5,1,0,18,0] , currently I only get [3,5,1,18] // DateTime.Now.FirstDayOfWeek() is extension method that return first day of the week. Thank you for help. 

 var records = logs.Where(x=>x.date> DateTime.Now.FirstDayOfWeek())
            .GroupBy(x => x.date.Day)
            .Select(x =>
                x.Any()?x.Count():0 
           ).ToArray();

Upvotes: 1

Views: 1009

Answers (3)

NetMage
NetMage

Reputation: 26936

Using another extension method to create a range of dates:

public static IEnumerable<DateTime> Range(this DateTime startDate, int numberOfDays) => Enumerable.Range(0, numberOfDays).Select(e => startDate.AddDays(e));

You can GroupJoin with the week of dates and create the array:

var recordgroups = logs.Where(x => x.date > DateTime.Now.FirstDayOfWeek())
           .GroupBy(x => x.date.Day);
var records = DateTime.Now.FirstDayOfWeek().Range(7)
                  .GroupJoin(recordgroups, wd => wd.Day, lg => lg.Key, (_, lg) => lg.Any() ? lg.First().Count() : 0).ToArray();

Upvotes: 1

Emre Kabaoglu
Emre Kabaoglu

Reputation: 13146

You can't join with a List and DbSet because Linq to Entities can't translate a List to SQL language. So, It seems that there is no option except to fill the empty days in a loop;

var records = logs
    .GroupBy(x => SqlFunctions.DatePart("dw", x.date))
    .Select(x =>
        new WeekCounts
        {
            Day = x.Key,
            Count = x.Count()
        }).ToList();

for (int i = 0; i < 7; i++)
{
    if (records.All(x => x.Day != i))
    {
        records.Add(new WeekCounts { Day = i, Count = 0 } );
    }
}

public class WeekCounts
{
    public int? Day;
    public int Count;
}

Upvotes: 1

Muhammad Soliman
Muhammad Soliman

Reputation: 23866

I am not sure this solution will work, but just give it a try, simply you can do left outer join with static list, below is just snippet (pseudo code) how to do it.

List<String> weekdays = new List<String>();
weekdays.Add("Monday");
weekdays.Add("Tuesday");
...
...
...
//Left outer join to display forcibly all weekdays and join them with your log's data and continue to grouping. 


var result = from w in weekdays
join l in logs into joined 
from j in joined.DefaultIfEmpty()

If it did not work, please leave a comment as I've another solution for you to make it workable :)

If it did not work, check this, after returning the results, left join your static data (weekdays) with the results (records) which means you've returned only the records that their counts have data (>0) but for only those that don't have data at all, it should be coming as zero after the join.

var result = from w in weekdays
join l in records into joined 
from j in joined.DefaultIfEmpty()

Upvotes: 1

Related Questions