Entity Framework - lazy loading or additional async/await query method?

Question

I have these Domain Models

public class Topic
{
    public int TopicId { get; set; }

    public virtual ICollection<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }

    public int? TopicId { get; set; }
    public virtual Topic Topic { get; set; }
}

For example I would like to impliment method TestAsync, there I want to work with Topic object and related Posts objects.

Topic model I can get using async method and topicId as param.

public async Task<bool> TestAsync(int topicId)
{
    var topic = await topicService.GetByIdAsync(topicId);

    // posts ...
}

And I have two ways, how to get related posts. But, what the difference if I will use LazyLoading or just another async query?

// Example: 1 (LazyLoading)
var posts = topic.Posts;

// OR Example: 2 (Async method)
var posts = await postService.GetAllByTopicIdAsync(topicId);

So, I think that Example:1 will works synchronously, and also that I lose all the advantages of async/await code. But Example: 2 makes me think, that may be I dont know all charms of Lazy Loading:) Could anyone clarify what solution I should use and why? Thanks:)

The "advantages" of async/await code for database access in ASP.NET are frankly minimal. — David Browne - Microsoft, Jul 31 '17 at 16:49
It is not a choice between async/await or lazy loading imho. It is a choice between firing two queries to the database (using lazy loading) or having a join in sql (eager loading using Include). — Peter Bons, Jul 31 '17 at 16:52
David, but in many articles async/await is recommended to use it for working with methods which work with database, because at this time thread returns to the thread pool. Peter, Eager Loading can be a solution, thanks! — Vladimirs Gorkins, Jul 31 '17 at 17:06
The original thread may return to the thread pool, but I would expect another one to be draw to serve the DB request, which ends up nullifying most benefits. Not for web sites, but for desktop, where you have a single "UI thread", delegating work to the thread pool is much better. — Alejandro, Jul 31 '17 at 19:31
@DavidBrowne-Microsoft do you have some reading material expanding on that statement? Like why is is minimal? Is it so minimal that it's not even worth it? Where is it not minimal then? — romeozor, Aug 01 '17 at 13:00
@romeozor In a web app/service/api the only benefit of async methods is that they reduce the number of threads required to process concurrent requests. If your database backend can support thousands of concurrent requests from each web server, then saving on web server threads may improve performance. But in the normal case where your maximum request throughput is achieved with 10s or 100s of concurrent requests per web server, the benefits are minimal. — David Browne - Microsoft, Aug 01 '17 at 13:17
@DavidBrowne-Microsoft but lots of MS examples use a web app with SQL Server backing it (which I assume can handle the requests). So is it just MS "Pavloving" devs to use async/await in their web apps so when eventually they switch to Azure, they hog less cloud resources? — romeozor, Aug 01 '17 at 13:31
@romeozor Moving to an async model is generally a good thing. It's just that you shouldn't worry to much if you need to block a thread to run a query. — David Browne - Microsoft, Aug 01 '17 at 13:32
You should worry. Not a lot, but if you're interested, at all, in maximizing performance and throughput, then you should use it. You'll never achieve highest possible throughput without the async-await model, because any way you look at it, without async-await, you're blocking a thread. If you're blocking a thread for 500ms or 1000ms waiting on a db query, another thread will eventually need spawned for other work, which takes time and memory. That delay has opportunity costs, like not serving another request and starting composition of another query as early as possible. — Triynko, Mar 15 '18 at 14:30

score 14 · Accepted Answer · answered Jul 31 '17 at 16:57

14

Lazy loading is always synchronous, which is unfortunate. EF Core, for example, with its async-first mentality, does not (yet) support lazy loading.

Other options are to either do a join (eager loading) as Peter suggested, which asynchronously performs a single query; or to do an explicit second asynchronous query. Which one you'd choose comes down to how your model is normally used.

Personally, I would choose to do the eager loading if the models are always used together, and do multiple asynchronous queries otherwise. I do not use lazy loading myself, though nothing would prevent it from working.

answered Jul 31 '17 at 16:57

Stephen Cleary

437,863
77
675
810

Thanks for the extensive advice. It helped me. Thanks Peter too:) – Vladimirs Gorkins Jul 31 '17 at 17:11
Lazy loading could cause a problem if it uses a disposable resource, since the method will probably exit its `using` clause before the lazy load fires. – John Wu Mar 13 '18 at 02:05
Lazy loading and navigation properties in general are terrible. Even with eager loading, if you're loading more than one or two, it bloats the query beyond recognition and ends up returning duplicate data across rows because it has to return a single grid of data as opposed to multiple active result sets. – Triynko Mar 15 '18 at 14:51

score 2 · Answer 2 · answered Mar 16 '20 at 12:50

        public async Task<TProperty> GetReferenceAsync<TEntity, TProperty>(DBContext context, TEntity entity, Expression<Func<TEntity, TProperty>> property)
        where TEntity : class, IIdentifiable where TProperty : class
        {
            var getProperty = property.Compile();
            var value = getProperty(entity);
            if (value != null) { return value; }

            await context.Entry(entity).Reference(property).LoadAsync();
            return getProperty(entity);
        }

Usage: var posts = await GetReferenceAsync(context, topic, e => e.Posts);

score -1 · Answer 3 · answered Jul 31 '17 at 16:53

-1

You should do neither. I am assuming your GetByIdAsync() is implemented like this.

public async Task<Topic> GetByIdAsync(int id)
{
        return await context.Topics.FirstAsync(t=>t.Id == id);
}

You should change it to be

public async Task<Topic> GetByIdAsync(int id)
{
        return await context.Topics.Include(t=>t.Posts).FirstAsync(t=>t.Id == id);
}

answered Jul 31 '17 at 16:53

Tanveer Badar

5,438
2
27
32

You can't recommend this as you don't know the internals, maybe Topics contains thousands of records and the Posts are rarely used, then this solution is the most ineffective one. – Gusman Jul 31 '17 at 16:56
Or perhaps add a parameter to GetByIdAsyc to request that the Posts be loaded as well. – David Browne - Microsoft Jul 31 '17 at 16:56
@Gusman That is why I said "assuming". And how does the row count in Topics affect this solution anyway? I am curious. – Tanveer Badar Jul 31 '17 at 16:58
Simply because the more records you have on topics the more posts you have. If each Topic has an average of 100 posts (just random numbers) then loading 1000 topics would load 100000 posts. – Gusman Jul 31 '17 at 16:59
Ok, forget the thousands, I didn't realized it was by id, anyway if the function is used widely and post's aren't required usually that would be a performance killer. – Gusman Jul 31 '17 at 17:01
Yeah, I was astounded that Include().First() would force everything to load. That would be horrendous at the least. I think you also down voted my answer? – Tanveer Badar Jul 31 '17 at 17:02
@Tanveer, your example is OK, but should keep in mind, that it's EagerLoading, not LazyLoading with all possible problems when selecting a set of topics. – Vladimirs Gorkins Jul 31 '17 at 19:17

Entity Framework - lazy loading or additional async/await query method?

3 Answers3

Linked