这是我的情况-我有一个DB,它有一些名为食谱、配料和配方的表。
食谱由1+种成分组成。
配方在食谱和配料表之间有FK。
生成的类是recipe
和ingredient
,并且recipe
具有如下的导航属性:
public virtual ICollection<ingredients> ingredients { get; set; }
很好,我知道我得到了一个生成的recipe
类和一个生成了的ingredient
类,而recipes_ingredients
表并没有得到生成的类,因为EF只是将其视为导航属性。
现在,我得到了一个名为SetIngredientsForRecipe
的函数,它看起来是这样的(为了简洁起见,去掉了try-catch代码:
public void SetIngredientsForRecipe(long recipeId, List<string> ingredients)
{
using (var db = new FoodEntities(ConnectionString, null, null))
{
var existing = GetCurrentIngredients(recipeId);
var toRemove = existing.Except(ingredients);
var toAdd = ingredients.Except(existing);
var recipe = db.recipes.Where(r => r.Id == recipeId).FirstOrDefault();
foreach (var name in toRemove)
{
var entry = recipe.ingredients.Where(i => i.Name == name).FirstOrDefault();
recipe.ingredients.Remove(entry);
}
foreach (var name in toAdd)
{
var entry = db.ingredients.Where(i => i.Name == name).FirstOrDefault();
recipe.ingredients.Add(entry);
}
db.SaveChanges();
}
}
顾名思义,其目的是将给定配方的配料表更新为列表中的任何配料。我仍然对EF感到满意,并想知道是否有更好(更有效?)的方法来完成我想要做的事情。
跟进:
根据下面ntziolis的建议,我选择使用
recipe.ingredients.Clear()
清除配方/成分映射中的任何内容,然后使用提到的嘲讽快速添加新的内容。类似这样的东西:
foreach (var name in ingredients)
{
// Mock an ingredient since we just need the FK that is referenced
// by the mapping table - the other properties don't matter since we're
// just doing the mapping not inserting anything
recipe.ingredients.Add(new Ingredient()
{
Name = name
});
}
这非常有效。
一般性能准则为:
- 尽量只处理id
- 尽可能模拟实体,而不是从数据库中检索它们
- 使用EF4的新功能,如
Contains
,以简化和加快代码
基于这些原则,这里有一个优化的(但不是更简单的)解决方案来解决您的问题:
public void SetIngredientsForRecipe(long recipeId, List<string> ingredients)
{
using (var db = new FoodEntities(ConnectionString, null, null))
{
var recipe = db.recipe.Single(r => r.ID == recipeId);
// make an array since EF4 supports the contains keyword for arrays
var ingrArr = ingredients.ToArray();
// get the ids (and only the ids) of the new ingredients
var ingrNew = new HasSet<int>(db.ingrediants
.Where(i => ingrArr.Contains(i.Name))
.Select(i => I.Id));
// get the ids (again only the ids) of the current receipe
var curIngr = new HasSet<int>(db.receipes
.Where(r => r.Id == recipeId)
.SelectMany(r => r.ingredients)
.Select(i => I.Id));
// use the build in hash set functions to get the ingredients to add / remove
var toAdd = ingrNew.ExpectWith(curIngr);
var toRemove = curIngr.ExpectWith(ingrNew);
foreach (var id in toAdd)
{
// mock the ingredients rather than fetching them, for relations only the id needs to be there
recipe.ingredients.Add(new Ingredient()
{
Id = id
});
}
foreach (var id in toRemove)
{
// again mock only
recipe.ingredients.Remove(new Ingredient()
{
Id = id
});
}
db.SaveChanges();
}
}
如果你想更简单,你可以清除所有成分,并在必要时重新添加,EF甚至可能足够聪明,可以发现关系没有改变,但也不确定:
public void SetIngredientsForRecipe(long recipeId, List<string> ingredients)
{
using (var db = new FoodEntities(ConnectionString, null, null))
{
var recipe = db.recipe.Single(r => r.ID == recipeId);
// clear all ingredients first
recipe.ingredients.Clear()
var ingrArr = ingredients.ToArray();
var ingrIds = new HasSet<int>(db.ingrediants
.Where(i => ingrArr.Contains(i.Name))
.Select(i => I.Id));
foreach (var id in ingrIds)
{
// mock the ingredients rather than fetching them, for relations only the id needs to be there
recipe.ingredients.Add(new Ingredient()
{
Id = id
});
}
db.SaveChanges();
}
}
更新
一些编码错误已经纠正。
您可以使用FirstOrDefault
调用压缩Where
子句:
recipe.ingredients.FirstOrDefault(i => i.Name == name);
虽然我个人更喜欢使用SingleOrDefault
,但我不确定到底有什么区别:
recipe.ingredients.SingleOrDefault(i => i.Name == name);
此外,由于传入的成分列表是List<string>
(与成分ID列表相反),这在某种程度上意味着,作为这个过程的一部分,也可能会创建新的成分,而这个过程没有被处理(尽管为了简洁起见,可能被省略了)。