在C#代码中将使用MERGE的SQL进程替换为LINQ

Question

我需要将一个进程从SQL Server转移到我的应用程序代码中。T-SQL过程使用MERGE，如下所示，有条件地更新或插入。

-- Synchronize the InterestRates table with refreshed/new data from ImportRates_Stg table
MERGE InterestRates AS TARGET
USING ImportRates_Stg AS SOURCE
ON (TARGET.Effective = SOURCE.EffectiveDate)
-- When records are matched on the Effective date, update the records if there is any change to the Rate
WHEN MATCHED AND TARGET.Effective = SOURCE.EffectiveDate
THEN UPDATE SET TARGET.Rate = SOURCE.Rate
-- When no records are matched on the Effective date,
-- insert the incoming records from ImportRates_Stg table to InterestRates table
WHEN NOT MATCHED BY TARGET
THEN INSERT (Effective, Rate) VALUES (SOURCE.EffectiveDate, Rate);

我需要在C#中复制这个功能，我认为LINQ可能是最好的方法，但到目前为止，我的所有尝试都失败了。这是我迄今为止掌握的代码。将数据从excel文件导入列表是可行的。当我进入实际逻辑来替换不起作用的SQL MERGE时。。。

public async Task<IActionResult> OnPostAsync()
{
// Perform an initial check to catch FileUpload class attribute violations.
if (!ModelState.IsValid)
{
return Page();
}
string filePath = RatesBatchImportFilepath + Path.GetFileName(Request.Form.Files["RatesExtract"].FileName);
using (FileStream fileStream = new FileStream(filePath, FileMode.Create))
{
await Request.Form.Files["RatesExtract"].CopyToAsync(fileStream);
}
var newRates = new List<InterestRate>();
using (var wb = new XLWorkbook(filePath, XLEventTracking.Disabled))
{
var ws = wb.Worksheet(1);
DataTable dataTable = ws.RangeUsed().AsTable().AsNativeDataTable();
if (dataTable.Rows.Count > 0)
{
foreach (DataRow dataRow in dataTable.Rows)
{
if (dataRow.ItemArray.All(x => string.IsNullOrEmpty(x?.ToString()))) continue;
newRates.Add(new InterestRate()
{
Effective = Convert.ToDateTime(dataRow["PeriodEndingDate"]),
Rate = Convert.ToDecimal(dataRow["AY01NetPerf"])
});
};
}
}
IQueryable<InterestRate> existingRates = from s in _context.InterestRates
orderby s.Effective descending
select s;
foreach (var oldRate in existingRates)
{
DateTime ourDate = oldRate.Effective;
var thisUpdateQuery =
from thisRate in newRates
where thisRate.Effective == ourDate
select thisRate;
foreach (var rate in thisUpdateQuery)
{
oldRate.Effective = rate.Effective;
newRates.Remove(rate); // this causes an error.
}
}
foreach (var rate in newRates)
{
Rates.Add(rate);
}
_context.SaveChanges();
return RedirectToPage("./Index");
}

以下是错误：InvalidOperationException：集合已被修改；枚举操作可能无法执行。

Answer 1

这里是一个合并函数，具有sql:中的所有选项，如merge

public static async Task Merge<T>(this List<T> target, List<T> source, Func<T, T, bool> mergeOn, Func<T, T, Task> onMatched = null, Func<List<T>, Task> whenNotMatchedByTarget = null, Func<List<T>, Task> whenNotMatchedBySource = null)
{
var sourceTemp = JsonConvert.DeserializeObject<List<T>>(JsonConvert.SerializeObject(source, new JsonSerializerSettings() { ReferenceLoopHandling = ReferenceLoopHandling.Ignore }));
var notMatchedByTarget = new List<T>();
bool isMatched;
for (int i = 0; i < target?.Count; i++)
{
isMatched = false;
for (int j = 0; j < sourceTemp?.Count; j++)
{
if (mergeOn(target[i], sourceTemp[j]))
{
if (onMatched != null)
await onMatched(target[i], sourceTemp[j]);
sourceTemp.RemoveAt(j);
isMatched = true;
break;
}
}
if (!isMatched)
notMatchedByTarget.Add(target[i]);
}
if (whenNotMatchedByTarget != null)
await whenNotMatchedByTarget(notMatchedByTarget);
if (whenNotMatchedBySource != null)
await whenNotMatchedBySource(sourceTemp);
}

然后像这样使用：-

await targetList.Merge(source: sourceList,
mergeOn: (t, s) => t.Id == s.Id,
onMatched: Update,
whenNotMatchedByTarget: Delete,
whenNotMatchedBySource: Add);

Task Update(sourceType old, sourceType new){};
Task Delete(List<sourceType> listToBeDeleted){};
Task Add(List<sourceType> listToBeAdded){};

Answer 2

这里有一个我认为有效的扩展方法——

public static List<T> Merge<T>(this List<T> list, Func<T, T, bool> mergeOnFunc, Action<T, T> ifMatchedByTargetAction)
{
List<T> mergedList = new List<T>();
list.ForEach(rec =>
{
if (!mergedList.Any(x => mergeOnFunc(rec, x)))
mergedList.Add(rec); // if the record hasn't been added yet, add it
else // if the record has already been added, merge the changes
ifMatchedByTargetAction(mergedList.Where(x => mergeOnFunc(rec, x)).First(), rec);
});
return mergedList;
}

这里有一个如何使用扩展方法的例子(假设您已经将所有的可合并项分批到一个单独的列表w.concat或类似的东西中——对不起，这不是一个理想的用例，但我有一个(。

classAttendanceList = classAttendanceList.Merge(
(a, b) => { return a.StudentName == b.StudentName; }, // this is your "ON"
(a, b) => { a.DaysAttended += b.DaysAttended; }); // this is your "WHEN MATCHED"