c# - 使用 linq 查询简化流程

Question

这是我的 table :

瞳孔营养

Id     PupilId   NutritionId
1       10          100  
2       10          101

我的另一张 table 营养:

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          

这是一个存储最终Rate的字段:

decimal Rate= 0;

案例 1:值为 0 且 Batch Id 为 1 的操作字段

 Rate= Rate + NutritionRate(i.e 5000 because for batch id 1 with condition 0 only 1 record is there).

案例 2:现在对于值为 1 和 Batch 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 和 Batch 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 提供解决方案吗？

Answer 1

最短的并不总是最好的。

就我个人而言，我认为单个复杂的 LINQ 语句可能难以阅读和调试。

这是我会做的:

    public decimal CalculateRate(int pupilId, int batchId)
    {
        return db.Nutrition
            .Where(x => x.BatchId == batchId && db.PupilNutrition.Any(y => y.PupilId == pupilId && y.NutritionId == x.NutritionId))
            .GroupBy(x => x.Operation)
            .Select(CalculateRateForOperationGroup)
            .Sum();
    }

    public decimal CalculateRateForOperationGroup(IGrouping<int, Nutrition> group)
    {
        switch (group.Key)
        {
            case 0:
                // Rate = Sum(x)
                return group.Sum(x => x.NutritionRate);
            case 1:
                // Rate = Min(10000, Sum(x))
                return Math.Min(10000, group.Sum(x => x.NutritionRate));
            case 2:
                // Rate = -Max(x)
                return -group.Max(x => x.NutritionRate);
            default:
                throw new ArgumentException("operation");
        }
    }

然后你可以按如下方式使用它:

var rateForFirstBatch = CalculateRate(10, 1);
var rateForSecondBatch = CalculateRate(10, 2);