So I've noticed that we have a ton of database calls and trying to reduce them to a minimum, but I ran into issues attempting it.
How I've normally done it:
List<ItemData> itemData = new List<ItemData>()
{
new ItemData() { Id = null, ItemId = 1, AttributeId = 1001, Value = "Test" },
new ItemData() { Id = null, ItemId = 1, AttributeId = 1002, Value = "Test2" },
new ItemData() { Id = null, ItemId = 2, AttributeId = 1001, Value = "Test3" }
};
List<ItemData> dbData = new List<ItemData>() // a mock of database data
{
new ItemData() { Id = 1, ItemId = 1, AttributeId = 1001, Value = "Test234324" },
new ItemData() { Id = 2, ItemId = 1, AttributeId = 1005, Value = "Test223234" },
new ItemData() { Id = 3, ItemId = 1, AttributeId = 1006, Value = "Test234343" },
new ItemData() { Id = 4, ItemId = 2, AttributeId = 1001, Value = "Test234242" },
new ItemData() { Id = 5, ItemId = 2, AttributeId = 1002, Value = "Test234342" },
new ItemData() { Id = 6, ItemId = 2, AttributeId = 1005, Value = "Test323423" }
};
var newItems = new List<ItemData>();
foreach (var item in itemData)
{
var dbItem = db.ItemData.Where(c => c.ItemId == item.ItemId && c.AttributeId == item.AttributeId).SingleOrDefault();
if (dbItem != null) // update
{
dbItem.Value = item.Value;
}
else // add
{
newItems.Add(item);
}
}
db.ItemData.AddRange(newItems);
db.SaveChanges();
but for every attribute call that's a call to the database which isn't ideal, so I tried to go a where -> any method to grab the database values where one of the items being passed in (itemData) has a match:
using (var db = new MyDbContext())
{
var dbItems = dbItems.Where(c => itemData.Any(d => d.ItemId == c.ItemId && d.AttributeId == c.AttributeId)).ToList();
// var dbItems = db.ItemData.Where(c => itemData.Any(d => d.ItemId == c.ItemId && d.AttributeId == c.AttributeId)).ToList();
foreach (var dbItem in dbItems)
{
// not sure this would work, but goal is to update the database values and db.SaveChanges() at the end to commit them
var item = itemData.Where(c => c.ItemId == dbItem.ItemId && c.AttributeId == dbItem.ItemId).SingleOrDefault();
dbItem.Value = item.Value;
}
// failing that they don't exist, add them. This one isn't as much of an issue (just add range them)
...
db.SaveChanges();
}
but when that is done, can't create a constant value of type ItemData. Only primitives or enumeration types are able to do so. My first thought was this was due to the Any, but after doing some reading, I feel like it is the itemData preceding the Any.
How would I rewrite the Where -> Any to grab the dbItems that have an itemData analog?
edit: sorry, forgot to include that I'm using EntityFramework 6.4.4