我有以下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/