使用Entity Framework查询数据库异常：查询Reviews表重复返回同一条数据

Hey there! Let's break down why you're getting duplicate first reviews instead of the three distinct entries from your table—it's a common pitfall with data mapping, so we'll sort this out quickly.

Common Causes & Fixes #

1. Incorrect Primary Key Configuration on Your Review Entity

Most ORMs (like Entity Framework, NHibernate) use the primary key to identify unique entities. If your Review class marks Id as the sole primary key, but your table has multiple rows with the same Id (all 1 here), the ORM will treat all three rows as the same entity. It'll load the first one, then reuse that instance for the other two rows.

Example of the wrong configuration (EF):

public class Review
{
    [Key] // This tells EF Id is the unique primary key
    public int Id { get; set; }
    public string Name { get; set; }
    public string Summary { get; set; }
    public double Rating { get; set; }
}

Fixes:

• Add a true unique primary key to your table: Add an auto-incrementing ReviewId column (e.g., ReviewId INT IDENTITY(1,1) PRIMARY KEY in SQL Server) and update your entity to use this as the primary key.
• Use a composite primary key: If you can't modify the table, configure your ORM to use a combination of columns that are unique (like Id + Name). For EF, use Fluent API:

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
      modelBuilder.Entity<Review>()
          .HasKey(r => new { r.Id, r.Name }); // Combines Id and Name as the unique key
  }
  

2. Reusing the Same Review Instance in Manual Data Reading

If you're using SqlDataReader or similar to fetch data manually, you might be creating only one Review object and updating its properties in the loop—meaning you're adding the same object to the list three times.

Example of the mistake:

public List<Review> GetReviewsById(int id)
{
    var reviews = new List<Review>();
    var review = new Review(); // Only one instance created

    using (var conn = new SqlConnection("YourConnectionString"))
    {
        conn.Open();
        var cmd = new SqlCommand("SELECT * FROM Reviews WHERE Id = @Id", conn);
        cmd.Parameters.AddWithValue("@Id", id);
        
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                // Updating the same object every time
                review.Id = (int)reader["Id"];
                review.Name = reader["Name"].ToString();
                review.Summary = reader["Summary"].ToString();
                review.Rating = (double)reader["Rating"];
                reviews.Add(review); // Adding the same reference three times
            }
        }
    }
    return reviews;
}

Fix:
Create a new Review instance inside the while loop, so each row maps to a unique object:

while (reader.Read())
{
    var review = new Review(); // New instance per row
    review.Id = (int)reader["Id"];
    review.Name = reader["Name"].ToString();
    review.Summary = reader["Summary"].ToString();
    review.Rating = (double)reader["Rating"];
    reviews.Add(review);
}

3. ORM Caching Interference

Some ORMs (like EF Core) cache tracked entities in the DbContext. If you've already loaded the first Id=1 review earlier in the context's lifecycle, it might reuse that cached instance instead of loading the new rows.

Fix:
Use AsNoTracking() to disable entity tracking for this query, which bypasses the context cache:

public List<Review> GetReviewsById(int id)
{
    using (var context = new YourDbContext())
    {
        return context.Reviews
            .Where(r => r.Id == id)
            .AsNoTracking() // Disables caching for this query
            .ToList();
    }
}

Start by checking your entity's primary key setup—it's the most likely culprit here. If that's not it, move on to verifying your data retrieval code for instance reuse.

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