29437

Linq to Entity, selecting group without value

Question:

I would like to be able to group all my data by months even if some months doesn't contain any data. At this moment, I can group but the data returned contain only months with data.

Here is how my code look like:

var twelveMonthAgo = date.AddMonths(-12).Date; var twelveMonthAgoFirstOfMonth = new DateTime(twelveMonthAgo.Year, twelveMonthAgo.Month, 1, 0, 0, 0, 0); var data = (from i in Database.Users where i.RegisterDate >= twelveMonthAgoFirstOfMonth group i by new {y=i.RegisterDate.Year,m = i.RegisterDate.Month} into g select new UserStatistic{ Date = EntityFunctions.CreateDateTime(g.Key.y, g.Key.m, 1, 0, 0, 0) , UserCount = g.Count(o => o.Id) }); //The code below is what I would like to remove var toReturn = new List<UserStatistic>(); var allData = data.ToList(); DateTime datei = twelveMonthAgoFirstOfMonth; while (datei.Year<= date.Year && datei.Month<=date.Month){ var info = allData.SingleOrDefault(x => x.Date.HasValue && x.Date.Value.Year == datei.Year && x.Date.Value.Month == datei.Month); toReturn.Add(info ?? new UserStatistic { Date = datei, UserCount = 0, PaymentPaid = 0 }); datei = datei.AddMonths(1); } return toReturn.AsQueryable();

As you can see, the code under the comments build a collection of the last 12 months and check if some data has been out of the database and fill up the collection if some exist, otherwise put 0.

How can I do all that without having to do the code below the comment?

Answer1:

Here's some code that uses a group join to a subquery get your desired results:

DateTime date = DateTime.Today; DateTime firstOfMonth = new DateTime(date.Year, date.Month, 1); DateTime twelveMonthAgoFirstOfMonth = firstOfMonth.AddMonths(-12); // Generate a collection of the months and years for the last 12 months var monthYears = Enumerable.Range(-12, 12).Select(monthOffset => { DateTime monthDate = firstOfMonth.AddMonths(monthOffset); return new { y = monthDate.Year, m = monthDate.Month }; }); // Go through the list of months and years and join them to the users retrieved from the database in the subquery. var data = from monthYear in monthYears join i in (from i in Database.Users where i.RegisterDate >= twelveMonthAgoFirstOfMonth && i.RegisterDate < firstOfMonth select i) on monthYear equals new { y = i.RegisterDate.Year, m = i.RegisterDate.Month } into gj select new UserStatistic() { Date = new DateTime(monthYear.y, monthYear.m, 1), UserCount = gj.Count() });

This can also be expressed as a group in the subquery with a left outer join:

DateTime date = DateTime.Today; DateTime firstOfMonth = new DateTime(date.Year, date.Month, 1); DateTime twelveMonthAgoFirstOfMonth = firstOfMonth.AddMonths(-12); var monthYears = Enumerable.Range(-12, 12).Select(monthOffset => { DateTime monthDate = firstOfMonth.AddMonths(monthOffset); return new { y = monthDate.Year, m = monthDate.Month }; }); var data = (from monthYear in monthYears join i in (from i in Database.Users where i.RegisterDate >= twelveMonthAgoFirstOfMonth && i.RegisterDate < firstOfMonth group i by new {y = i.RegisterDate.Year, m = i.RegisterDate.Month} into g select new { Key = g.Key, UserCount = g.Count() }) on monthYear equals i.Key into j from k in j.DefaultIfEmpty() select new UserStatistic() { Date = new DateTime(monthYear.y, monthYear.m, 1), UserCount = k != null ? k.UserCount : 0 });

Since I don't have your EF model, you'll have to try it and see if you need to replace new DateTime with EntityFunctions.CreateDateTime.

Answer2:

I haven't compiled and debugged this but It should give an idea of how I think you could fix yor problem. I use Enumerable.Range to generate a set of place holders for the months you require. I then had to choose between left joining the placeHolders to the data and merging or, just concatenating the ommissions.

I also reformated the code from the question a bit to help me understand it.

IQueryable<UserStatistic> GetPastMonths(int months) { var limitDay = date.AddMonths(-months).Date; var limit = new DateTime(limitDay.Year, limitDay.Month, 1, 0, 0, 0, 0); var placeHolders = Enumerable.Range(0, months + 1) .Select(m => new UserStatistic { Date = limit.AddMonths(-m), UserCount = 0 }); var data = Database.Users .Where(i => i.RegisterDate >= limit) .GroupBy(i => new {y=i.RegisterDate.Year, m = i.RegisterDate.Month}) .Select(g => new UserStatistic { Date = EntityFunctions.CreateDateTime( g.Key.y, g.Key.m, 1, 0, 0, 0), UserCount = g.Count(o => o.Id) }); return data.Concat(placeHolders .Where(p => !data.Any(d => d.Date == p.Date))) .AsQueryable(); }

The cocatenating the ommissions approach is almost garaunteed to give the data back in an illogical order but, you have no garauntees anyway unless you use an order by clause.

Recommend

  • Iterating through csv records based on the version of the record via Python
  • Linq Method Syntax - Can not convert IQueryable to Bool
  • How to synchronize two onloadend functions in angular 2?
  • LINQ's *OrDefault throws exceptions in MVC3 + ActiveRecord
  • Cannot rename Discriminator column in Entity Framework 4.1 Code First database
  • LINQ's *OrDefault throws exceptions in MVC3 + ActiveRecord
  • How to update information on MKPinAnnotationView?
  • Getting Results from Search entity for twitter search
  • Visibility of individual items in MvcSiteMapProvider?
  • Db Resource Authorization in EF Core
  • Projection of single entities in EF with extension methods
  • Linq distinct based on two columns
  • Dynamically generated lookup key for IQueryable
  • Updating entry adds a new entry to database
  • Use of this Javascript
  • SSO with signing and signature validation doesn't work
  • How to show dropdown in excel using jrxml (jasper api)?
  • Importing jscolor library in angular 2
  • Release, debug version and Authorization Google?
  • Websockets service method fails during R startup
  • Alternatives to the OPTIONAL fallback SPARQL pattern?
  • How to get next/previous record number?
  • Apache 2.4 - remove | delete | uninstall
  • Proper way to use connect-multiparty with express.js?
  • Angular 2 constructor injection vs direct access
  • Trying to get generic when generic is not available
  • Java static initializers and reflection
  • embed rChart in Markdown
  • Android Google Maps API OnLocationChanged only called once
  • How does Linux kernel interrupt the application?
  • How to get Windows thread pool to call class member function?
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • costura.fody for a dll that references another dll
  • Why is Django giving me: 'first_name' is an invalid keyword argument for this function?
  • Observable and ngFor in Angular 2
  • How can I use `wmic` in a Windows PE script?
  • UserPrincipal.Current returns apppool on IIS
  • git trying to push non-existent file … after clearing cache
  • java string with new operator and a literal
  • How to push additional view controllers onto NavigationController but keep the TabBar?