使用EF从SQL查询关联数据失败，需排查及验证分组求和

问题概述 #

我正在编写数据筛选逻辑，但遇到无法从SQL数据库获取数据的问题，已排查5小时仍未定位原因。

现有代码 #

var context = _contextFactory.CreateDbContext();

//select records
var dok = await context.Dok
.Where(x => x.Data == DateTime.Today.AddDays(-2) && x.TypDok == 21)
.ToListAsync();

// Define the DokId (column name) range from dok
var dokIdRange = dok.Select(x => x.DokId);

//select records containing dokId
var pozDok = await context.PozDok
              .Where(x => dokIdRange.Contains(x.DokId))
             .ToListAsync();

//group them and count
var result = (
        from pd in pozDok
        group pd by pd.TowId into g
        select new
        {
            TowId = g.Key,
            IloscPlusSum = g.Sum(x => x.IloscPlus)
        }
    ).ToList();

需求说明 #

我的需求是：

• 从Dok表中筛选指定日期的记录，提取DokId集合；
• 筛选PozDok表中包含该DokId的记录；
• 最后按TowId分组，对IloscPlus进行求和（同一TowId的多条记录合并为一条，总和IloscPlus）。

更新信息：实体类定义 #

public class Dok
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public decimal DokId { get; set; }
    public decimal UzId { get; set; }
    public decimal MagId { get; set; }
    public DateTime Data { get; set; }
    public int KolejnyWDniu { get; set; }
    public DateTime DataDod { get; set; }
    public DateTime DataPom { get; set; }
    public string NrDok { get; set; }
    public short TypDok { get; set; }
    public short Aktywny { get; set; }
    public short Opcja1 { get; set; }
    public short Opcja2 { get; set; }
    public short Opcja3 { get; set; }
    public short Opcja4 { get; set; }
    public short CenyZakBrutto { get; set; }
    public short CenySpBrutto { get; set; }
    public short FormaPlat { get; set; }
    public short TerminPlat { get; set; }
    public short PoziomCen { get; set; }
    public decimal RabatProc { get; set; }
    public decimal Netto { get; set; }
    public decimal Podatek { get; set; }
    public decimal NettoUslugi { get; set; }
    public decimal PodatekUslugi { get; set; }
    public decimal NettoDet { get; set; }
    public decimal PodatekDet { get; set; }
    public decimal NettoDetUslugi { get; set; }
    public decimal PodatekDetUslugi { get; set; }
    public decimal NettoMag { get; set; }
    public decimal PodatekMag { get; set; }
    public decimal NettoMagUslugi { get; set; }
    public decimal PodatekMagUslugi { get; set; }
    public decimal Razem { get; set; }
    public decimal DoZaplaty { get; set; }
    public decimal Zaplacono { get; set; }
    public decimal Kwota1 { get; set; }
    public decimal Kwota2 { get; set; }
    public decimal Kwota3 { get; set; }
    public decimal Kwota4 { get; set; }
    public decimal Kwota5 { get; set; }
    public decimal Kwota6 { get; set; }
    public decimal Kwota7 { get; set; }
    public decimal Kwota8 { get; set; }
    public decimal Kwota9 { get; set; }
    public decimal Kwota10 { get; set; }
    public int Param1 { get; set; }
    public int Param2 { get; set; }
    public int Param3 { get; set; }
    public int Param4 { get; set; }
    public short EksportFK { get; set; }
    public DateTime Zmiana { get; set; }
    public int? NrKolejny { get; set; }
    public int? NrKolejnyMag { get; set; }
    public int? Param5 { get; set; }
    public int? Param6 { get; set; }
    public decimal? Kwota11 { get; set; }
    public decimal? Kwota12 { get; set; }
    public int? WalId { get; set; }
    public decimal? Kurs { get; set; }
    public decimal? CentrDokId { get; set; }
    public short? Opcja5 { get; set; }
    public short? Opcja6 { get; set; }
    public short? Opcja7 { get; set; }
    public short? Opcja8 { get; set; }
    public DateTime? ZmianaPkt { get; set; }
    public decimal? ZaplaconoPodatek { get; set; }
    public decimal? ZaplaconoWKasie { get; set; }
}

public class PozDok
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public decimal DokId { get; set; }
    public int Kolejnosc { get; set; }
    public int NrPozycji { get; set; }
    public decimal TowId { get; set; }
    public short TypPoz { get; set; }
    public decimal IloscPlus { get; set; }
    public decimal IloscMinus { get; set; }
    public short PoziomCen { get; set; }
    public short Metoda { get; set; }
    public decimal CenaDomyslna { get; set; }
    public decimal CenaPrzedRab { get; set; }
    public decimal RabatProc { get; set; }
    public decimal CenaPoRab { get; set; }
    public decimal Wartosc { get; set; }
    public decimal CenaDet { get; set; }
    public decimal CenaMag { get; set; }
    public short Stawka { get; set; }
    public short TypTowaru { get; set; }
    public decimal IleWZgrzewce { get; set; }
    public short? StawkaDod { get; set; }
    public decimal? Netto { get; set; }
    public decimal? Podatek { get; set; }
}

无法获取数据的排查方向 #

• 日期匹配问题：DateTime.Today.AddDays(-2)是纯日期（不带时间），但Dok表的Data字段是DateTime类型，可能包含时间部分。直接用==匹配会漏掉带时间的记录，建议改用范围查询：

  var targetDate = DateTime.Today.AddDays(-2);
  .Where(x => x.Data >= targetDate && x.Data < targetDate.AddDays(1))
  

• Dok表筛选条件无数据：单独执行Dok的查询，确认是否存在符合TypDok ==21且日期正确的记录。可通过断点或日志查看dok集合是否为空。
• 实体类与数据库映射问题：检查实体类属性与数据库表字段的类型、名称是否完全匹配，比如TypDok的short类型是否与数据库对应，DokId的decimal类型是否与数据库一致，避免因类型不匹配导致查询无结果。
• PozDok主键配置错误：PozDok作为Dok的子表，主键不应仅设为DokId（子表中同一DokId会有多条记录），建议改为复合主键（如DokId + NrPozycji）或单独的自增主键。错误的主键映射会导致EF Core无法正确读取数据。
• 查询逻辑优化与验证：当前代码先将Dok数据拉到内存再查询PozDok，若Dok集合为空则PozDok查询必然无结果。建议改为数据库端关联查询，提升效率同时减少内存处理问题：

  var targetDate = DateTime.Today.AddDays(-2);
  var result = await context.Dok
      .Where(d => d.Data >= targetDate && d.Data < targetDate.AddDays(1) && d.TypDok ==21)
      .Join(context.PozDok, 
            d => d.DokId, 
            pd => pd.DokId, 
            (d, pd) => pd)
      .GroupBy(pd => pd.TowId)
      .Select(g => new {
          TowId = g.Key,
          IloscPlusSum = g.Sum(x => x.IloscPlus)
      })
      .ToListAsync();
  

• 数据库连接与权限：确认_contextFactory创建的DbContext连接的是目标数据库，应用程序拥有读取Dok和PozDok表的权限。

分组求和逻辑确认 #

你的分组求和逻辑是正确的：从PozDok集合中按TowId分组，对每个分组的IloscPlus字段求和，最终返回每个TowId对应的总和。只要PozDok集合中有有效数据，就能得到符合需求的结果。若改用上述数据库端关联查询，效率会更高，也能避免内存处理可能带来的潜在问题。

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