Perfomance issue - .Count in MVC view

Question

For several pages I use a .Count inside a foreach loop

@model ICollection<Item>
foreach(var item in Model)
{
    @item.Flight.FlightReservations.count
}

Because of lazy loading the EF makes a round trip to the database for this.

Now I want to fix this by using this or linq version: Include("List.Flight.FlightReservations")

Doing this makes loading my dbSet take even longer than those foreach round trips

How can I 'load' parts of only 1 object?

I would like to use context.Items.Single(p => p.id == id).Include(.....) So I only load 1 item fully.

Or any other solutions for this? (A way to force load item.List.item2.List inside controller)

Any suggestions are welcome :) Thanks

EDIT : now using

Where(..).ToDictionary(item => item, item => item.Flight.FlightReservations.Count); Also noticed adding an index to my 'clustered index' table helped a little. Still slow though

var f = _pr.FindBy(duifid);
var result = (from p in f.IngeschrevenVluchten
              select new PrestatieModel
              {
                 Eindpos = p.Eindpositie, 
                 Locatie = p.Vlucht.Locatie.Naam, 
                 AantalInschrijvingen = p.Vlucht.Inschrijvingen.Count(), 
                 Vlucht = p.Vlucht
              });

This query executes very fast, making a IEnumerable<Model>. But still it loads very slow once sent to the view.

return PartialView(result);

Answer 1

Here is some tips to speed of EF:

1- Use Pre-Generated Mapping Views. Before the Entity Framework can execute a query or save changes to the data source, it must generate a set of mapping views to access the database. Because mapping view generation is a significant part of the overall cost of executing the first query, the Entity Framework enables you to pre-generate mapping views and include them in the compiled project.

2- Do not create new instance of DbContext every where instead use Ioc for creating and maintaining DbContext instance.

3- If you only want display data then turn of entity tracking.This may result in better performance when querying for large numbers of entities in read-only scenarios.

var someData = context.SomeEntity.Where(...).AsNoTracking().ToList(); 

4- Reduce round trip to database when getting data from database by using Include and ToList() method.

context.Items.Single(p => p.id == id).Include(t => t.List1.Select(l => l.List2)).ToList();

5- If you have huge amount of data use Paging.

var list = context.Items.AsNoTracking().Select(x => x.Content).Skip(15).Take(15).ToList();