LINQ表达式组合/连接(LINQ Expression to combine / join)

LINQ表达式组合/连接(LINQ Expression to combine / join)

我正在开发一个配方管理器程序，首先使用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.ItemName

I'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) }