EF Core Code First中FirstOrDefault查询存在元素返回Null及重复标签插入拦截失效问题解决

Let's break down why your duplicate check isn't working and fix it step by step.

Root Cause of the Problem #

Looking at your query logic for existing tags:

var tag1 = _context.FirstTags.Where(x => (string.IsNullOrEmpty(postParam.Post.FirstTag.tagName) || x.tagName == postParam.Post.FirstTag.tagName)) 
    .FirstOrDefault(); 

The issue here is two-fold:

1. The string.IsNullOrEmpty(...) condition is unnecessary for your use case (you're passing a valid tag name in your request) and could lead to unexpected results if the tag name is accidentally empty.
2. If your database uses a case-sensitive collation, a query for "firstTag1" won't match a stored tag named "FirstTag1" (or any case variation). Even if case isn't the issue, this redundant condition might be causing EF Core to generate SQL that doesn't properly target the tag you're looking for.

Additionally, when creating a new tag, you're hardcoding values like "tag1 test" instead of using the tag name/description from your request—this is a bug that would create incorrect tags even when the check works.

Corrected Code #

Here's the revised version of your CreatePost method with fixes for the duplicate check and tag creation:

[HttpPost] 
public async Task<ActionResult<Post>> CreatePost([FromBody] NewPostParam postParam) 
{ 
    // Validate input first (optional but recommended)
    if (string.IsNullOrWhiteSpace(postParam.Post.FirstTag?.tagName) || 
        string.IsNullOrWhiteSpace(postParam.Post.SecondTag?.tagName))
    {
        return BadRequest("Tag names cannot be empty.");
    }

    // Look for existing FirstTag with matching name (case-insensitive query)
    var tag1 = await _context.FirstTags
        .FirstOrDefaultAsync(x => x.tagName.Equals(postParam.Post.FirstTag.tagName, StringComparison.OrdinalIgnoreCase)); 

    // Look for existing SecondTag with matching name (case-insensitive query)
    var tag2 = await _context.SecondTags
        .FirstOrDefaultAsync(x => x.tagName.Equals(postParam.Post.SecondTag.tagName, StringComparison.OrdinalIgnoreCase)); 

    // Create new FirstTag only if it doesn't exist, using request values
    if (tag1 == null) { 
        tag1 = new FirstTag { 
            tagName = postParam.Post.FirstTag.tagName, 
            tagDescription = postParam.Post.FirstTag.tagDescription // Use request's description
        }; 
        _context.FirstTags.Add(tag1); 
    } 

    // Create new SecondTag only if it doesn't exist, using request values
    if (tag2 == null) { 
        tag2 = new SecondTag { 
            tagName = postParam.Post.SecondTag.tagName, 
            tagDescription = postParam.Post.SecondTag.tagDescription // Use request's description
        }; 
        _context.SecondTags.Add(tag2); 
    } 

    var newPost = new Post { 
        Title = postParam.Post.Title, 
        Description = postParam.Post.Description, 
        FirstTag = tag1,
        SecondTag = tag2
    }; 
    _context.Posts.Add(newPost); 
    await _context.SaveChangesAsync(); // Don't forget to save all changes!

    return CreatedAtAction(nameof(GetPost), new { id = newPost.Id }, newPost);
}

Key Improvements #

• Removed redundant string.IsNullOrEmpty check: We now explicitly look for a tag with the exact name from the request (with case-insensitive matching to cover more scenarios).
• Used asynchronous query methods: FirstOrDefaultAsync instead of FirstOrDefault to keep the method properly async and efficient.
• Fixed tag creation: Now uses the tag name and description from your request instead of hardcoded values.
• Added input validation: Ensures tag names aren't empty before proceeding, preventing invalid data from being processed.
• Explicit SaveChangesAsync: Makes sure all changes (new tags + new post) are saved to the database in one transaction.

Extra: Database-Level Protection #

To add an extra layer of safety against duplicates (even if your code has bugs), add a unique constraint to the tagName column in both FirstTag and SecondTag tables.

You can do this via EF Core Fluent API in your DbContext:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<FirstTag>()
        .HasIndex(t => t.tagName)
        .IsUnique();

    modelBuilder.Entity<SecondTag>()
        .HasIndex(t => t.tagName)
        .IsUnique();
}

After adding this, run a new migration and update your database. Now, if any duplicate tag name is accidentally inserted, the database will throw an error, preventing inconsistent data.

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