EntityFramework-Effort: Multiple includes are very slow

Hi, We are using Effort to unit/integration test some EF6 code that usually depends on a SQL Server database. At one point the code runs a statement similar to:

v = context.Stuff
                    .Include("Things")
                    .Include("Things.Children")
                    .Include("Table1.Nav1")
                    .Include("Table1.Nav2")
                    .Include("Table2.Nav1")
                    .Include("Table2.Nav2")
                    .FirstOrDefault(x => x.ThingId == matchObject.ThingId);

When executing against SQL Server with thousands of rows this typically takes around 1 second to execute. Using Effort with only 2 rows in the Stuff collection and nothing in the Included tables it takes about 2 minutes to execute.

This is an issue for us. Is there anything I can do to speed it up?

About this issue

Original URL
State: open
Created 8 years ago
Reactions: 5
Comments: 16 (6 by maintainers)

Most upvoted comments

Hello @cgreening ,

We tried some alternative solution today but so far, none work.

Time is currently missing with all new projects here, so for now, nothing will be done but that’s for sure something we will continue to look. With the project provided by @ElectricAegis , we can easily reproduce it.

JonathanMagnan on Dec 21, 2018

I know it’s almost Christmas, but did you make any progress with this issue?

cgreening on Dec 19, 2018

Hello,

Just to let you known that we are currently investigating it. That one will not be easy.

We started to investigate and find out that the issue is mainly caused because of the Expression Tree provided by Entity Framework is not optimized. Effort only translate the tree in LINQ Expression without optimizing it either

At the end, a lot of LINQ method is called that could be easily skipped.

The simple example provided by @ElectricAegis has a LINQ expression of 4681 lines. We will start to learn about how SQL Server handle it and try to check if we can do something similar for Effort

JonathanMagnan on Sep 28, 2018

See my example here

There is a test which the demonstrates the difference in execution time for a single item in the database:

ElectricAegis on Sep 26, 2018

Hello guys,

My developer tried to reproduce it but every with millions of entities, the performance was very reasonable.

I know it has been a long time since this issue has been created but we are currently working on every issue in this project to close them.

Is there anyone that could help us with a project/sample with this issue?

Best Regards,

Jonathan

JonathanMagnan on Sep 15, 2018

I’d like to confirm this issue. Replacing some of the Include()s with separate loads and then invoking ChangeTracker.DetectChanges() seem to be a doable work-around. Invocation time went from 1.6s to 200ms. However, when run against a SQL Server the orders of magnitude speed improvement between the two implementations was not observed.

As I see it, there are two work-arounds:

Dont use multiple Include()s in any code that is tested using Effort.
Upgrade to Entity Framework Core and use their built-in in-memory database.

Personally, I’d like a better reason to upgrade to Entity Framework Core, but on the other hand Effort shouldn’t need a major rewrite during its obsolescence.

robert-j-engdahl on Mar 31, 2017

I have the same issue regarding includes. Didn’t find any other documentation regarding this problem either and it feels very strange, don’t even know where to begin with the debugging effort. Did anyone manage to find a solution to this yet?

LauriListak on Jan 25, 2017

I am discovering this same issue… I have the following statement that takes about 5 minutes:

        investment = db.Investment
            .Include(e => e.Positions.Select(p => p.Transactions))
            //.Include(e => e.Positions.Select(p => p.InvestmentCarry))
            .Include(e => e.RelatedInvestments)
            .Include(e => e.PrivateInvestment)
            .Include(e => e.CoInvestment)
            .FirstOrDefault(e => e.Id == id)
            ;

Anyone have any ideas?

markjjordan on Jul 20, 2016