EF Core: prevent individual queries when updating data

entity-framework entity-framework-core

Question

I've got data in two tables in a SQL database. I've got EF Core 2.0 sitting on top. I have a method that takes in the results of a google sheet, and will compare to the values in the database. Right now I can see that in my for loop, it's doing an individual select query for every comparison. I tried doing .Load() on both tables in question but that didn't seem to have an effect. How can I get EF Core to rely on the in-memory data and avoid all of the additional DB hits?

public async Task UpdateRatingsFromGoogleDoc(IList<IList<object>> values)
        {
            bool dirty = false;
            if (values != null && values.Count > 0)
            {
        //Tried this but hasn't helped
                _myDb.Users.Load();
                _myDb.RatingInfo.Load();

                foreach (var row in values)
                {
                    string lastName = (string)row[0];
                    string firstName = (string)row[1];
                    string ratingRawVal = (string)row[3];
                    int rating = 0;
                    if (ratingRawVal != "Rec")
                        if (!(int.TryParse(ratingRawVal, out rating)))
                            continue;
                    var userQuery = _myDb.Users.Where(u => u.FirstName == firstName && u.LastName == lastName);
                    if (userQuery.Count() == 1)
                    {
                        var userId = userQuery.FirstOrDefault().Id;
                        var member = _myDb.RatingInfo.Where(f => f.UserId == userId).FirstOrDefault();
                        if (member.Rating != rating)
                        {
                            member.Rating = rating;
                            member.RatingIsEstimate = false;
                            member.RatingSetTime = DateTime.Now;
                            dirty = true;
                        }
                    }

                }
                if (dirty)
                    await _myDb.SaveChangesAsync();
            }
        }

Update: I've also tried _myDb.Users.Include(u=>u.RatingInfos).Load(); this also doesn't seem to work.

1
0
9/9/2018 9:02:27 PM

Popular Answer

If you wan to reduce database queries and you don't need latest changes to database you should cache the data, use a temporary variable to cache data and add .ToList() at the end of your query to prevent lazy loading.

0
9/10/2018 3:36:07 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow