MVC - Count in a foreach loop, best practice

Question

I have been working on getting a count records within a foreach loop. I am going to need run many of these counts on a single page. I am looking for the most efficient way to do this.

I have gotten this far, but I am not sure if I headed down the right path. If I am, how do I get this data into my view.

ViewModel

public class AgtLeadStatsListVM
{
    public string LoanAgent { get; set; }
    public DateTime LeadDate { get; set; }
    public int LeadDailyCt { get; set; }
    public int LeadWeeklyCt { get; set; }
    public int LeadMTDCt { get; set; }
    public int LeadYTDCt { get; set; }
    public IEnumerable<MWFUser> AgentList { get; set; }
    public virtual WebLead Lead { get; set; }
}

Controller

 var model = new AgtLeadStatsListVM();            
 {
 // Get Selected Agent's Information
 var AgentList = from l in db.MWFUsers
                 where (l.UserTitle == "Banker"
                 select l;

    foreach (var agent in AgentList)
    {

    // Daily Lead Count
    var LeadDailyCt = db.WebLeads.Count(x => (x.LoanAgent == agent.UserEmail)
    && (x.LeadDate >= todayDate && x.LeadDate <= todayEndDay));

    // Weekly Lead Count
    var LeadWeeklyCt = db.WebLeads.Count(x => (x.LoanAgent == agent.UserEmail)
                                         && x.LeadDate >= firstOfWeek
                                         && x.LeadDate <= todayEndDay);

    // Monthly Lead Count
    var LeadMTDCount = db.WebLeads.Count(x => (x.LoanAgent == agent.UserEmail)
                                         && x.LeadDate >= firstOfMonth
                                         && x.LeadDate <= todayEndDay);

   // YTD Lead Count
   var LeadYTDCount = db.WebLeads.Count(x => (x.LoanAgent == agent.UserEmail)
                                         && x.LeadDate >= firstOfMonth
                                         && x.LeadDate <= todayEndDay);
}
}

View

@model LoanModule.ViewModels.AgtLeadStatsListVM

<div>
    @foreach (var item in Model.AgentList)
    {
    <p>@Model.LoanAgent</p>
    <p>@Model.LeadDailyCt</p>
    <p>@Model.LeadWeeklyCt</p>
    <p>@Model.LeadMTDCt</p>
    <p>@Model.LeadYTDCt</p>
    }

I am receiving this error on my View: Object reference not set to an instance of an object. (on line: @foreach (var item in Model.AgentList))

What am I missing?

Thank you.

Answer 1

The semicolon at the end of var model = new AgtLeadStatsListVM(); means that you are no longer in an object initializer after that line. The syntax you're probably trying for is something more along these lines:

var agents = 
    from l in db.MWFUsers
    where l.UserTitle == "Banker"
    select l;

var model = new AgtLeadStatsListVM
{
    // Get Selected Agent's Information
    AgentList = agents.ToList(),
    // Daily Lead Count
    LeadDailyCt = agents.Sum(a => db.WebLeads.Count(
        x => (x.LoanAgent == a.UserEmail)
            && (x.LeadDate >= todayDate && x.LeadDate <= todayEndDay)))
    // ...
}

By the way, if you want to get all of this information in a single round-trip, you could use this group by-based trick.

var model = 
    (from agent in agents
    let webLeads = db.WebLeads.Where(x => x.LoanAgent == agent.UserEmail)
    group new{agent, webLeads} by 0 into g
    select new AgtLeadStatsListVM
    {
        // Get Selected Agent's Information
        AgentList = g.Select(e => e.agent).ToList(),
        // Daily Lead Count
        LeadDailyCt = g.Sum(e => e.webLeads.Count(x => x.LeadDate >= todayDate && x.LeadDate <= todayEndDay)),
        // ...
    }).FirstOrDefault();

Update

From your comments it sounds like this is more what you're going for:

var model = 
    (from agent in agents
    let webLeads = db.WebLeads.Where(x => x.LoanAgent == agent.UserEmail)
    select new AgtLeadStatsListVM
    {
        // Get Selected Agent's Information
        LoanAgent = agent.UserEmail,
        // Daily Lead Count
        LeadDailyCt = webLeads.Count(x => x.LeadDate >= todayDate && x.LeadDate <= todayEndDay),
        // ...
    }).ToList();

And your view code:

@model IEnumerable<LoanModule.ViewModels.AgtLeadStatsListVM>

<div>
    @foreach (var item in Model)
    {
      <p>@item.LoanAgent</p>
      <p>@item.LeadDailyCt</p>
      <p>@item.LeadWeeklyCt</p>
      <p>@item.LeadMTDCt</p>
      <p>@item.LeadYTDCt</p>
    }

The AgentList property should be removed from your model entirely.

Answer 2

I am receiving this error on my View: Object reference not set to an instance of an object. (on line: @foreach (var item in Model.AgentList))

The AgentList is null.

Furthermore, you haven't initialized correctly your model.

Specifically, this line of code

var model = new AgtLeadStatsListVM();

creates a new object of type AgtLeadStatsListVM, where

• LoanAgent is null
• LeadDate 1/1/0001 12:00:00 AM
• LeadDailyCt is 0
• LeadWeeklyCt is 0
• LeadMTDCt is 0
• LeadYTDCt is 0
• AgentList is null
• WebLead is Lead

The default values, since you didn't set any value. Probably, you want to make use of an object initializer, there you don't need ();. We write just this:

var model = new AgtLeadStatsListVM
{
    LoadAgent = "Name of the LoadAgent",
    LeadDate = DateTime.Now.Utc,
    LeadDailyCt = agents.Sum(a => db.WebLeads.Count(
    x => (x.LoanAgent == a.UserEmail)
        && (x.LeadDate >= todayDate && x.LeadDate <= todayEndDay)))
    // Do the same for the rest of the corresponding properties.
}

Answer 3

I am going to ignore the error that you are getting (see other answers for it) and reference only best practice and a most efficient way for counting part of the question.

The most efficient way (at least in my opinion) would be using some caching technique for the result and updating the cache on daily basis(since the maximum resolution that you use is daily). Clearly, choosing an appropriate caching mechanism depends on your application. It can go from storing some data in static variable on application start, to running a dedicated Redis server (or any other fast data structure store). The bottom line here is: try to minimize the number of queries to DB and cache any suitable data.