Batching Entity Framework queries for more efficiency

Question

If I have the code snippet below that queries the db on each loop, is there a way to make it more efficient by running the query just once and passing in a list or collection?

using (var dbContext = new YogabandyContext(ybDatabaseConnectionString))
{
    foreach (StripeBalanceTransaction transaction in balanceTransactions)
    {
        var profileCharge = dbContext.Charges.Where(i => i.BalanceTransactionId == transaction.Id).FirstOrDefault();

        if (profileCharge == null)
        {
            // do some error work
        }
        else
        {
            profileCharge.PayoutStatus = PayoutStatus.Succeeded;
            profileCharge.PayoutId = payoutId;
            profileCharge.PayoutObjectResponse = stripeEvent.StripeResponse.ObjectJson;
        }
    }

    dbContext.SaveChanges();
}

Answer 1

You can refactor this method. Only two queries will be performed to database instead of balanceTransactions.Count + 1 times:

var ids = balanceTransactions.Select(x => x.Id).ToList();

//first query
var profileCharges = dbContext.Charges
                         .Where(x => ids.Contains(x.BalanceTransactionId).ToList();

var existedIds = profileCharges.Select(x => x.BalanceTransactionId).ToList();
var notExisted = balanceTransactions.Where(x => !existedIds.Contains(x.Id)).ToList()
foreach(var transaction in notExisted)
{
    //do some error work
}

profileCharges.ForEach(x => 
{
     x.PayoutStatus = PayoutStatus.Succeeded;
     x.PayoutId = payoutId;
     x.PayoutObjectResponse = stripeEvent.StripeResponse.ObjectJson;
})

//second query
dbContext.SaveChanges();