Looping DbSet.Local is slow in EF Core compared to EF 6

[HjalteParner]

I have been queering EF 6 DbSet.Local with great success for a number of years. However doing the same in EF Core is a factor 6 slower which unfortunately is a showstopper for me as I'm dealing with a huge amount of data which again prevent me to make the shift from EF 6 to EF Core.

Please help me to solve this issue.

Below please find an example which can be run both in EF 6. and EF Core

private BlogContext _blogContext;

public void BlogTest()
{
    _logger.Information("BlogTest started");

    _blogContext = new BlogContext();
    _blogContext.ChangeTracker.AutoDetectChangesEnabled = false; // EF Core
    //_blogContext.Configuration.AutoDetectChangesEnabled = false; // EF 6

    // Add blogs to context
    for (int i = 0; i < 10000; i++)
    {
        _blogContext.Blogs.Add(new Blog { ID = i });
    }

    _logger.Information("BlogTest continued");

    // Loop blogs in context
    for (int i = 1; i < 100000; i++)
    {
        foreach (var blog in _blogContext.Blogs.Local)
        {
        }
    }

    _logger.Information("BlogTest ended");
}

public class Blog
{
    public int ID { get; set; }
}

public class BlogContext: DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=Blog;Trusted_Connection=True;MultipleActiveResultSets=True;");
    }
}

[ajcvickers]

Note for triage: iteration over the local entities is slow, since it is a view over the state manager.

@HjalteParner A workaround that makes the behavior more like EF6 is to convert the LocalView to an ObservableCollection. For example:

var local = context.Blogs.Local.ToObservableCollection();

foreach (var blog in local)
{
}

This is essentially what EF6 does. It has the downside that the creating the ObservableCollection for a large number of entities can be slow, and the ObservableCollection uses memory. It's probably wise to cache the created collection so that overhead of creating it is not incurred every time.

[HjalteParner]

Thanks you very much @ajcvickers for your reply. Looping the LocalView through the ObservableCollection definitely makes a big difference making EF Core almost on par with the EF6 implementation.

Another thing I was wondering about is that setting the context AutoDetectChangesEnabled = false in my example seems to have no effect in EF Core while in EF 6 not setting this property to false an as such keeping the default true, makes EF 6 terrible slow.

Can you explain this behavior as well?

[ajcvickers]

@HjalteParner See #14001

[HjalteParner]

Thanks @ajcvickers, that explains the current behavior (or lack of behavior you might say) when AutoDetectChangesEnabled is set to true.

[oeramirez]

Hi. I think this is still happening. I upgraded a project from Microsoft.EntityFrameworkCore 2.1.11 to 3.1.5 and got performance issues getting data from Local. Please find attached results of profiling.

[Prinsn]

I don't have quality metrics on this, but that appears to be true for me as well.

Moving from 2.2 to 3.1 and a stress test we have that executes with a warning benchmark of above 24 seconds (and passes in 2.2),

Over 1000 entities being added onto the context, the execution of checking 7 local's with .Any() goes from 67ms to 720ms.

The stress test processes 5000 in just this one spot, so the expected total processing time in 2.2 cannot possibly exceed an average of 4ms each, if my math is correct, so even the baseline execution time is slower by an order of magnitude, potentially two, as that 4ms is for the entirety of code execution.

I also appear to have a 3-50 ms drift elsewhere, that's just DbSet.Local.FirstOrDefault(...) 8 times.

All my other timing metrics are showing a delta between timings of 0 ms from initial to 1000th.

EDIT:

Looks like the problem might be https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/breaking-changes#dbe

Related/more precise/else https://entityframeworkcore.com/knowledge-base/61074160/dbset-tentity--local-any---performance-issue-when-upgraging-ef-core-from-2-2-6-to-3-1-3

[ajcvickers]

Discussion continued on #21564