从DbSet中获取单个值，并将该值与另一个DbSet进行比较

Question

我们正在与一个提供数据的外部系统进行集成。该系统包含有关客户许可证及其续订日期的信息。每个许可证或称为模型的TPCase可以有多个续订周期TPRenewalCycle。然后客户可以在我们的系统中下订单，许可证得到更新，新的续订周期开始。

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

客户可以不使用此系统即可更新其许可证，因此我们希望不向OrderRow添加可空外键来增加TPRenewalCycle。

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

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

Case（来自外部系统的模型）：

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;
}

- Ogglas

Answer 1

比我最初想象的要容易，我猜我得写下这个问题才能看到它。

考虑到我们在OrderRow中有一个对TPRenewalCycle的外键，我只需将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);
  }

}

- Ogglas