Using DbSet.Load() and DbSet.Local in entity framework core
One of the common scenarios that people usually face in application development is to upload a bulk of raw data (maybe from an excel or csv import) and save it in the database using entity framework. Data might be in plain format but the actual entity would usually be hierarchical.
For example, let us take the below table structure which represents a part of the simple expense management application.
So we have a typical relationship where many tags can be linked to an expense and vice versa (many to many) and an expense can be linked to one of the many categories (many to one).
In this case, if you are importing/uploading an expense file you would have to add all the expense and link it to correct categories and tags. If the tags and categories provided in the import file do not exist we will have to create them and then refer them in expense before saving the expense itself.
Scenarios which you might have to handle in this case are below
- If the category does not exist then add the category and refer the newly added category to expense else refer the the existing category.
- If tag does not exist then add the new tag and refer the newly added expense in the expense <-> tag mapping else refer the existing tag.
- Execute the whole thing in single transaction.
One way to achieve this is to call SaveChanges every time we add a Category or a Tag and so that it is available for the next expense to be referred. The drawback of this approach is that we lose transactional behaviour (unless you use TransactionScope which might be heavy addition for a simple usecase) and performance degradation.
Alternative is to use DbSet<entity>.Load() and DbSet<entity>.Local. Below is the sample code.
public virtual bool AddExpenses(IList<Expense> expenses)
{
try
{
//**** 1 -> LOAD THE ENTITIES IN THE CONTEXT
_context.Tags.Load();
_context.Categories.Load();
//**** 2 -> LOOP THROUGH ALL THE EXPENSES
for (int i=0;i<expenses.Count;i++) { var expenseToBeAdded = new Expense { Amount = expenses[i].Amount, Description = expenses[i].Description, ExpenseDateTime = expenses[i].ExpenseDateTime, Imported = true, Item = expenses[i].Item, User = expenses[i].User }; //**** 3 -> LOOP THROUGH ALL THE TAGS IN THE EXPENSE
expenseToBeAdded.Tags = new List<ExpenseTag>();
for (int j=0;j< expenses[i].Tags?.Count();j++) { var expenseTag = expenses[i].Tags.ElementAt(j); //***** 4 -> FIND IF THE TAG EXIST (USING LOCAL PROPERTY OF DBSET)
var existingTag = _context.Tags.Local.FirstOrDefault(x => String.Equals(x.TagName, expenseTag.Tag.TagName,StringComparison.OrdinalIgnoreCase) && x.User.Id == expenses[i].User.Id);
if(existingTag == null)
{
var newTag = new Tag() { TagName = expenseTag.Tag.TagName, User = expenses[i].User };
//***** 5 -> ADD THE TAG TO LOCAL PROPERTY OF DBSET (NOT TO THE DBSET DIRECTLY)
_context.Tags.Local.Add(newTag);
expenseToBeAdded.Tags.Add(new ExpenseTag() { Tag = newTag });
}
else
{
expenseToBeAdded.Tags.Add(new ExpenseTag { Tag = existingTag });
}
}
//***** 6 -> FIND IF THE CATEGORY EXIST (USING LOCAL PROPERTY OF DBSET)
var exisingCategory = _context.Categories.Local.FirstOrDefault(x => String.Equals(x.Name, expenses[i].ExpenseCategory.Name,StringComparison.OrdinalIgnoreCase) && x.User.Id == expenses[i].User.Id);
if(exisingCategory == null)
{
var newCategory = new Category() { Name = expenses[i].ExpenseCategory.Name, User = expenses[i].User };
//***** 7 -> ADD THE CATEGORY TO LOCAL PROPERTY OF DBSET (NOT TO THE DBSET DIRECTLY)
_context.Categories.Local.Add(newCategory);
expenseToBeAdded.ExpenseCategory = newCategory;
}
else
{
expenseToBeAdded.ExpenseCategory = exisingCategory;
}
_context.Expenses.Add(expenseToBeAdded);
}
//***** 8 -> ONLY CALL SAVECHANGES AT THE END WHEN EVERYTHING HAS BEEN ADDED TO THE CONTEXT
_context.SaveChanges();
return true;
}
catch (Exception ex)
{
_logger.LogError(ex, "Could not add multiple expenses.");
return false;
}
}
DbSet<T>.Local acts as a local cache which keeps all the tracked entities in it. So if you add or update something during a process want to refer it elsewhere you can find that in the ‘Local’ cache (this would not be available in database as SaveChanges is still not called).
In the above code, we first call Load method for Categories and Tags which basically brings these entities in memory and make them part of the local EF cache. Then we add entities to DbSet.Local and find the existing entities in DbSet.Local. All the entities added to Local are also synced with DbSet so calling SaveChanges will save all the added and updated entities as usual.
This way we complete the whole operation and call SaveChanges only once at the end which executes everything as part of a single transaction and is a lot faster than calling SaveChanges multiple times.
In case you want to learn more on entity framework core, this is an excellent course to get started with. Also if you want to learn entity framework core from end to end there is a whole pluralsight learning path for entity framework core which has excellent courses.