I have a generic class that performs add/update on entities of type T. The AddOrUpdate()
method takes in a DbSet
collection to act on as well as a list of items to add or update in the DbSet
. The ItemExists()
is used to check to see if an item already exists in the collection. If it does, we update. If not, we add. The method essentially compares the primary key of the item passed in with every single item in the table, and returns true (as well as the database object itself) if there's a match.
The code works fine for tables with small number of records. For larger tables however, the ItemExists()
method is very inefficient. The method uses a foreach loop which itself is inside another foreach loop in the caller method, giving O(n^2).
An easier way would be to simply use contextDataSet.Contains(item)
, but that throws an exception that says Unable to create a constant value of type
which makes sense since EF can't translate the class into a SQL query. So that's a no go.
Now my actual question: is there a way to replace the entire DbSet<T>
with IEnumerable<T>
that gets passed in? The IEnumerable that gets passed in is bound to a datagrid on the view and essentially includes all the items, so logically speaking, replacing the entire collection should be safe. Any help is greatly appreciated.
Code
public void AddOrUpdate<I, P>(Expression<Func<I, P>> dbSetExpression, IEnumerable<T> itemsToUpdate)
where I : DbContext, new()
where P : DbSet<T>
{
DataFactory.PerformOperation<I>(c =>
{
if (m_primaryKey == null && !TryFindPrimaryKey(c))
{
throw new ArgumentException("Primary key cannot be null.");
}
// Get the table name from expression passed in.
string dbsetName = ((MemberExpression)dbSetExpression.Body).Member.Name;
var propertyInfo = c.GetType().GetProperties().Single(p => p.Name == dbsetName);
// Get the values in the table.
DbSet<T> contextDataSet = propertyInfo.GetValue(c) as DbSet<T>;
foreach (var item in itemsToUpdate)
{
// If the primary key already exists, we're updating. Otherwise we're adding a new entity.
T existingItem;
if (ItemExists(contextDataSet, item, out existingItem) && existingItem != null)
{
c.Entry(existingItem).CurrentValues.SetValues(item);
}
else
{
contextDataSet.Add(item);
}
}
c.SaveChanges();
});
}
private bool ItemExists(DbSet<T> itemInDbSet, T itemInList, out T existingItem)
{
foreach (var dbItem in itemInDbSet)
{
// Get the primary key value in the database.
var dbValue = dbItem.GetType().GetProperties().Single(
p => p.Name == m_primaryKey).GetValue(dbItem);
// Get the primary key value from the item passed in.
var itemValue =
itemInList.GetType().GetProperties().Single(
p => p.Name == m_primaryKey).GetValue(itemInList);
// Compare the two values.
if (dbValue.ToString() == itemValue.ToString())
{
existingItem = dbItem;
return true;
}
}
existingItem = null;
return false;
}