CODEWITHSUNDEEP
c#sqlperformanceentity-framework
KingKerosin
KingKerosin

Reputation: 3841

Entity Framework performance of include

It's more a technical (behind the scenes of EF) kind of question for a better understanding of Include for my own.

Does it make the query faster to Include another table when using a Select statement at the end?

ctx.tableOne.Include("tableTwo").Where(t1 => t1.Value1 == "SomeValueFor").Select(res => new {
    res.Value1,
    res.tableTwo.Value1,
    res.tableTwo.Value2,
    res.tableTwo.Value3,
    res.tableTwo.Value4
});

Does it may depend on the number of values included from another table?

In the example above, 4 out of 5 values are from the included table. I wonder if it does have any performance-impact. Even a good or a bad one?

So, my question is: what is EF doing behind the scenes and is there any preferred way to use Include when knowing all the values I will select before?

Upvotes: 0

Views: 1603

Answers (3)

Massimo Zerbini
Massimo Zerbini

Reputation: 3191

I agree with @Karamfilov for his general discussion, but in your example your query could not be the most performant. The performance can be affected by many factors, such as indexes present on the table, but you must always help EF in the generation of SQL. The Include method can produce a SQL that includes all columns of the table, you should always check what is the generated SQL and verify if you can obtain a better one using a Join.

This article explains the techniques that can be used and what impact they have on performance: https://msdn.microsoft.com/it-it/library/bb896272(v=vs.110).aspx

Upvotes: 0

Learning Curve
Learning Curve

Reputation: 1569

It is about how you want EF to load your data. If you want A 'Table' data to be pre populated than use Include. It is more handy if Include statement table is going to be used more frequently and it will be little slower as EF has to load all the relevant date before hand. Read the difference between Lazy and Eager loading. by using Include, it will be the eager loading where data will be pre populated while on the other hand EF will send a call to the secondary table when projection takes place i-e Lazy loading.

Upvotes: 0

Vladislav Karamfilov
Vladislav Karamfilov

Reputation: 471

In your case it doesn't matter if you use Include(<relation-property-name>) or not because you don't materialize the values before the Select(<mapping-expression>). If you use the SQL Server Profiler (or other profiler) you can see that EF generates two exactly the same queries.

The reason for this is because the data is not materialized in memory before the Select - you are working on IQueryable which means EF will generate an SQL query at the end (before calling First(), Single(), FirstOrDefault(), SingleOrDefault(), ToList() or use the collection in a foreach statement). If you use ToList() before the Select() it will materialize the entities from the database into your memory where Include() will come in hand not to make N+1 queries when accessing nested properties to other tables.

Upvotes: 1

Related Questions