Is foreach with FindAsync is good for performance?

Vova Kotov 0
Viewed 47

I have a fairly short, but it seems to me, questionable query in terms of performance:

        List<Example> examples = await _dataContext.Examples.ToListAsync();

        foreach (Example example in examples)
        {
            User? user = await _dataContext.Users.FindAsync(example.UserId);

            if (user != null) user.Points += example.Worth * example.Multiplier;
        }

        await _dataContext.SaveChangesAsync();
        await _dataContext.Database.ExecuteSqlRawAsync("TRUNCATE TABLE\"Examples\"");

Is this option really bad for performance and are there any alternatives to it? Thank you!

.net-core entity-framework-core c#
1 Answers

To use contains to fetch multiple entities is much faster. If the entity list to load is large, it is best to split the contains to a fix size. This will ensure to hit the SQL-query cache on the server.

Your example makes a bit no sense. The points should be updated on "example" creation and the fastest possible in your example would be to write a UPDATE RAW-SQL to calc. and update the points server site.

Here is the better batch lookup method (assuming example-ids are coming from somewhere else then the database)

 List<Example> examples = await _dataContext.Examples.ToListAsync();

 var exampleIds = examples.Select(n => n.UserId).Chunk(10);

        foreach (var ids in exampleIds)
        {
            var userQuery = _dataContext.Users.Where(n => ids.Contains(n.UserId));

           await foreach(var user in userQuery.AsAsyncEnumerable())
           {
              user.Points += example.Worth * example.Multiplier;
           }
        }

        await _dataContext.SaveChangesAsync();
        await _dataContext.Database.ExecuteSqlRawAsync("TRUNCATE TABLE\"Examples\"");
Andreas Dirnberger0
Related