Replace a SQL process that uses MERGE with LINQ in C# code

Question

I need to transfer a process from SQL Server to my application code. The T-SQL process uses MERGE, as shown below, to conditionally update or insert.

-- 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);

I need to reproduce this functionality in C#, and am thinking that LINQ would likely be the best way to do this, but so far all of my attempts have failed. Here is the code that I have so far. Importing the data to a list from an excel file is all working. It is when I get to the actual logic to replace the SQL MERGE which is not working...

        public async Task 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();

        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 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");
    }

Here is the Error: InvalidOperationException: Collection was modified; enumeration operation may not execute.

user_15863397 · Answer

here you are a merge function with all options like merge in sql:

    public static async Task Merge(this List target, List source, Func mergeOn, Func onMatched = null, Func, Task> whenNotMatchedByTarget = null, Func, Task> whenNotMatchedBySource = null)
    {
        var sourceTemp = JsonConvert.DeserializeObject>(JsonConvert.SerializeObject(source, new JsonSerializerSettings() { ReferenceLoopHandling = ReferenceLoopHandling.Ignore }));
        var notMatchedByTarget = new List();
        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);
    }

then use it like:-

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 listToBeDeleted){};
Task Add(List listToBeAdded){};

user_1921544 · Answer

Here's an extension method that I think works --

    public static List Merge(this List list, Func mergeOnFunc, Action ifMatchedByTargetAction)
    {
        List mergedList = new List();
        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;
    }

Here's an example of how you'd use the extension method (assumes you've already batched all of your mergeables into a single list w. concat or something similar -- sorry, it's not an ideal use case, but it's the one I have).

            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"

Replace a SQL process that uses MERGE with LINQ in C# code

2 Answers