Entity Framework Count with a filter on field really slow - large count

asp.net-mvc c# entity-framework-6


One firm has a lot of members while I am doing a Model Mapping, like 405,000 members.

viewModel.EmployeeCount = company.MembershipUser.Count(x => x.Deleted == false);

The SQL query takes a few milliseconds to execute when I run it. One list view controller hit may take up to ten minutes in ASP.NET MVC using EF6 C#. Thoughts?

MembershipUser is a public virtual virtual (FK) utilizing entity framework 6, not C#6, and Company is my Domain Model Entity.

When I request a company list from my CompanyController (MVC), I just get a list without the number of companies on it. I need to add the count when I do a viewModelMapping on my model to prepare it for passing to the view, but I am without access to the context, database, etc.

            // Redisplay list of companies
            var viewModel = CrmViewModelMapping.CompanyListToCompanyViewModel(pagedCompanyList);

CompanyListToCompanyViewModel converts a list of businesses into a list of my viewmodel and does the membership user count there.

I also tried including the count attribute in the business DomainModel using the following syntax:

public int EmployeeCount
            // return MembershipUser.Where(x => x.Deleted == false).Count();
            return MembershipUser.Count(x => x.Deleted == false);

However, it also takes a long time for businesses with a large workforce.

It nearly seems as if I want to make this my SQL query:

Select *, (SELECT count(EmployeeID) as Count WHERE Employee.CompanyID = CompanyID) as employeeCount from Company

But in the beginning, I naively believed that EF lazy loading and subQueries could handle the workload. But I'm dying from the overhead on huge numbers. I don't really see a difference with tiny datasets, but as the numbers increase, my site becomes unusable.

5/27/2018 12:35:44 PM

Accepted Answer

5/27/2018 1:00:14 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow