Entity Framework返回日期异常问题求助

在API中使用Entity Framework时，按日期排序对象列表出现异常。每日爬取网页保存产品数据到SQL数据库后，数据库中数据正确（两条记录日期分别为2023-07-18和2023-07-19），但EF查询返回两条日期均为2023-07-18的数据。

数据库表结构 #

CREATE TABLE [dbo].[ScrappedProducts] (
    [Domain]          VARCHAR (50)    NOT NULL,
    [ProductId]       VARCHAR (50)    NOT NULL,
    [ScrappedDate]    DATE            NOT NULL,
    [ProductUrl]      VARCHAR (256)   NOT NULL,
    [ProductName]     VARCHAR (200)   NOT NULL,
    [ProductBrand]    VARCHAR (200)   NOT NULL,
    [ProductType]     VARCHAR (100)   NOT NULL,
    [ProductAlcool]   DECIMAL (16, 2) NOT NULL,
    [ProductQuantity] INT             NOT NULL,
    [ProductContain]  DECIMAL (16, 2) NOT NULL,
    [ProductPrice]    DECIMAL (16, 2) NOT NULL,
    [ProductImageUrl] VARCHAR (256)   NOT NULL,
    PRIMARY KEY CLUSTERED ([ProductId] ASC, [Domain] ASC, [ScrappedDate] ASC)
);

实体类 #

namespace WebScrapperAPI.Components
{
    public class Product
    {
        public string Domain { get; set; }
        public DateTime ScrappedDate { get; set; }
        public string ProductId { get; set; }
        public string ProductUrl { get; set; }
        public string ProductName { get; set; }
        public string ProductBrand { get; set; }
        public string ProductType { get; set; }
        public decimal ProductAlcool { get; set; }
        public int ProductQuantity { get; set; }
        public decimal ProductContain { get; set; }
        public decimal ProductPrice { get; set; }
        public string ProductImageUrl { get; set; }
    }
}

DbContext类 #

using Microsoft.EntityFrameworkCore;
using WebScrapperAPI.Components;

namespace WebScrapperAPI.Data
{
    public class ScrapperDBContext : DbContext
    {
        public DbSet<Product> ScrappedProducts { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            string connectionString = "Server=tcp:xxx,xxx;Initial Catalog=xxx;Persist Security Info=False;User ID=xxx;Password=xxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";

            var loggerFactory = LoggerFactory.Create(builder =>
            {
                builder.AddConsole(); // 添加控制台日志
                builder.AddFilter(DbLoggerCategory.Database.Command.Name, LogLevel.Information); // 仅过滤SQL日志
            });
            optionsBuilder.UseSqlServer(connectionString)
                .UseLoggerFactory(loggerFactory)
                .LogTo(Log, new[] { DbLoggerCategory.Database.Command.Name }, LogLevel.Information);
        }
        private void Log(string t)
        {
            File.WriteAllText("test.txt", t);
        }
    }
}

数据库中的原始数据 #

Auchan  0067c78b-7b53-4e2a-b655-d9c071a5ca87    2023-07-18  https://www.auchan.fr/valauria-muscat-vin-doux-naturel-de-rivesaltes-aop-15-5/pr-C1211479   Muscat vin doux naturel de Rivesaltes AOP   VALAURIA    Liquor  15.50   1   75.00   6.65    /static/images/pixel.png

Auchan  0067c78b-7b53-4e2a-b655-d9c071a5ca87    2023-07-19  https://www.auchan.fr/valauria-muscat-vin-doux-naturel-de-rivesaltes-aop-15-5/pr-C1211479   Muscat vin doux naturel de Rivesaltes AOP   VALAURIA    Liquor  15.50   1   75.00   6.65    /static/images/pixel.png

Entity Framework返回的数据 #

{
    "domain": "Auchan",
    "scrappedDate": "2023-07-18T00:00:00",
    "productId": "0067c78b-7b53-4e2a-b655-d9c071a5ca87",
    "productUrl": "https://www.auchan.fr/valauria-muscat-vin-doux-naturel-de-rivesaltes-aop-15-5/pr-C1211479",
    "productName": "Muscat vin doux naturel de Rivesaltes AOP",
    "productBrand": "VALAURIA",
    "productType": "Liquor",
    "productAlcool": 15.5,
    "productQuantity": 1,
    "productContain": 75,
    "productPrice": 6.65,
    "productImageUrl": "/static/images/pixel.png"
  },
  {
    "domain": "Auchan",
    "scrappedDate": "2023-07-18T00:00:00",
    "productId": "0067c78b-7b53-4e2a-b655-d9c071a5ca87",
    "productUrl": "https://www.auchan.fr/valauria-muscat-vin-doux-naturel-de-rivesaltes-aop-15-5/pr-C1211479",
    "productName": "Muscat vin doux naturel de Rivesaltes AOP",
    "productBrand": "VALAURIA",
    "productType": "Liquor",
    "productAlcool": 15.5,
    "productQuantity": 1,
    "productContain": 75,
    "productPrice": 6.65,
    "productImageUrl": "/static/images/pixel.png"
  }

查询代码 #

public static IEnumerable<Product> GetProducts()
{
    var products = new List<Product>();
    using (var context = new ScrapperDBContext())
    {
        products = context.ScrappedProducts.ToList();
    }
    return products;
}

解决方案 #

问题根源是EF未正确识别数据库的复合主键：数据库中主键为ProductId + Domain + ScrappedDate，但实体类未配置这三个字段作为主键，导致EF无法区分不同记录，重复加载同一条数据。

修复步骤： #

1. 配置复合主键
   • 方式一：使用数据注解在实体类中标记主键

     using System.ComponentModel.DataAnnotations;
     using System.ComponentModel.DataAnnotations.Schema;
     
     namespace WebScrapperAPI.Components
     {
         public class Product
         {
             [Key, Column(Order = 0)]
             public string Domain { get; set; }
             [Key, Column(Order = 1)]
             public string ProductId { get; set; }
             [Key, Column(Order = 2)]
             public DateTime ScrappedDate { get; set; }
             // 其他属性保持不变
         }
     }
     

   • 方式二：在DbContext中通过Fluent API配置

     protected override void OnModelCreating(ModelBuilder modelBuilder)
     {
         modelBuilder.Entity<Product>()
             .HasKey(p => new { p.Domain, p.ProductId, p.ScrappedDate });
     }
     

2. 添加排序（可选）
   查询时按日期排序，确保结果有序：

   products = context.ScrappedProducts.OrderBy(p => p.ScrappedDate).ToList();
   

配置完成后，EF会正确加载数据库中两条不同日期的记录。

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