数据库操作预期影响1行实际0行，双表插入异常求助

Hey there, let’s tackle that frustrating "Database operation expected to affect 1 row(s) but actually affected 0 row(s)" error you’re hitting when inserting records into two tables. I’ve run into similar issues before, so here are some actionable checks and fixes to try:

First off, make sure both your Test and Xyz entities have every required database column filled in. This error often pops up when you’re missing a non-nullable field the database expects:

• Verify primary keys: If you’re using auto-incrementing IDs, ensure your entity marks the key with attributes like [Key] and [DatabaseGenerated(DatabaseGeneratedOption.Identity)] (for EF Core) or your ORM’s equivalent. If the key isn’t auto-generated, you need to set it manually before inserting.
• Don’t overlook foreign keys: If either entity references another table, make sure the referenced record exists and you’ve set the foreign key value correctly.

Your attempt to use a unit of work is smart, but it looks like your code snippet cuts off—one common mistake here is forgetting to commit the transaction. Here’s a cleaned-up, complete version to try:

try 
{
    // First, make sure you're populating all required fields for both entities
    var test = new Test 
    {
        // Fill in all non-nullable properties here
    };
    var xyz = new Xyz 
    {
        // Fill in all non-nullable properties here
    };

    using (var uow = _unitOfWorkManager.Begin(TransactionScopeOption.RequiresNew))
    {
        var tenantId = _unitOfWorkManager.Current.GetTenantId();
        // Use the tenant ID scope properly (ensure it's disposed correctly)
        using (_unitOfWorkManager.Current.SetTenantId(tenantId))
        {
            await _testRepository.InsertAsync(test);
            await _xyzRepository.InsertAsync(xyz);
            
            // Critical: Commit the transaction! Without this, all changes roll back
            await uow.CompleteAsync();
        }
    }
}
catch (Exception ex)
{
    // Log the FULL exception (including inner exceptions) here—this is key to debugging
    Console.WriteLine(ex.ToString());
    throw;
}

If you skip CompleteAsync() (or Commit() in older frameworks), the transaction rolls back silently, resulting in 0 rows inserted.

Since you’re working with tenant IDs, confirm that the tenantId you’re using is valid:

• Make sure it’s not null unless your system supports un-tenanted operations.
• Check that the tenant exists in your Tenants table—some multi-tenant frameworks (like ABP) filter operations by tenant, so an invalid ID can cause inserts to fail without a clear error.

Turn on database logging to see the actual INSERT queries being sent to the database. This will show you if the query is missing fields, has invalid values, or is being filtered out.
For EF Core, you can add logging in your service setup:

services.AddDbContext<YourDbContext>(options =>
    options.UseSqlServer(yourConnectionString)
           .LogTo(Console.WriteLine, LogLevel.Information));

Look at the generated SQL—if it’s missing a required column, that’s your culprit.

Ensure your _testRepository and _xyzRepository are properly implementing inserts:

• If they’re custom repositories, confirm they’re calling DbSet.Add() or the equivalent, and that changes are being tracked by the database context.
• Make sure the async insert methods are properly awaited (no missing await keywords that could cause the operation to not complete).

Sometimes the problem is on the database side, not your code:

• Check for foreign key violations: Are you trying to insert a record that references a non-existent entity?
• Verify unique indexes: Could you be hitting a duplicate value in a unique column?
• Check triggers: Do any database triggers exist that might be rolling back the insert silently?

Finally, always log the full exception stack trace—inner exceptions usually have the specific detail about why the insert failed, even if the top-level error is vague.

内容的提问来源于stack exchange，提问作者Vivek Nuna