这是我的表格：
PupilNutrition
（学生营养）Id PupilId NutritionId
1 10 100
2 10 101
我的另一个表格营养：
Id Nutritioncategory BatchId NutritionRate NutritionId Operation
1 A 1 9000 100 1
2 B 1 5000 100 0
3 C 1 5000 100 1
4 D 2 6000 101 2
5 E 2 7000 101 2
6 F 2 8000 101 0
这是一个用于存储最终费率的字段：
decimal Rate= 0;
案例1: 操作字段值为0且批次ID为1
Rate= Rate + NutritionRate(i.e 5000 because for batch id 1 with condition 0 only 1 record is there).
案例2: 现在针对操作字段值为1和批次ID为1的情况
Here i want to sum up both the value i.e(10000 and 5000) but during addition if sum of both is greater than 10000 then just take 10000 else take sum like this:
if(9000 + 5000 > 10000)
Rate= 10000
else
Rate=Rate + (Addition of both if less than 10000).
案例3:现在针对操作字段值为0和批次ID为2的情况
Rate= Rate + NutritionRate(i.e 8000 because for batch id 1 with condition 0 only 1 record is there).
案例 4: 现在针对操作字段值为2和批次ID为2的情况
Here I will select Maximum value from Nutrition Rate field if there are 2 records:
Rate=Rate - (Maximum value from 6000 and 7000 so will take 7000).
我的Class文件：
public partial class PupilNutrition
{
public int Id { get; set; }
public int PupilId { get; set; }
public int NutritionId { get; set; }
}
public partial class Nutrition
{
public int Id { get; set; }
public string Nutritioncategory { get; set; }
public decimal NutritionRate { get; set; }
public Nullable<int> NutritionId { get; set; }
}
这是我的做法：
batchId=1 or 2;//
List<int> NutritionId = db.PupilNutrition.Where(x => PupilId == 10).Select(c => c.NutritionId).ToList(); //output 100,101
var data = db.Nutrition.Where(x => x.BatchId == batchId && NutritionId.Contains(x.NutritionId.Value)).ToList();
我尝试过这样做，但这是一项繁琐的工作：
var data1=data.where(t=>t.Operation==0);
Rate= Rate + data1.Sum(p => p.NutritionRate);
同样适用于操作1：
var data2=data.where(t=>t.Operation==1);
This is left because here i need to sum up 2 value if two records are there as shown in my above cases and if sum is greater than 10000 than select 10000 else select sum.
同样的，对于操作2：
var data3=data.where(t=>t.Operation==2);
Rate= Rate - data3.Max(p => p.NutritionRate);
我认为我已经完成了这个劳动过程，但我想使用linq查询来更好地完成它。
所以有人可以帮我简化整个linq查询过程或者其他更好的方式，并提供Operation Value 2的解决方案吗？