How to properly cache a table in Entity Framework for this use case-CodePudding

var fdPositions = dbContext.FdPositions.Where(s => s.LastUpdated > DateTime.UtcNow.AddDays(-1));

foreach (JProperty market in markets)
{
    // bunch of logic that is irrelevant here
    var fdPosition = fdPositions.Where(s => s.Id == key).FirstOrDefault();

    if (fdPosition is not null)
    {
        fdPosition.OddsDecimal = oddsDecimal;
        fdPosition.LastUpdated = DateTime.UtcNow;
    }
    else
    {
        // bunch of logic that is irrelevant here
    }
}

await dbContext.SaveChangesAsync();

This block of code will make 1 database call on this line

var fdPosition = fdPositions.Where(s => s.Id == key).FirstOrDefault();

for each value in the loop, there will be around 10,000 markets to loop through.

What I thought would happen, and what I would like to happen, is 1 database call is made

var fdPositions = dbContext.FdPositions.Where(s => s.LastUpdated > DateTime.UtcNow.AddDays(-1));

on this line, then in the loop, it is checking against the local table I thought I pulled on the first line, making sure I still properly am updating the DB Object in this section though

if (fdPosition is not null)
{
    fdPosition.OddsDecimal = oddsDecimal;
    fdPosition.LastUpdated = DateTime.UtcNow;
}

So my data is properly propagated to the DB when I call

await dbContext.SaveChangesAsync();

How can I update my code to accomplish this so I am making 1 DB call to get my data rather than 10,000 DB calls?

CodePudding user response：

Define your fdPositions variable as a Dictionary<int, T>, in your query do a GroupBy() on Id, then call .ToDictionary(). Now you have a materialized dictionary that lets you index by key quickly.

var fdPositions = context.FdPositions.Where(s => s.LastUpdatedAt > DateTime.UtcNow.AddDays(-1))
                .GroupBy(x=> x.Id)
                .ToDictionary(x=> x.Key, x=> x.First());

//inside foreach loop: 

// bunch of logic that is irrelevant here
bool found = fdPositions.TryGetValue(key, out var item);