c# - 简化我的 Linq 查询和联合

Question

我有以下 Linq 查询，我从两个具有相同过滤器和连接的不同表中检索数据；最后合并结果集。

            var plannedReceiptsResult = db.OMS_Planned_Receipts.Where(p => p.Product == product && p.PeriodID >= StartPeriod && p.PeriodID <= EndPeriod)
            .Join(db.Periods, c => c.PeriodID, o => o.PeriodID, (c, o) => new { c, o })
          .Select(b => new PivotViewModel
          {
              Product = b.c.Product,
              PeriodID = b.c.PeriodID,
              SiteID = b.c.SiteID,
              Value = b.c.Value,
              Activity = "Planned Receipts",
              PeriodStart = b.o.Period_Start,
              PeriodEnd = b.o.Period_End,
              PeriodDescription = b.o.Display
          });

        var systemForecastResult = db.OMS_System_Forecast.Where(p => p.Product == product && p.PeriodID >= StartPeriod && p.PeriodID <= EndPeriod)
               .Join(db.Periods, c => c.PeriodID, o => o.PeriodID, (c, o) => new { c, o })
          .Select(b => new PivotViewModel
          {
              Product = b.c.Product,
              PeriodID = b.c.PeriodID,
              SiteID = b.c.SiteID,
              Value = b.c.Value,
              Activity = "System Forecast",
              PeriodStart = b.o.Period_Start,
              PeriodEnd = b.o.Period_End,
              PeriodDescription = b.o.Display
          });

        var activityResult = plannedReceiptsResult.Union(systemForecastResult);

我需要对另外 8 个表执行相同的操作，最后合并它们的结果集。我所有的表都具有相同的架构。我希望通过委托(delegate)或方法来简化它。请指教。

另请注意，我根据 applying global filters article 将 EF 5 与 IDBSet 过滤一起使用

---

我认为应该是下面的方法，但不确定我应该如何调用它。

    public interface IEntity
{
    Int16 TenantID { get; set; }
    string Product { get; set; }
    string SiteID { get; set; }
    int PeriodID { get; set; }
    double? Value { get; set; }
}

        public static void UnionActivity<T>(IDbSet<T> source, IQueryable<DAL.Period> jointSource,
    string product, string activity, int StartPeriod, double EndPeriod, ref List<PivotViewModel> unionSet) where T : class, IEntity
    {

        unionSet = unionSet.Union(source.Where(p => p.Product == product && p.PeriodID >= StartPeriod && p.PeriodID <= EndPeriod)
           .Join(jointSource, c => c.PeriodID, o => o.PeriodID, (c, o) => new { c, o })
           .Select(b => new PivotViewModel
           {
               Product = b.c.Product,
               PeriodID = b.c.PeriodID,
               SiteID = b.c.SiteID,
               Value = b.c.Value,
               Activity = activity,
               PeriodStart = b.o.Period_Start,
               PeriodEnd = b.o.Period_End,
               PeriodDescription = b.o.Display
           })).ToList();
    }

我试过以下但有语法错误:

        List<PivotViewModel> activityResult1 = new List<PivotViewModel>();
        ViewerModel.UnionActivity<System.Data.Entity.IDbSet<DAL.OMS_Planned_Receipts)>(db.OMS_Planned_Receipts, db.Periods, product, "Planned Receipts", StartPeriod, iEndPeriod, ref activityResult1);

Answer 1

我不确定是否可以，但是由于您所有的实体类都具有相同的属性，我们可以创建一个接口(interface)并让实体类使用分部类来实现它。有了这个接口(interface)，我们可以创建一个从 IQueryable 中提取数据的方法。

像这样。

public interface IEntity
{
    string Product { get; set; }
    int PeriodID { get; set; }
    object SiteID { get; set; }
    object Value { get; set; }
}

public partial class OMS_Planned_Receipt : IEntity // Don't know the exact name of your entity class
{
}

public partial class OMS_System_Forecast : IEntity 
{
}

private static IQueryable<PivotViewModel> SelectObjects(IQueryable<IEntity> source,IQueryable<PeriodEntity> jointSource, string product, int StartPeriod, int EndPeriod)
    {
       return source.Where(p => p.Product == product && p.PeriodID >= StartPeriod && p.PeriodID <= EndPeriod)
          .Join(jointSource, c => c.PeriodID, o => o.PeriodID, (c, o) => new { c, o })
          .Select(b => new PivotViewModel
         {
             Product = b.c.Product,
             PeriodID = b.c.PeriodID,
             SiteID = b.c.SiteID,
             Value = b.c.Value,
             Activity = "System Forecast",
             PeriodStart = b.o.Period_Start,
             PeriodEnd = b.o.Period_End,
             PeriodDescription = b.o.Display
         });
    }

因此，您可以像这样调用此方法

var receipts = SelectObjects(db.OMS_Planned_Receipts, db.Periods, product, StartPeriod, EndPeriod);
var forecasts = SelectObjects(db.OMS_System_Forecast, db.Periods, product, StartPeriod, EndPeriod);