Entity Framework Code First - Reducing round trips with .Load() and .Local

Question

I'm setting up a new application using Entity Framework Code Fist and I'm looking at ways to try to reduce the number of round trips to the SQL Server as much as possible.

When I first read about the .Local property here I got excited about the possibility of bringing down entire object graphs early in my processing pipeline and then using .Local later without ever having to worry about incurring the cost of extra round trips.

Now that I'm playing around with it I'm wondering if there is any way to take down all the data I need for a single request in one round trip. If for example I have a web page that has a few lists on it, news and events and discussions. Is there a way that I can take down the records of their 3 unrelated source tables into the DbContext in one single round trip? Do you all out there on the interweb think it's perfectly fine when a single page makes 20 round trips to the db server? I suppose with a proper caching mechanism in place this issue could be mitigated against.

I did run across a couple of cracks at returning multiple results from EF queries in one round trip but I'm not sure the complexity and maturity of these kinds of solutions is worth the payoff.

In general in terms of composing datasets to be passed to MVC controllers do you think that it's best to simply make a separate query for each set of records you need and then worry about much of the performance later in the caching layer using either the EF Caching Provider or asp.net caching?

Please also take a look at this answer: https://stackoverflow.com/a/49687720/538387 — Tohid, Apr 06 '18 at 07:39

Ladislav Mrnka · Accepted Answer · 2011-03-31T07:48:10.983

3

It is completely ok to make several DB calls if you need them. If you are affraid of multiple roundtrips you can either write stored procedure and return multiple result sets (doesn't work with default EF features) or execute your queries asynchronously (run multiple disjunct queries in the same time). Loading unrealted data with single linq query is not possible.

Just one more notice. If you decide to use asynchronous approach make sure that you use separate context instance in each asynchronous execution. Asynchronous execution uses separate thread and context is not thread safe.

edited Mar 31 '11 at 07:48

answered Mar 30 '11 at 16:21

Ladislav Mrnka

360,892
59
660
670

`Loading unrealted data with single linq query is not possible.` Is it still not possible in 2014 with EF 6? – Andrew Savinykh Mar 18 '14 at 01:52
@zespri: There is support for mapping stored procedures with multiple result sets since EF 5. – Ladislav Mrnka Mar 18 '14 at 10:05

score 0 · Answer 2 · answered Mar 30 '11 at 16:03

0

I think you are doing a lot of work for little gain if you don't already have a performance problem. Yes, pay attention to what you are doing and don't make unnecessary calls. The actual connection and across the wire overhead for each query is usually really low so don't worry about it.

Remember "Premature optimization is the root of all evil".

answered Mar 30 '11 at 16:03

John Farrell

24,673
10
77
110

True that. I'm in pre production now and want to get a handle on how these systems will be architected, seems like caching is the way to go for db call optimization. – BrooklynDev Mar 30 '11 at 21:11
"... in say 97% of the time." – AviD Oct 10 '11 at 13:50

score 0 · Answer 3 · answered Aug 16 '11 at 00:42

My rule of thumb is that executing a call for each collection of objects you want to retrieve is ok. Executing a call for each row you want to retrieve is bad. If your web page requires 20 collections then 20 calls is ok.

That being said, reducing this to one call would not be difficult if you use the Translate method. Code something like this would work

var reader = GetADataReader(sql);
var firstCollection = context.Translate<whatever1>(reader);
reader.NextResult();
var secondCollection = context.Translate<whateve2r>(reader);
etc

The big down side to doing this is that if you place your sql into a stored proc then your stored procs become very specific to your web pages instead of being more general purpose. This isn't the end of the world as long as you have good access to your database. Otherwise you could just define your sql in code.

Entity Framework Code First - Reducing round trips with .Load() and .Local

3 Answers3