Optimize EF Core query with Include()

entity-framework entity-framework-core optimization

Question

I have following query within my project and it is consuming lot of time to execute. I am trying to optimize it, but not able to successfully do it. Any suggestions would be highly appreciated.

_context.MainTable
.Include(mt => mt.ChildTable1)
.Include(mt => mt.ChildTable1.ChildTable2)
.Include(mt => mt.ChildTable3)
.Include(mt => mt.ChildTable3.ChildTable4)
.SingleOrDefault(
        mt =>
        mt.ChildTable3.ChildTable4.Id == id 
        &&
        mt.ChildTable1.Operation == operation
        && 
        mt.ChildTable1.Method = method
        && 
        mt.StatusId == statusId);
1
1
9/7/2018 11:00:15 AM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Popular Answer

Include() gets translates to join and you are using too many joins in the code. You can optimize indexes with the help of DB engine execution plan.

I suggest you not to use all Include in one go. instead, you break the query and apply Include one by one. I meant you apply Include, get the result and then apply theIncludeagain and so..By having more than twoInclude` affect the performance.

1
9/7/2018 12:52:21 PM

Related Questions

EF Core 1.0 - Include() generates more than one queries

I am using EF 7.0.0-rc1-final....The following statement generates multiple queries on the server. Is this normal or I am missing something ?...Group myGroup = dbContext_ .Set<Group>() .Include(x => x.GroupRoles) .ThenInclude(x => x.Role) .FirstOrDefault(x => x.Name == "Approver"); ...I see two separa...
entity-framework-core
asked by regnauld

How to include related entities in an Entity Framework Core 1.0.1 raw query?

Setup:...ASP .NET Core 1 Web API...MySQL Server Community Edition 5.7...Entity Framework Core 1.0.1 with Pomelo MySQL driver (3 separate contexts, 3 separate databases)....Contexts: MainContext (maindb), Module1Context (module1db), Module2Context (module2db)....I want to execute a query that returns a list of Posts (from Module1Context), but I need...
asp.net-core dbcontext entity-framework entity-framework-core mysql
asked by Alin Florin

EF Core 2.0 include nested entities with dynamic query

First of all You are using ...GetOrAddSet... of ...IDbSetCache...public interface IDbSetCache ...// // Summary: // This API supports the Entity Framework Core infrastructure and is not intended // to be used directly from your code. This API may change or be removed in future...Include... is method of ...IQueryable<TEntity...
.net-core c# entity-framework-core
asked by programtreasures

EF Core query from Include but no need to return in result

Okay, so this is probably from not knowing how to use EF core correctly as this is my 2nd day using but I appear to have to run ....Include()... in order to query against the "inner join" this method creates. ...I've got this bit working and filtering down my results, but I don't want to return the include in the model as it's making a 256kb JSON r...
c# entity-framework entity-framework-core sql
asked by The Angry Saxon

EF How to query more entities with .include() and using repository pattern

I got the following sql statement that I want to implement with entity framework with linq (lambda expression). Here is the SQL:...select * from tbl_ExampleStoneCatalog join tbl_ExampleStoneCategory on tbl_ExampleStoneCatalog.fk_ESC = tbl_ExampleStoneCategory.pk_ESC join tbl_ExampleStones on tbl_ExampleStoneCatalog.fk_ES = tbl_ExampleStones.pk_E...
c# entity-framework entity-framework-6 linq repository-pattern
asked by Marc Ottenbacher

.Net Core 2.1 EF Core Null Reference Exception when searching database with Where linq query

I have a pretty simple query here:...public async Task<IActionResult> Index(string sortOrder) { ApplicationUser user = await _userManager.GetUserAsync(User); var context = _context.Engine .Include(e => e.EngineType) .Where(e => e.Company == user.Company); return View(await context.ToListAsync());...
.net-core entity-framework-core
asked by Ian Gleeson

Parallel EF Core queries with DbContext injection in ASP.NET Core

I have writing an ASP.NET Core web application that needs all the data from some tables of my database to later organize it into readable format for some analysis....My problem is that this data is potentially massive, and so in order to increase performance i decided to get this data in parallel and not one table at a time....My issue is that i do...
asp.net-core c# dependency-injection entity-framework-core
asked by Tomer Something

How to translate this SQL query to a LINQ query in EF Core?

It's literally one to one translation....SQL query...SELECT A, B, C, D , TimeInsertedLocal FROM Indicators WHERE TimeInsertedLocal >= ( SELECT MAX(I.TimeInsertedLocal) FROM Indicators AS I ) ...EF Core LINQ query:...var indicators = dbContext.Set<Indicator>(); var query = indicators .Where(i => i.TimeInsertedLocal >= indicators.Max...
c# entity-framework-core linq sql
asked by Ivan Stoev

How to improve performance of an EF Core query which uses several Includes

I've got this query, which I'll simplify for brevity:...public IQueryable<User> GetByIdAsync(Guid userId) { return MyContext .Users //Bunch of Includes //Most of which have a ThenInclude //Followed by another ThenInclude .FirstOrDefaultAsync(u => u.Id == userId) } ...When run for around 100 us...
database-performance entity-framework-core performance psql
asked by AjLearning

EF Core 2.1 Linq Query to EF Core 3.0

I am dealing with a query that i cannot get to work on ef core 3.0 and it worked fine in the 2.1 version, i would like if anyone can help figure how i can get it work in the 3.0 version....So let me start with the challenge. ...i have a table that is named Zone and it has a one to many relationship to a table called Bin, and bin has a one to many r...
c# ef-core-3.0 entity-framework entity-framework-core linq
asked by Sol Stein

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow