1
\$\begingroup\$

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));
    }
}
\$\endgroup\$
1
\$\begingroup\$
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;
    }
}
\$\endgroup\$
  • \$\begingroup\$ I've never messed with GroupBy, but from looking at examples like this, I can't figure out how it's any better than using a foreach and a switch block to just check each enum and add to a counter as you go. What you're getting at is that by calling five Counts, I'm iterating through the list 5 times, which is bad, right? \$\endgroup\$ – Sinjai Jul 28 '17 at 21:20
  • \$\begingroup\$ @Sinjai I can't figure out how it's any better than using a foreach and a switch block to just check each enum - you have answered your question yourself. You need to write a count per enum value. Should you add a new value to the enum then you need to extend the foreach that counts them. This is a maintenance nightmare. \$\endgroup\$ – t3chb0t Jul 29 '17 at 6:54
  • \$\begingroup\$ Good call! I just wanted to make sure they were functionally the same before adding a bunch of code I don't understand. I could alternatively loop through the enum myself, but I suppose that would be reinventing the wheel. Is executing the queryable down to a list still my best bet? \$\endgroup\$ – Sinjai Jul 29 '17 at 14:56
  • \$\begingroup\$ I really can't figure out how to use GroupBy here. Could you please post an example? \$\endgroup\$ – Sinjai Jul 31 '17 at 17:54
  • \$\begingroup\$ Have mercy... I can't figure out how to use GroupBy in a way different from Count. \$\endgroup\$ – Sinjai Aug 18 '17 at 20:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.