我正在开发一个配方管理器程序,首先使用Entity Framework 6代码。 我在数据库中有以下实体:
public class Item { [DatabaseGenerated( DatabaseGeneratedOption.Identity ), Key] public int ItemId { get; set; } [Required] public string ItemName {get; set; } // Other properties } public class Recipe { [DatabaseGenerated( DatabaseGeneratedOption.Identity ), Key] public int RecipeId { get; set; } [Required] public string RecipeName { get; set; } public virtual ICollection<Ingredient> Ingredients { get; set; } // Other properties } public class Ingredient { [Key, Column( Order = 0 )] public int RecipeId { get; set; } [ForeignKey( "RecipeId" )] public virtual Recipe Recipe { get; set; } [Key, Column( Order = 1 )] public int ItemId { get; set; } [ForeignKey( "ItemId" )] public virtual Item Item { get; set; } public double Quantity { get; set; } // Other properties } public class Meal { [DatabaseGenerated( DatabaseGeneratedOption.Identity ), Key] public int MealId { get; set; } [Required] public string MealName {get; set; } public virtual ICollection<Recipe> Recipes { get; set; } public virtual IEnumerable<Ingredient> Ingredients() { if ( Recipes == null ) yield break; foreach ( var recipe in Recipes ) foreach ( var ingredient in recipe.Ingredients ) yield return ingredient; } }我的问题是Meal类中的Ingredients属性。 那里的代码将返回制作膳食所需的所有配料列表,但我想返回一个组合列表。 也就是说,如果餐中的一个配方需要2个鸡蛋,而另一个需要1个鸡蛋,则会返回两个鸡蛋入口。 我想返回一个3个鸡蛋的入口。
有没有可用于组合这些的LINQ表达式? 相当于:
SELECT item.ItemName, SUM(ing.Quantity) FROM Meals AS m JOIN RecipeMeals AS rm ON m.MealId = rm.Meal_MealId JOIN Ingredients AS ing ON rm.Recipe_RecipeId = ing.RecipeId JOIN Items AS item ON ing.ItemId = item.ItemId GROUP BY item.ItemNameI'm working on a recipe manager program, using Entity Framework 6 code first. I have the following entities in the database:
public class Item { [DatabaseGenerated( DatabaseGeneratedOption.Identity ), Key] public int ItemId { get; set; } [Required] public string ItemName {get; set; } // Other properties } public class Recipe { [DatabaseGenerated( DatabaseGeneratedOption.Identity ), Key] public int RecipeId { get; set; } [Required] public string RecipeName { get; set; } public virtual ICollection<Ingredient> Ingredients { get; set; } // Other properties } public class Ingredient { [Key, Column( Order = 0 )] public int RecipeId { get; set; } [ForeignKey( "RecipeId" )] public virtual Recipe Recipe { get; set; } [Key, Column( Order = 1 )] public int ItemId { get; set; } [ForeignKey( "ItemId" )] public virtual Item Item { get; set; } public double Quantity { get; set; } // Other properties } public class Meal { [DatabaseGenerated( DatabaseGeneratedOption.Identity ), Key] public int MealId { get; set; } [Required] public string MealName {get; set; } public virtual ICollection<Recipe> Recipes { get; set; } public virtual IEnumerable<Ingredient> Ingredients() { if ( Recipes == null ) yield break; foreach ( var recipe in Recipes ) foreach ( var ingredient in recipe.Ingredients ) yield return ingredient; } }My problem is the Ingredients property in the Meal class. The code there will return a list of all ingredients needed to make the meal, but I want to return a combined list. That is, if one recipe in the meal needs 2 eggs and another one needs 1 egg, this returns two entries for eggs. I want to return one entry for 3 eggs.
Is there a LINQ expression I can use to combine these? Something equivalent to:
SELECT item.ItemName, SUM(ing.Quantity) FROM Meals AS m JOIN RecipeMeals AS rm ON m.MealId = rm.Meal_MealId JOIN Ingredients AS ing ON rm.Recipe_RecipeId = ing.RecipeId JOIN Items AS item ON ing.ItemId = item.ItemId GROUP BY item.ItemName最满意答案
当你定义了导航属性 (并且你确实拥有它们)时,它非常简单 - 不需要考虑连接,只需将它们导航为对象即可。 EF将为您生成必要的SQL连接。
等效的LINQ to Entities查询如下所示:
from meal in db.Meals from recipe in meal.Recipes from ingredient in recipe.Ingredients let item = ingredient.Item group ingredient by item.ItemName into itemGroup select new { ItemName = itemGroup.Key, Quantity = itemGroup.Sum(e => e.Quantity) }When you have navigation properties defined (and you do have them), it's quite easy - no need to think for joins, just "navigate" as if they are objects. EF will produce the necessary SQL joins for you.
The equivalent LINQ to Entities query is like this:
from meal in db.Meals from recipe in meal.Recipes from ingredient in recipe.Ingredients let item = ingredient.Item group ingredient by item.ItemName into itemGroup select new { ItemName = itemGroup.Key, Quantity = itemGroup.Sum(e => e.Quantity) }更多推荐
发布评论