EntityFramework – Grouping by Date Ranges

If you’ve ever created an outstanding balance report or other report that deals with aggregating data into date ranges, you’ll know that it isn’t immediately obvious how to structure your query, whether using SQL or LINQ (at least, it wasn’t to me).

My initial thought was to run multiple queries (one for each time range) and munge the results together. However, an elegant solution is to use SQL’s CASE expression to group date ranges together.

Let’s say you wanted a report that summed the amount of unpaid invoices in 20 day groupings (0-19 days past due, 20-39 past due, 40+). You could write something like this:

SELECT DaysSinceDueRange, SUM(Amount)
FROM (SELECT CASE WHEN GETDATE() - DueDate < 20 THEN 0
		  WHEN GETDATE() - DueDate BETWEEN 20 AND 39 THEN 20
		  WHEN GETDATE() - DueDate > 39 THEN 40
             END AS DaysSinceDueRange,
             Amount
       FROM Invoices
       WHERE Unpaid=1) inv
GROUP BY DaysSinceDueRange

This is really elegant, but then the question becomes how to do this with an ORM like EntityFramework. There are a couple of tricks required here:

  1. To do the date comparisons, EntityFramework requires the usage of System.Data.Entity.DbFunctions.DiffDays method (in EF 6 – it used to be in System.Data.Objects.EntityFunctions). If you try to do something like (DateTime.Now – invoice.DueDate).TotalDays, you’ll get an exception “DbArithmeticExpression arguments must have a numeric common type” because the subtraction operator is not defined for Dates in SqlServer.
  2. To do CASE / WHEN / THEN / END in EntityFramework, you have to make use of a lot of ternary operators. It can be kind of ugly, but if you write your code well enough, it should be fairly readable (or at least as readable as the SQL expression).

Here is an example of the SQL above translated into LINQ:

Context.Set<Invoice>()
       .Where(inv => inv.Unpaid)
       .Select(inv => new
       {
           DaysSinceDueRange = DbFunctions.DiffDays(inv.DueDate, DateTime.Today) < 20 ? 0 :
                               DbFunctions.DiffDays(inv.DueDate, DateTime.Today) >= 20 && 
                                   DbFunctions.DiffDays(inv.DueDate, DateTime.Today) < 40 ? 20 : 
                               40,
           Amount = inv.Amount
       }).GroupBy(inv => inv.DaysSinceDueRange)
       .Select(g => new
       {
           DaysSinceDueRange = g.Key.DaysSinceDueRange,
           Amount = g.Sum(inv => inv.Amount)
       });

Of course, you can get more complicated in a hurry, but I think this is a pretty elegant way to handle grouping data by date ranges.

Project Fi is the way to go if you are a low cellular data user

After years of being an AT&T mobile customer, dating back to the Cingular days, I finally made the jump to Google’s Project Fi last December. All in all, the service has been very good, and the savings have been ridiculous. AT&T and Verizon have recently rolled out unlimited data plans, so the pricing is a little different than the plan I was on, but it’s not too dissimilar from what I had. Note that I do not recommend project Fi if you’re a really heavy cellular data user (> 6GB total) because they charge $10/GB. If you’re using a lot of data, that will add up fast. However, the problem with most plans is they don’t give you anything if you don’t use that data (maybe you get rollover data, but I’d rather have money). Project Fi pays you back for what you don’t use.

With AT&T, My wife, my sister, and I had a family share plan with 6GB of data. The total for this plan was right around $210/mo, and that was with a corporate discount applied. All of us had new-ish smartphones (my wife and sister had iPhones, myself an android), and were coming up on the end of our 2-year contracts.

During my attendance of That Conference last year, I heard someone tell me about Project Fi and how little it cost. I started looking into it and when my phone (LG G3) turned itself into an unbootable brick one day in September, I decided to buy a Nexus 6P as a replacement. Not only was the phone reasonably priced, but was one of the very limited selection of phones that work on Project Fi. It took a little convincing of my wife to move from her iPhone to Android, but when they announced the Pixel, she agreed to make the move.

Cell Coverage and Quality

A few months in, I can tell you the service, at least where I live in Madison, Wisconsin, has been very good. I haven’t had many instances where I couldn’t get a signal. From the Project Fi FAQ:

Project Fi has partnered with Sprint, T-Mobile, and U.S. Cellular, three of the leading carriers in the US, to provide our service.

They also provide a link to a coverage map: https://fi.google.com/coverage

Project Fi also tries to utilize WI-FI calling when there is low cell quality. I have found this to be a bit of a mixed bag – I sometimes don’t get a dialtone or the phone doesn’t indicate that a call is going out until, suddenly, someone picks up.

The only places where I’ve noticed signal quality problems so far have been inside airports. In particular, it was difficult to get a signal at O’hare. Other people I was travelling with who had Verizon received a better signal.

I have also noticed that sometimes SMS messages won’t come through unless I enable cellular data (and yes, I have verified this even when messages are not MMS). My wife hasn’t had the same problem on her pixel, so mine could be a hardware issue or something specifically related to the Nexus 6P.

Costs

Getting back to the costs, our bill comes in at around $45/month. For two people. Previously, it was costing about $140/month for two people. That’s almost $100/mo, we’re saving. Because Fi reimburses you for unused data, it incentivizes us to use less than the 2GB we pay for. I even have a little widget on my phone that shows how much data I use, and it really encourages me to think about how I’m using data.

Here is a breakdown of the charges from my last bill:

Last month’s usage (for Feb 2 – Mar 2)
Unused data Credit for 1.666 GB at $10/GB -$16.65
Next month’s charges (for Mar 2 – Apr 2)
Fi Basics 2 people, $20 + $15/member $35.00
Prepaid data 2 GB at $10/GB $20.00
Taxes & regulatory fees $6.12

Total: $44.47

International Calling

One of the other great benefits of Project Fi is the international calling aspect of the plan. Data is still $10/GB in 135 countries. From their FAQ:

Project Fi offers high speed data in over 135+ countries and destinations for the same $10/GB you pay in the U.S. For a complete breakdown of specific countries please check our International Rates.

Further:

Unlimited international texts are included in your plan. If you’re using cell coverage, calls cost 20ยข per minute. If you’re calling over Wi-Fi, per-minute costs vary based on which country you’re calling and you’re charged only for outbound calls. Please check our international rates for more information.

I haven’t had a chance to try it out, but I love this part of the plan. When I traveled to Belize last year, we paid $20 for a SIM card and calls there are generally very expensive. Data is incredibly expensive. Had I had Project Fi at the time, those charges would have been very minimal (coverage is another issue altogether, but at least when you have coverage, usage comes at a reasonable rate).

Summary

So, in summary, if you’re a relatively low cellular data user and don’t mind having Google phones, this plan is a great value. I’m looking at saving nearly $1200 this year because of it. I can think of a lot better things to do with my money than spend it on cellular service.