C# MVC5+EF6环境下，插入含双外键的新父/子行方案咨询

Hey Ivan, let's tackle this problem step by step. You're working with MVC5, EF6, and SQL Server, needing to insert parent/child rows (including those with composite foreign keys in TD_Cell) only if they don't already exist. The core idea is to validate the existence of each dependent entity first, use transactions to guarantee data consistency, and leverage EF6's relationship mapping to handle foreign keys correctly.

First, make sure your DbContext properly maps the relationships between your entities—especially the composite key/foreign key for TD_Cell. Here's how to set it up:

public class YourDbContext : DbContext
{
    public DbSet<TD_Table> Tables { get; set; }
    public DbSet<TD_Column> Columns { get; set; }
    public DbSet<TD_Row> Rows { get; set; }
    public DbSet<TD_Cell> Cells { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // Configure composite primary key for TD_Cell (RowId + ColumnId)
        modelBuilder.Entity<TD_Cell>()
            .HasKey(c => new { c.RowId, c.ColumnId });

        // Map TD_Cell's foreign keys to TD_Row and TD_Column
        modelBuilder.Entity<TD_Cell>()
            .HasRequired(c => c.Row)
            .WithMany(r => r.Cells)
            .HasForeignKey(c => c.RowId);

        modelBuilder.Entity<TD_Cell>()
            .HasRequired(c => c.Column)
            .WithMany(col => col.Cells)
            .HasForeignKey(c => c.ColumnId);

        // Map TD_Table's one-to-many relationships with TD_Row and TD_Column
        modelBuilder.Entity<TD_Table>()
            .HasMany(t => t.Rows)
            .WithRequired(r => r.Table)
            .HasForeignKey(r => r.TableId);

        modelBuilder.Entity<TD_Table>()
            .HasMany(t => t.Columns)
            .WithRequired(col => col.Table)
            .HasForeignKey(col => col.TableId);
    }
}

Next, create a method that checks for existing entities before inserting, wrapped in a transaction to ensure all operations succeed or fail together. This prevents partial data inserts that would break referential integrity.

public void AddOrUpdateCell(string tableName, string rowName, string columnName, string cellValue)
{
    using (var context = new YourDbContext())
    {
        using (var transaction = context.Database.BeginTransaction())
        {
            try
            {
                // 1. Check if TD_Table exists; create if not
                var targetTable = context.Tables.FirstOrDefault(t => t.Name == tableName);
                if (targetTable == null)
                {
                    targetTable = new TD_Table { Name = tableName };
                    context.Tables.Add(targetTable);
                    context.SaveChanges(); // Save to get auto-generated TableId
                }

                // 2. Check if TD_Row exists for this table; create if not
                var targetRow = context.Rows.FirstOrDefault(r => r.TableId == targetTable.Id && r.Name == rowName);
                if (targetRow == null)
                {
                    targetRow = new TD_Row { TableId = targetTable.Id, Name = rowName };
                    context.Rows.Add(targetRow);
                    context.SaveChanges(); // Save to get auto-generated RowId
                }

                // 3. Check if TD_Column exists for this table; create if not
                var targetColumn = context.Columns.FirstOrDefault(col => col.TableId == targetTable.Id && col.Name == columnName);
                if (targetColumn == null)
                {
                    targetColumn = new TD_Column { TableId = targetTable.Id, Name = columnName };
                    context.Columns.Add(targetColumn);
                    context.SaveChanges(); // Save to get auto-generated ColumnId
                }

                // 4. Check if TD_Cell exists for this Row+Column; create or update if needed
                var targetCell = context.Cells.FirstOrDefault(c => c.RowId == targetRow.Id && c.ColumnId == targetColumn.Id);
                if (targetCell == null)
                {
                    targetCell = new TD_Cell
                    {
                        RowId = targetRow.Id,
                        ColumnId = targetColumn.Id,
                        Value = cellValue
                    };
                    context.Cells.Add(targetCell);
                }
                else
                {
                    // Optional: Update the cell value if it already exists
                    targetCell.Value = cellValue;
                }
                context.SaveChanges();

                // Commit all changes if everything succeeded
                transaction.Commit();
            }
            catch (Exception ex)
            {
                // Rollback on any failure to avoid inconsistent data
                transaction.Rollback();
                // Log the exception here (use your preferred logging framework)
                throw new InvalidOperationException("Failed to add/update cell data", ex);
            }
        }
    }
}

• Transaction Safety: The BeginTransaction() call is critical—without it, if one insert fails after others succeed, you’ll end up with orphaned rows (e.g., a Table exists but no corresponding Row/Column).
• SaveChanges() Timing: We call SaveChanges() after inserting each parent entity to retrieve the auto-generated primary key (like TableId), which is required for the child entities’ foreign keys. If you want to minimize round-trips, you could use EF’s change tracking to delay saves, but this approach is simpler and easier to debug.
• Concurrency Protection: To handle race conditions (e.g., two requests trying to insert the same Table at the same time), add unique constraints in your SQL Server tables:
  • TD_Tables: Unique constraint on Name
  • TD_Rows: Unique constraint on (TableId, Name)
  • TD_Columns: Unique constraint on (TableId, Name)
    Then catch SqlException with error codes 2601 or 2627 (SQL Server’s unique violation codes) and re-query the entity, since it may have been created by another request.
• Performance: Add indexes to the columns you’re querying (e.g., TD_Tables.Name, TD_Rows.TableId + TD_Rows.Name) to speed up the existence checks, especially if your tables grow large.

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