使用Linq删除多层关联子表的Tbl_Category记录及查询编写求助

Hey there! Let's break down your two LINQ delete requirements step by step, with practical code examples tailored to your scenarios:

First off, deleting a category with nested child tables linked all the way to Tbl_File depends on whether your database has cascade delete enabled on the foreign keys. Here are the two scenarios:

Scenario 1: Database has cascade delete configured #

If your foreign key relationships (from Tbl_Category → intermediate tables → Tbl_File) are set up with cascade delete, LINQ will handle deleting all related records automatically when you remove the parent category. Here's how to do it:

// Assume your DbContext instance is named _dbContext
// Replace 'targetCategoryId' with your actual category identifier (e.g., ID from request)
var categoryToDelete = await _dbContext.Tbl_Category
    .FirstOrDefaultAsync(c => c.CategoryId == targetCategoryId);

if (categoryToDelete != null)
{
    _dbContext.Tbl_Category.Remove(categoryToDelete);
    await _dbContext.SaveChangesAsync();
    // All related child records (including Tbl_File) will be deleted automatically via cascade
}

Scenario 2: No cascade delete (manual cleanup required) #

If cascade delete isn't enabled, you need to delete records starting from the deepest child table (Tbl_File) up to the parent Tbl_Category to avoid foreign key constraint errors. Adjust the intermediate tables to match your actual schema:

using var transaction = await _dbContext.Database.BeginTransactionAsync(); // Use transaction for atomicity

try
{
    // 1. Delete related Tbl_File records first (deepest child)
    var relatedFiles = await _dbContext.Tbl_File
        // Replace the path below with your actual navigation property chain to Tbl_Category
        .Where(f => f.Asset.SubCategory.CategoryId == targetCategoryId)
        .ToListAsync();
    _dbContext.Tbl_File.RemoveRange(relatedFiles);

    // 2. Delete intermediate child tables (e.g., Tbl_SubCategory, Tbl_Asset - adjust to your schema)
    var relatedSubCategories = await _dbContext.Tbl_SubCategory
        .Where(sc => sc.CategoryId == targetCategoryId)
        .ToListAsync();
    _dbContext.Tbl_SubCategory.RemoveRange(relatedSubCategories);

    // 3. Finally delete the target Tbl_Category record
    var categoryToDelete = await _dbContext.Tbl_Category
        .FirstOrDefaultAsync(c => c.CategoryId == targetCategoryId);
    if (categoryToDelete != null)
    {
        _dbContext.Tbl_Category.Remove(categoryToDelete);
    }

    // Commit all changes at once
    await _dbContext.SaveChangesAsync();
    await transaction.CommitAsync();
}
catch (Exception ex)
{
    // Rollback if any step fails
    await transaction.RollbackAsync();
    // Log the exception or handle error as needed
}

Since Tbl_user uses username as its primary key, you'll first fetch the username from the user's session, then find and delete the matching record. Below is an example for ASP.NET Core (adjust session retrieval for your framework if needed):

// Get username from session (ASP.NET Core example)
var currentUsername = HttpContext.Session.GetString("Username");

if (!string.IsNullOrEmpty(currentUsername))
{
    // Find the user by primary key (username)
    var userToDelete = await _dbContext.Tbl_user
        .FirstOrDefaultAsync(u => u.username == currentUsername);

    if (userToDelete != null)
    {
        _dbContext.Tbl_user.Remove(userToDelete);
        await _dbContext.SaveChangesAsync();
    }
    else
    {
        // Handle case where user doesn't exist (e.g., log warning, return error response)
        Console.WriteLine($"User with username '{currentUsername}' not found.");
    }
}
else
{
    // Handle case where session has no username (user not logged in)
    Console.WriteLine("No username found in session. User must be logged in to perform this action.");
}

Key Notes for Both Scenarios: #

• Always add null checks before deleting to avoid NullReferenceException.
• Use asynchronous methods (Async suffix + await) for better performance in web applications.
• For manual cascade deletes, wrap operations in a transaction to ensure atomicity (all changes succeed or none do).
• Test delete operations in a non-production environment first to avoid accidental data loss!

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