Fast line counter

Question

I have the following query that returns the number of logins per day from a specific date.

var sot =   from uts in DataContext.UserTrackingStatistics
              let startDate = new DateTime(2009, 10, 01) 
              where uts.LastLogin >= startDate
              group uts by uts.LastLogin.Date into myGroup
              orderby myGroup.Key.Date
              select new { Count = myGroup.Count() , myGroup.Key.Date};

I would like to say that the count was 0 for a given day, and did not return anything. How can I do this in this query?

Answer 1

You cannot do this only with LINQ-to-SQL, since you will have to use unionin your query data that does not actually exist that LINQ-to-SQL cannot do.

To do this, you need to fill in the gaps on the client side. I am not against VS right now, but the general approach would be as follows:

• ( , , , .
• Enumerable.Range 0 , Select, . , L2S; ,
• , ( LINQ, ) Date

0 .

, , , , .

var allDates = Enumerable.Range(0, (DateTime.Today - startDate).TotalDays)
              .Select(i => new { Count = 0, Date = startDate.AddDays(i) });

var fullResults = from d in allDates
                  join r in results on d.Date == r.Date
                  from oj in r.DefaultIfEmpty()
                  select new { Count = oj == null ? 0 : oj.Count, Date = d.Date };