Update 2 table values in one query in LINQ

Question

Hello,

I have 2 tables Table1 with columns [BId,Name,Amount] Table2 with columns [CId,BId, ExpenseType,Expense]. BId Is the foreign key in Table2.

The Amount field in Table1 is always higher than Expense in Table2. I need to update the values of Amount (increase or decrease) based on value of Expense and I want to do it in single query in LINQ. Eg If the Expense has to be updated with 200, I would decrease(negate) Amount value with 200. If Expense is reduce to 100 then the Amount is increased by a value of 100.

Thanks in advance for any suggestions.

Answer 1

You cannot perform updates with one query. You cannot even do that in SQL, let alone in LinQ. What you need here is a transaction. Within the transaction, You can load both table records into variables, update their values, and commit the transaction.

using (var transaction = new TransactionScope()) {
    try {
        record1 = (from r in myDataContext.Table1s where r.BId == myBid select r).FirstOrDefault();
        record2 = (from r in myDataContext.Table2s where r.BId == myBid select r).FirstOrDefault();

        // Perform your record updates here
        var oldExpense = record2.Expense;
        record2.Expense = newExpense;
        record1.Amount += (newExpense - oldExpense);

        myDataContext.SubmitChanges(); // if an exception occurs here, the transaction is aborted

        transaction.Complete(); // commits the transaction

    } catch (Exception ex) {
        // TODO: Exception handling
    }
}