我有以下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个表执行相同的操作,最后合并它们全部的结果集。我所有的表都具有相同的架构。我希望使用委托或方法简化此过程。请指教。

还请注意,我正在根据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);

最佳答案

以下示例将为您工作。

public void Load()
{

    List<PivotViewModel> activityResult = new List<PivotViewModel>();
    MyLinq(db.OMS_Planned_Receipts, db.Periods, "System ForeCast", ref activityResult);
    MyLinq(db.OMS_System_Forecast, db.Periods, "System ForeCast", activityResult);

    //activityResult has unionized result
}

private void MyLinq(IEnumerable<MyData> myData, IEnumerable<MyPeriod> periods, string activity, ref IEnumerable<PivotViewModel> unionSet)
{
    unionSet = unionSet.Union
    (
        myData.Where(p => p.Product == product && p.PeriodID >= StartPeriod && p.PeriodID <= EndPeriod)
        .Join(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 = activity,
            PeriodStart = b.o.Period_Start,
            PeriodEnd = b.o.Period_End,
            PeriodDescription = b.o.Display
        })
    ).ToList();
}

关于c# - 简化我的Linq查询和联合,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/25938536/

10-13 07:51