MVC5/EF/LINQ - Multiple Select Count queries returned to a view, best practice

Question

I am creating my first MVC5 / EF / LINQ application and I would like to display the results of several (about 75) "Select Count(*)" queries in one view. The purpose is to display different statistics from a single table on a home page "dashboard", along with a list of "active" items from the same table in the database.

I have read different opinions on how the best way run select county queries in LINQ... What is the most efficient way to do this considering how many queries I need to run on the page?

A sample query would be

select count(*) from TableName WHERE LType="A" AND (LDate BETWEEN 02/10/2015 AND 02/25/2015)

public ActionResult Index(string sortOrder, string currentFilter, string searchString, int? page)
    {
        ViewBag.CurrentSort = sortOrder;
        ViewBag.LastNameSortParam = String.IsNullOrEmpty(sortOrder) ? "LastName" : "";
        ViewBag.DateSortParam = sortOrder == "Date" ? "LeadDate" : "Date";
        if (searchString != null)
        {
            page = 1;
        }
        else
        {
            searchString = currentFilter;
        }

        ViewBag.CurrentFilter = searchString;

        var newLeads = from l in db.WebLeads
                       where (l.LoanAgent == null || l.LoanAgent == "hb@mwfinc.com")
                       select l;                           
        if (!String.IsNullOrEmpty(searchString))
        {
            newLeads = newLeads.Where(l => l.LastName.Contains(searchString)
                                   || l.FirstName.Contains(searchString));
        }
        switch (sortOrder)
        {
            case "Date":
                newLeads = newLeads.OrderBy(l => l.LeadDate);
                break;
            case "LeadDate":
                newLeads = newLeads.OrderByDescending(l => l.LeadDate);
                break;
            case "LastName":
                newLeads = newLeads.OrderBy(l => l.LastName);
                break;
            default:
                newLeads = newLeads.OrderByDescending(l => l.LeadDate);
                break;
        }

        int pageSize = 15;
        int pageNumber = (page ?? 1);

        DateTime minDate = System.DateTime.Today;
        DateTime maxDate = System.DateTime.Now;

        // Leads to Be Assigned Daily Count
        var ctPurchTBA = db.WebLeads.Count(x => (x.LoanType == "Home Purchase" || x.LoanType == "CalPATH Home Purchase")
                              && x.LeadDate >= minDate
                              && x.LeadDate <= maxDate);


        return View(newLeads.ToPagedList(pageNumber, pageSize));                        
    }

'

Answer 1

As mentioned by Osadella in the comments, you can use the POCO generated by Entity Framework to do this in a much more sane way.

You can do this in SQL, as you're already aware. The problem with using count in SQL is that it is not very performant. You're not gonna notice any huge difference with a dataset like the one you've mentioned, but it's worth knowing that large enterprise applications suffer when using SQL count.

To answer your question however, you should probably use LINQ on the POCO (big data model generated by your DbContext in EF) for the simple reason that you can use it. LINQ is a much simpler abstraction over SQL, there are some fairly good examples here about the benefits of LINQ.

You can use SQL based LINQ or you can use method based LINQ, I'd use method based because it's a lot more readable and easier to write.

The code above;

select count(*) 
from TableName 
WHERE LType="A" 
AND (LDate BETWEEN 02/10/2015 AND 02/25/2015)

would become:

var count = db.TableNames.Count(x => x.LType == "A" 
                                  && x.LDate >= minDate 
                                  && x.LDate <= maxDate);

Obviously you'd need to define minDate and maxDate somewhere as well.