使用 linq 根据其他列值检索行值

Question

我有三个数据表，基本上包含以下数据：

表格1：

ID      FContractID     | WaitingTime

1   |   1       |       85

2   |   1       |       98

3   |   1       |       122

4   |   1       |       45

5   |   1       |       234

6   |   1       |       101

等等。

表 2：

PricingCriterionItemId  PricingCriterionName        PricingCriterionUnit

1                   |   WaitingTimeMax          |   min

2                   |   WaitingTimePeriod       |   min

3                   |   WaitingTimeOverdue      |   €/period

4                   |   OverDuePriceMax         |   €

表3：

PricingCriterionId      ContractID      PricingCriterionItemId      PricingCriterionValue

1                   |   1           |   1                       |   70

2                   |   1           |   2                       |   30

3                   |   1           |   3                       |   30,00

4                   |   1           |   4                       |   120,00

我想在表 1 中添加一个包含等待时间成本的列。等待时间成本将计算如下

WaitingTimeCost = min(((WaitingTime - WaitingTimeMax) / WaitingTimePeriod) * WaitingTimeOverdue, OverDuePriceMax)

是否可以使用 linq 使用其他列值在特定行上分配列值？

就像是

  var result = from WaitingData in table1

                join PricingCriteria in table4 

                on WaitingData.ContractId equals PricingCriteria.ContractId

                let WaitingTimeMax = (select PricingCriterionValue from table4 where PricingCriterionName = "WaitingTimeMax")

                let ...

                let WaitingTimeCost = min(((WaitingTime - WaitingTimeMax) / WaitingTimePeriod) * WaitingTimeOverdue, OverDuePriceMax)

                select new

                {

                    ID,

                    WaitingTimeCost

                }

如何使用 linq 正确地制定这个？

Answer 1

您不需要在数据库查询中执行所有操作。数据库只是IO设备，只负责读写数据。加载所有需要的数据并计算。var contractsId = waitingData.Select(data => data.ContractId).ToList();var pricingCriterias = table4.Where(criteria => contractsId.Contains(criteria.ContractId)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.ToLookup(criteria => criteria.ContractId);var maxWaitingTime =&nbsp;&nbsp; &nbsp; pricingCriterias.SelectMany(group => group)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Where(criteria => criteria.PricingCriterionName = "WaitingTimeMax")&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Max(criteria => criteria.PricingCriterionValue);foreach (var waitingItem in waitingData){&nbsp; &nbsp; // Calculate others waiting values&nbsp; &nbsp; var waitingPerPeriod = (WaitingTime - maxWaitingTime) / WaitingTimePeriod);&nbsp; &nbsp; var waitingPrice = waitingPerPeriod * WaitingTimeOverdue;&nbsp; &nbsp; var WaitingTimeCost = Math.Min(waitingPrice, OverDuePriceMax)}下面的示例与DataTables.然而，它DataTable是沉重且非强类型的数据结构，作为开发人员需要更多的时间来处理它。而是将数据转换DataTable为具有描述性属性名称的普通强类型对象 - 您将免费获得 IntelliSense ;)var contractsId =&nbsp;&nbsp; &nbsp; waitingData.AsEnumerable()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.Select(row => row.Field<int>("ContractId"))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.ToList();var pricingCriterias =&nbsp;&nbsp; &nbsp; table4.AsEnumerable()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Where(row => contractsId.Contains(row => row.Field<int>("ContractId"))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .ToLookup(row => row.Field<int>("ContractId"));var maxWaitingTime =&nbsp;&nbsp; &nbsp; pricingCriterias.SelectMany(group => group)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Where(row => row.Field<string>("PricingCriterionName") = "WaitingTimeMax")&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Max(row => row.Field<int>("PricingCriterionValue"));foreach (var waitingItem in waitingData){&nbsp; &nbsp; // Calculate others waiting values&nbsp; &nbsp; var waitingPerPeriod = (WaitingTime - maxWaitingTime) / WaitingTimePeriod);&nbsp; &nbsp; var waitingPrice = waitingPerPeriod * WaitingTimeOverdue;&nbsp; &nbsp; var WaitingTimeCost = Math.Min(waitingPrice, OverDuePriceMax)}