Does Entity Framework run database query again for already run query?

Question

I am developing an Asp.Net mvc project using Entity Framework code first approach. But I am trying to improve performance of my application. I am not clear that Entity Framework run database query again or not if I access the same condition again.

Here is my code:

var item = context.Items.FirstOrDefault();// Database query will be run for this
var start = item.Promotions.FirstOrDefault().Start; // Another query will be run for this
var end = item.Promotions.FirstOrDefault().End; //Will another query be run again? Same query is run again for this
var price = item.Promotions.FirstOrDefault().Price; //Here also

As you can see in the code, new query will be run to retrieve promotion start date. But when I retrieve end date, database will run the query again even if they are same query. I am confused by it.

cpoDesign · Accepted Answer · 2016-07-11 08:03:57Z

3

While the answer is correct there is another option to consider

Your original query

var item = context.Items.FirstOrDefault();// Database query will be run for this
var start = item.Promotions.FirstOrDefault().Start; // Another query will be run for this
var end = item.Promotions.FirstOrDefault().End; //Will another query be run again? Same query is run again for this
var price = item.Promotions.FirstOrDefault().Price; //Here also

Loading item as object stops reading data from the object

var item = context.Items.FirstOrDefault();// Database query will be run for

Now all objects loaded are held in memory to read from without producing a query

item.Description 
item.Items //etc

Now to get data when you are lazy loading is var promotion = item.Promotions.FirstOrDefault() And this will produce another query

promotion.Start
promotion.End

The question is if you read promotion everytime, it would be better to include it with the query (using eager loading) so when the the object is being fetched, it loads the child object

var item = context.Items.Include(x=>x.Promotions).FirstOrDefault();

and then it will be only one query to db to get data about the item and the promotion.

Specific selection

You can also update the query to select only the items you are interested in by using :

var customObj = context.Promotions.Where(p=>p.ItemId==itemId).Select(x=>new{x.Start, x.End, x.Price}).FirstOrDefault();

And this way you will receive the specific select and it will generate specific query for you.

Note use sql profiler / ants profiler etc to see what sql you generate.

edited Jul 11, 2016 at 8:03

answered Jul 8, 2016 at 13:24

cpoDesign

9,11314 gold badges67 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Wai Yan Hein Over a year ago

So I retrieve like this context.Items.Include(x=>x.Promotions). Then I get an item like this var item = items.FirstOrDefault(). If U access item.Promotions.FirstOrDefault(), query will be run again?

cpoDesign Over a year ago

no it will include by default all instances associated against the item

Wai Yan Hein Over a year ago

What if one to one relationship? Example var item = context.items.FirstOrDefault(); var start = item.Promotion.Start; var end = item.Promotion.End . As you can see, I did not cached them assign to a variable. So another query run again when I retrieve promotion end date?

cpoDesign Over a year ago

In that case you should select only the specific fields instead of the whole object

Wai Yan Hein Over a year ago

So you mean new query will be run each time I access a property of promotion?

|

Daniel Hilgarth · Accepted Answer · 2016-07-08 13:16:31Z

1

The data in the database could have changed in the meantime, that's why EF is running the query again.

To improve the performance, simply cache the result yourself:

var item = context.Items.FirstOrDefault(); // Database query will be run for this
var promotion = item.Promotions.FirstOrDefault(); // Another query will be run for this
var start = promotion.Start; // No additional query
var end = promotion.End;     // No additional query
var price = promotion.Price; // No additional query

BTW: This has nothing to do with Lazy Loading. Lazy Loading is related to lazily loading other entities that it references. It is not related to its data.

edited Jul 8, 2016 at 13:16

answered Jul 8, 2016 at 13:14

Daniel Hilgarth

175k41 gold badges346 silver badges450 bronze badges

1 Comment

Wai Yan Hein Over a year ago

What if one to one relationship? Example var item = context.items.FirstOrDefault(); var start = item.Promotion.Start; var end = item.Promotion.End . As you can see, I did not cached them assign to a variable. So another query run again when I retrieve promotion end date?

Collectives™ on Stack Overflow

Does Entity Framework run database query again for already run query?

2 Answers 2

8 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related