0

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

Robert
  • 1,745
  • 5
  • 34
  • 61

0 Answers0