🚀 𝗘𝗙 𝗖𝗼𝗿𝗲 𝗦𝘁𝗿𝘂𝗴𝗴𝗹𝗲𝘀 𝘄𝗶𝘁𝗵 𝗧𝗿𝗶𝗴𝗴𝗲𝗿𝘀? 𝗦𝘄𝗶𝘁𝗰𝗵 𝘁𝗼 𝗦𝘁𝗼𝗿𝗲𝗱 𝗣𝗿𝗼𝗰𝗲𝗱𝘂𝗿𝗲𝘀!

While working with 𝗘𝗙 𝗖𝗼𝗿𝗲, I faced performance issues when inserting, updating, and deleting records in the 𝗘𝘅𝗽𝗲𝗻𝘀𝗲𝘀 𝘁𝗮𝗯𝗹𝗲, which had 𝗜𝗻𝘀𝗲𝗿𝘁, 𝗨𝗽𝗱𝗮𝘁𝗲, 𝗮𝗻𝗱 𝗗𝗲𝗹𝗲𝘁𝗲 𝘁𝗿𝗶𝗴𝗴𝗲𝗿𝘀.

✅ EF Core had trouble handling tables with triggers, causing performace issues.

✅ Debugging was difficult since triggers execute implicitly in the background.

💡 𝗦𝗼𝗹𝘂𝘁𝗶𝗼𝗻: 𝗨𝘀𝗲 𝗦𝘁𝗼𝗿𝗲𝗱 𝗣𝗿𝗼𝗰𝗲𝗱𝘂𝗿𝗲𝘀 𝗜𝗻𝘀𝘁𝗲𝗮𝗱 𝗼𝗳 𝗧𝗿𝗶𝗴𝗴𝗲𝗿𝘀

🔹 Created a 𝘀𝘁𝗼𝗿𝗲𝗱 𝗽𝗿𝗼𝗰𝗲𝗱𝘂𝗿𝗲 (𝗦𝗣) to update the Budget table when an Expense record is inserted, updated, or deleted.

🔹 Used 𝗘𝘅𝗲𝗰𝘂𝘁𝗲𝗦𝗾𝗹𝗥𝗮𝘄() in EF Core to call the SP manually after saving the Expense record.

🔹 This approach 𝗴𝗮𝘃𝗲 𝗳𝘂𝗹𝗹 𝗰𝗼𝗻𝘁𝗿𝗼𝗹, improved 𝗽𝗲𝗿𝗳𝗼𝗿𝗺𝗮𝗻𝗰𝗲, and made debugging easier.

1️⃣ 𝗖𝗿𝗲𝗮𝘁𝗲 𝗮 𝘀𝘁𝗼𝗿𝗲𝗱 𝗽𝗿𝗼𝗰𝗲𝗱𝘂𝗿𝗲 𝗶𝗻 𝗦𝗤𝗟 𝗦𝗲𝗿𝘃𝗲𝗿

CREATE PROCEDURE UpdateBudget
@ExpenseId INT,
@OperationType NVARCHAR(10)

AS
BEGIN
IF @OperationType = 'INSERT'
-- Increase budget based on the new expense
ELSE IF @OperationType = 'UPDATE'
-- Adjust budget for the updated expense
ELSE IF @OperationType = 'DELETE'
-- Revert budget for deleted expense
END

2️⃣ 𝗖𝗮𝗹𝗹 𝘁𝗵𝗲 𝘀𝘁𝗼𝗿𝗲𝗱 𝗽𝗿𝗼𝗰𝗲𝗱𝘂𝗿𝗲 𝗶𝗻 𝗘𝗙 𝗖𝗼𝗿𝗲

await _context.Database.ExecuteSqlRawAsync(
"EXEC UpdateBudget @p0, @p1", expenseId, "INSERT");

3️⃣ 𝗨𝘀𝗲 𝗶𝘁 𝗶𝗻 𝘆𝗼𝘂𝗿 𝘀𝗲𝗿𝘃𝗶𝗰𝗲/𝗿𝗲𝗽𝗼𝘀𝗶𝘁𝗼𝗿𝘆

public async Task AddExpense(Expense expense)
{
_context.Expenses.Add(expense);
await _context.SaveChangesAsync();
await UpdateBudgetAsync(expense.Id, "INSERT");
}

✅ Stored procedures 𝗲𝗹𝗶𝗺𝗶𝗻𝗮𝘁𝗲 𝗘𝗙 𝗖𝗼𝗿𝗲 𝘁𝗿𝗶𝗴𝗴𝗲𝗿-𝗿𝗲𝗹𝗮𝘁𝗲𝗱 performance 𝗶𝘀𝘀𝘂𝗲𝘀.

✅ They 𝗶𝗺𝗽𝗿𝗼𝘃𝗲 𝗽𝗲𝗿𝗳𝗼𝗿𝗺𝗮𝗻𝗰𝗲 and provide 𝗯𝗲𝘁𝘁𝗲𝗿 𝗱𝗲𝗯𝘂𝗴𝗴𝗶𝗻𝗴.

✅ Developers have 𝗳𝘂𝗹𝗹 𝗰𝗼𝗻𝘁𝗿𝗼𝗹 over when and how the budget updates.

💬 𝗛𝗮𝘃𝗲 𝘆𝗼𝘂 𝗳𝗮𝗰𝗲𝗱 𝘀𝗶𝗺𝗶𝗹𝗮𝗿 𝗶𝘀𝘀𝘂𝗲𝘀 𝘄𝗶𝘁𝗵 𝗘𝗙 𝗖𝗼𝗿𝗲 𝗮𝗻𝗱 𝘁𝗿𝗶𝗴𝗴𝗲𝗿𝘀? 𝗛𝗼𝘄 𝗱𝗶𝗱 𝘆𝗼𝘂 𝘀𝗼𝗹𝘃𝗲 𝘁𝗵𝗲𝗺? 𝗟𝗲𝘁’𝘀 𝗱𝗶𝘀𝗰𝘂𝘀𝘀! 👇