When I am running a Model Mapping, one company has a lot of members, like 405,000 members.
viewModel.EmployeeCount = company.MembershipUser.Count(x => x.Deleted == false);
When I run the SQL query, it takes a few milliseconds. In ASP.NET MVC, EF6 C# this can take up to 10 minutes for one list view controller hit. Thoughts?
Company is my Domain Model Entity, and MembershipUser is a public virtual virtual (FK) using entity framework 6, not C#6
When I'm in my CompanyController (MVC) and I ask for a company list, I get a list without the company count included. When I do a viewModelMapping to my Model to prep to pass to the view, I need to add the count, and do not have access to the context or DB, etc.
// Redisplay list of companies
var viewModel = CrmViewModelMapping.CompanyListToCompanyViewModel(pagedCompanyList);
CompanyListToCompanyViewModel maps the list of companies to the list of my ViewModel and does the count (MembershipUsers) there.
I also tried adding the count property to the company DomainModel such as:
public int EmployeeCount
{
get
{
// return MembershipUser.Where(x => x.Deleted == false).Count();
return MembershipUser.Count(x => x.Deleted == false);
}
}
But it also takes a long time on companies with a lot of Employees.
It's almost like I want this to be my SQL Query:
Select *, (SELECT count(EmployeeID) as Count WHERE Employee.CompanyID = CompanyID) as employeeCount from Company
But early on I just assumed I could let EF lazy loading and subQueries do the work. but the overhead on large counts is killing me. On small datasets I see no real difference, but once the counts get large my site is unsusable.
When you are accessing the navigation property and using the count method, you are materializing all the MembershipUser
table and doing the filter in C#.
There are three operations in this command: The C# go to the database and execute the query, transform the query result in C# object list (materialize) and execute the filter (x => x.Deleted == false) in this list.
To solve this problem you can do the filter in the MembershipUser DbSet:
Db.MembershipUser.Count(x => x.Deleted == false && companyId == company.Id);
Doing the query using the DbSet, the filter will be done in database without materialize all 405000 rows.