Counting matching rows in IQueryable

Question

I'm designing a basic ticketing system using ASP.NET MVC with Entity Framework.

I'm passing a DbSet<TicketModel> into a method as an IQueryable<TicketModel> that adds to a category counter based on a couple columns.

Tickets have four different states and an age. I need to display counters for the total as well as each of these states and counters for those between 60 and 90 days old and those above 90 days old.

As I understand it, operating on IQueryable is more efficient as it actually changes the query sent to the database. Operating on an actual List pulls the entire table then works on it. However, LINQ-to-Entities only works with columns in the database. How can I get around this?

Model:

public class TicketModel
{
    public int Id { get; set; }
    public StatusType Status { get; set; }
    public enum StatusType { Open, Resolved, Responded, Archived };
    public DateTime DateOfCreation { get; set; }
    public bool Is60To90DaysOld
    {
        get
        {
            TimeSpan age = DateTime.Today - DateOfCreation;
            return (age.Days >= 60 && age.Days < 90);
        }
    }
    public bool Is90DaysOld
    {
        get
        {
            TimeSpan age = DateTime.Today - DateOfCreation;
            return (age.Days >= 90);
        }
    }
}

ViewModel:

// Avoid using fully-qualified name for StatusType
using static MyProject.Models.TicketModel;
public class TicketCountersViewModel
{
    // Counters for each type of ticket
    public struct TicketCounts
    {
        public int Total { get; set; }
        public int Open { get; set; }
        public int Responded { get; set; }
        public int Resolved { get; set; }
        public int Archived { get; set; }
        public int Age60To90 { get; set; }
        public int Age90Plus { get; set; }
    }

    public TicketCounts Counters;

    public TicketCountersViewModel(IQueryable<TicketModel> ticketList)
    {
        Counters.Total = ticketList.Count();
        Counters.Open = ticketList.Count(e => e.Status == StatusType.Open);
        Counters.Responded = ticketList.Count(e => e.Status == StatusType.Responded);
        Counters.Resolved = ticketList.Count(e => e.Status == StatusType.Resolved);
        Counters.Archived = ticketList.Count(e => e.Status == StatusType.Archived);
        // Can't use LINQ-to-Entities here -- Is60To90DaysOld and Is90DaysOld aren't columns in database
        List<TicketModel> list = ticketList.ToList();
        Counters.Age60To90 = list.Count(e => e.Is60To90DaysOld);
        Counters.Age90Plus = list.Count(e => e.Is90DaysOld);
    }
}

Controller:

public class TicketsController
{
    private MyDbContext db = new MyDbContext();
    public ActionResult Index()
    {
        return View(new TicketCountersViewModel(db.Tickets));
    }
}

Answer 1

Counters.Total = ticketList.Count();
Counters.Open = ticketList.Count(e => e.Status == StatusType.Open);
Counters.Responded = ticketList.Count(e => e.Status == StatusType.Responded);
Counters.Resolved = ticketList.Count(e => e.Status == StatusType.Resolved);
Counters.Archived = ticketList.Count(e => e.Status == StatusType.Archived);

You have so many counters there that you should really consider using a GroupBy on the Status column.

---

// Can't use LINQ-to-SQL here -- Is60To90DaysOld and Is90DaysOld aren't columns in database
List<TicketModel> list = ticketList.ToList();
Counters.Age60To90 = list.Count(e => e.Is60To90DaysOld);
Counters.Age90Plus = list.Count(e => e.Is90DaysOld);

This part could also benefit from another GroupBy if you added one more property Age and a helper enum:

public enum Age
{ 
    Unknown,
    Between60And90Days,
    GreaterOrEqual90Days
}

The new property could replace the other two as they are mutually exclusive anyway:

public Age Age
{
    get 
    {
        TimeSpan age = DateTime.Today - DateOfCreation;

        if (age.Days >= 60 && age.Days < 90) return Age.Between60And90Days;
        if (age.Days >= 90) return Age.GreaterOrEqual90Days;

        return Age.Unknown;
    }
}