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));
}
}