I'm working on an ASP.NET MVC 4 application in which I'm trying to create a dashboard that reads from 2 database views; one view lists buildings with counts and goals, and another lists floors of those buildings with counts and goals. I'm trying to combine them on the dashboard, so I created a datamodel called DashboardView
which holds the buildings with the floors in a Children
property.
It works fine enough locally, but deployed on a production IIS server it runs incredibly slowly, and I'm not sure if it's due to inefficiencies in the way I'm accessing the data:
using (var db = new MyDBContext())
{
var views = (from building in db.BuildingViews.OrderBy(x => x.BuildingName) select new { building })
.AsEnumerable()
.Select(bldgView => new DashboardView
{
ViewType = "Building",
ViewLabel = bldgView.building.BuildingName,
CurrentCount = bldgView.building.Count,
GoalCount = bldgView.building.Goal,
Children = (from floors in db.FloorViews.Where(v => v.BuildingId == bldgView.Building.BuildingId) select new { floor })
.AsEnumerable()
.Select(floorView => new DashboardView
{
ViewType = "Floor",
ViewLabel = floorView.floor.FloorName
CurrentCount = floorView.floor.Count,
GoalCount = floorView.floor.Goal
}).ToList()
}).ToList();
double totalBldgCount = db.BuildingViews.Select(x => x.Count).Sum();
double totalGoalCount = db.BuildingViews.Select(x => x.Goal).Sum();
}
Is there a better way to create the data package I'm trying to achieve here, or is the issue possibly related to multiple people hitting the database at the same time?
::EDIT::
I understand now that using .AsEnumerable()
could be a culprit of poor performance; however I'm struggling to understand how I can properly construct my DashboardView
with child DashboardView
objects without it, as this does not work:
var query = (from buildings in db.BuildingViews
join floors in db.FloorViews on buildings.BuildingId equals floors.BuildingId
select new DashboardSprinklerView
{
ViewType = "Building",
ViewLabel = building.BuildingName,
CurrentCount = building.Count,
GoalCount = building.Goal,
Children = (floor from floors select new DashboardSprinklerView
{
....
}).ToList()
}).ToList();