c# - 从 DbSet 中的集合中获取单个值并将此值与另一个 DbSet 进行比较

Question

我们目前正在集成一个为我们提供数据的外部系统。该系统包含有关客户许可证及其更新时间的信息。每个许可证或作为模型调用的 TPCase 可以有多个更新周期 TPRenewalCycle。然后，客户可以在我们的系统中下订单，然后许可证会续订，新的续订周期开始。

但是，对于拥有大量需要续订的许可证的客户，我们遇到了一些性能问题。这是因为我们需要检查下一个更新周期是否已经订购但尚未处理，其余代码足够快(db.OrderRows.Any(...)

客户可以在不使用该系统的情况下续订他们的许可证，因此我们不希望为 OrderRow 添加可为空的外键到 TPRenewalCycle。

以下是我们当前的模型和代码。有没有办法加快这个过程？我能想到的是直接识别 tpCase.NextRenewalCycle，然后检查 OrderRows 中是否不存在任何值。

如果在 Entity Framework 中很难做到，我们可能愿意直接使用 T-SQL 如果我们不必像现在这样检查它。

案例(来自外部系统的模型):

public class TPCase
{
    [Key, Column(Order = 0)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public string BusinessSystemId { get; set; }

    [Key, Column(Order = 1)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int CaseId { get; set; }

    public bool VisibleToClient { get; set; }

    public virtual ICollection<TPCaseName> CaseNames { get; set; }

    public virtual ICollection<TPRenewalCycle> RenewalCycles { get; set; }

    [NotMapped]
    public TPRenewalCycle NextRenewalCycle { get; set; }
}

public class TPCaseName
{
    [Key, Column(Order = 0)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public string BusinessSystemId { get; set; }

    [Key, Column(Order = 1)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int CaseId { get; set; }

    [Key, Column(Order = 2)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int NameNo { get; set; }

    [Key, Column(Order = 3)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public string NameType { get; set; }

    [ForeignKey("BusinessSystemId,CaseId")]
    public virtual TPCase TPCase { get; set; }

    [ForeignKey("BusinessSystemId,NameNo")]
    public virtual TPName TPName { get; set; }

    public virtual TPNameType TPNameType { get; set; }

    public string ContactName { get; set; }
}

public class TPName
{
    [Key, Column(Order = 0)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public string BusinessSystemId { get; set; }

    [Key, Column(Order = 1)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int NameNo { get; set; }

    public string Name { get; set; }
}

public class TPNameType
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public string NameType { get; set; }
    public string Description { get; set; }
}

public class TPRenewalCycle
{
    [Key, Column(Order = 0)]
    [ForeignKey("TPCase")]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public string BusinessSystemId { get; set; }

    [Key, Column(Order = 1)]
    [ForeignKey("TPCase")]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int CaseId { get; set; }

    public virtual TPCase TPCase { get; set; }

    [Key, Column(Order = 2)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public string Action { get; set; }

    [Key, Column(Order = 3)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Cycle { get; set; }

    public bool? IsOpen { get; set; }

    public virtual ICollection<TPCaseEvent> CaseEvents { get; set; }
}

public class TPCaseEvent
{
    [Key, Column(Order = 0)]
    [ForeignKey("TPRenewalCycle")]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public string BusinessSystemId { get; set; }

    [Key, Column(Order = 1)]
    [ForeignKey("TPRenewalCycle")]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int CaseId { get; set; }

    [Key, Column(Order = 2)]
    [ForeignKey("TPRenewalCycle")]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public string Action { get; set; }

    [Key, Column(Order = 3)]
    [ForeignKey("TPRenewalCycle")]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Cycle { get; set; }

    public virtual TPRenewalCycle TPRenewalCycle { get; set; }

    [Key, Column(Order = 4)]
    [ForeignKey("TPEvent")]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int EventNo { get; set; }

    public virtual TPEvent TPEvent { get; set; }

    public DateTime? DueDate { get; set; }

    public DateTime? EventDate { get; set; }
}

public class TPEvent
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int EventNo { get; set; }

    public string Name { get; set; }

    [Column(TypeName = "nvarchar(MAX)")]
    [MaxLength]
    public string Description { get; set; }
}

订单(我们的模型):

public class Order
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public DateTime Created { get; set; }

    public DateTime Updated { get; set; }

    public virtual ICollection<OrderRow> OrderRows { get; set; }
}

public class OrderRow
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public DateTime Created { get; set; }

    public DateTime Updated { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public string BusinessSystemId { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int CaseId { get; set; }

    [ForeignKey("BusinessSystemId,CaseId")]
    public virtual TPCase TPCase { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public string Action { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Cycle { get; set; }

    [ForeignKey("BusinessSystemId,CaseId,Action,Cycle")]
    public virtual TPRenewalCycle TPRenewalCycle { get; set; }

    public int OrderId { get; set; }

    public virtual Order Order { get; set; }
}

方法:

public IEnumerable<TPCase> GetRenewalCasesForCustomer(IList<int> customerNumberKeys)
{
    var db = new DbContext();

    var cases = db.Cases
    .Where(tpCase =>
        tpCase.VisibleToClient == true &&
        tpCase.CaseNames.Any(
             caseName => customerNumberKeys.Contains(caseName.NameNo) && 
                  caseName.BusinessSystemId == "EN" && 
                  caseName.NameType == "T") &&
        tpCase.RenewalCycles.Any(
            renewalCycle =>
            renewalCycle.IsOpen == true &&
            renewalCycle.Action == "RW" &&
            renewalCycle.CaseEvents.Any(caseEvent =>
                caseEvent.EventNo == -112 &&
                caseEvent.DueDate >= DateTime.Now
            ))
    )
    .Include(tpCase => tpCase.CaseNames.Select(caseName => caseName.TPName))
    .Include(tpCase => tpCase.CaseNames.Select(caseName => caseName.TPNameType))
    .Include(tpCase => tpCase.RenewalCycles.Select(renewalCycle => renewalCycle.CaseEvents))
    .ToList();

    foreach (var tpCase in cases)
    {
        var cycles =
          tpCase.RenewalCycles.Where(
            x => x.IsOpen == true &&
                 x.Action == "RW" &&
                 x.CaseEvents.FirstOrDefault(y => y.EventNo == -112)?.DueDate != null &&
                 x.CaseEvents.Single(y => y.EventNo == -112)?.DueDate >= DateTime.Now)
            .ToList();

        if (cycles.Any())
        {
          var cycle = cycles.OrderBy(
            x => x.CaseEvents.Single(y => y.EventNo == -11)?.DueDate)
            .First();

          tpCase.NextRenewalCycle = cycle;
        }
    }

    for (int i = cases.Count - 1; i >= 0; i--)
    {
      var tpCase = cases[i];

      if (tpCase.NextRenewalCycle == null)
      {
        cases.RemoveAt(i);
        continue;
      }

      //This is the time consuming part where all the requests are made
      var orderExist = db.OrderRows.Any(x =>
        x.BusinessSystemId == tpCase.BusinessSystemId &&
        x.CaseId == tpCase.CaseId &&
        x.Action == tpCase.NextRenewalCycle.Action &&
        x.Cycle == tpCase.NextRenewalCycle.Cycle
      );

      if (orderExist)
      {
        cases.RemoveAt(i);
      }
    }       
    return cases;
}

Answer 1

比我最初想象的要容易，我想我必须写下问题才能看到它。

鉴于我们有一个 FK 到 TPRenewalCycle在 OrderRow我可以添加 public virtual ICollection<OrderRow> OrderRows { get; set; }至 TPRenewalCycle .

在查询中，我可以像其他表一样包含符合我给定条件的订单行。

.Include(tpCase => tpCase.RenewalCycles.Select(renewalCycle => renewalCycle.OrderRows))

现在我有了这个，我可以删除下一个续订周期有任何订单行的情况。因为我们已经有了信息，所以不需要往返数据库，而且速度非常快。

for (int i = cases.Count - 1; i >= 0; i--)
{
  var tpCase = cases[i];

  if (tpCase.NextRenewalCycle == null || tpCase.NextRenewalCycle.OrderRows.Any())
  {
    cases.RemoveAt(i);
  }

}