我有三個(gè)數(shù)據(jù)表，基本上包含以下數(shù)據(jù)：表格1：ID FContractID | WaitingTime1 | 1 | 852 | 1 | 983 | 1 | 1224 | 1 | 455 | 1 | 2346 | 1 | 101等等。表 2：PricingCriterionItemId PricingCriterionName PricingCriterionUnit1 | WaitingTimeMax | min2 | WaitingTimePeriod | min3 | WaitingTimeOverdue | €/period4 | OverDuePriceMax | €表3：PricingCriterionId ContractID PricingCriterionItemId PricingCriterionValue1 | 1 | 1 | 702 | 1 | 2 | 303 | 1 | 3 | 30,004 | 1 | 4 | 120,00我想在表 1 中添加一個(gè)包含等待時(shí)間成本的列。等待時(shí)間成本將計(jì)算如下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 正確地制定這個(gè)？
1 回答
撒科打諢
TA貢獻(xiàn)1934條經(jīng)驗(yàn) 獲得超2個(gè)贊
您不需要在數(shù)據(jù)庫(kù)查詢中執(zhí)行所有操作。
數(shù)據(jù)庫(kù)只是IO設(shè)備，只負(fù)責(zé)讀寫數(shù)據(jù)。
加載所有需要的數(shù)據(jù)并計(jì)算。
var contractsId = waitingData.Select(data => data.ContractId).ToList();
var pricingCriterias = table4.Where(criteria => contractsId.Contains(criteria.ContractId)
.ToLookup(criteria => criteria.ContractId);
var maxWaitingTime =
pricingCriterias.SelectMany(group => group)
.Where(criteria => criteria.PricingCriterionName = "WaitingTimeMax")
.Max(criteria => criteria.PricingCriterionValue);
foreach (var waitingItem in waitingData)
{
// Calculate others waiting values
var waitingPerPeriod = (WaitingTime - maxWaitingTime) / WaitingTimePeriod);
var waitingPrice = waitingPerPeriod * WaitingTimeOverdue;
var WaitingTimeCost = Math.Min(waitingPrice, OverDuePriceMax)
}
下面的示例與DataTables.
然而，它DataTable是沉重且非強(qiáng)類型的數(shù)據(jù)結(jié)構(gòu)，作為開(kāi)發(fā)人員需要更多的時(shí)間來(lái)處理它。
而是將數(shù)據(jù)轉(zhuǎn)換DataTable為具有描述性屬性名稱的普通強(qiáng)類型對(duì)象 - 您將免費(fèi)獲得 IntelliSense ;)
var contractsId =
waitingData.AsEnumerable()
.Select(row => row.Field<int>("ContractId"))
.ToList();
var pricingCriterias =
table4.AsEnumerable()
.Where(row => contractsId.Contains(row => row.Field<int>("ContractId"))
.ToLookup(row => row.Field<int>("ContractId"));
var maxWaitingTime =
pricingCriterias.SelectMany(group => group)
.Where(row => row.Field<string>("PricingCriterionName") = "WaitingTimeMax")
.Max(row => row.Field<int>("PricingCriterionValue"));
foreach (var waitingItem in waitingData)
{
// Calculate others waiting values
var waitingPerPeriod = (WaitingTime - maxWaitingTime) / WaitingTimePeriod);
var waitingPrice = waitingPerPeriod * WaitingTimeOverdue;
var WaitingTimeCost = Math.Min(waitingPrice, OverDuePriceMax)
}
- 1 回答
- 0 關(guān)注
- 182 瀏覽
添加回答
舉報(bào)
0/150
提交
取消