Simplifying my Linq query and union

Question

I have following Linq queries where I retrieve data from two different tables with same filter and join; and finally union the result set.

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

I need to perform the same with another 8 tables and finally union the result set of them all. All my tables have the same schema. I'm hoping to simplify this with a delegate or method. Please advise.

Also please note that I'm using EF 5 with IDBSet Filtering as per applying global filters article

I think following method should be it but am not sure how I should be calling it.

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

I tried following but there is syntax error :

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

What is collection type of OMS_System_Forecast and OMS_Planned_Receipts? Are these collection of same type? — loopedcode, Sep 19 '14 at 16:44
Did you check out this answer? http://stackoverflow.com/a/23178704/1798889 Seems to be real close to what you are looking for. — CharlesNRice, Sep 19 '14 at 16:49
Hi @loopedcode; Yes they are collection of same type but different tables. — Randeep Singh, Sep 19 '14 at 16:59
Maybe you could use implicit operators http://msdn.microsoft.com/en-us/library/z5z9kes2.aspx to convert from one type to the other. — yosbel, Sep 19 '14 at 17:25

score 1 · Answer 1 · answered Sep 19 '14 at 17:53

1

I'm not sure if it is ok, but as all of your entity classes have the same properties, we can create an interface and make the entity classes implement it using partial classes. Having this interface we could create a method that extract data from an IQueryable.

Something like this.

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

Thus, you could call this method like

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

answered Sep 19 '14 at 17:53

Viktor Arsanov

1,285
1
9
17

Can you advise on how to fix this error : Argument 1: cannot convert from 'System.Data.Entity.IDbSet' to 'System.Linq.IQueryable – Randeep Singh Sep 22 '14 at 08:46
Didn't you forget to write something like `public partial class OMS_Planned_Receipts : IEntity`? – Viktor Arsanov Sep 22 '14 at 08:58
And how do you access your table? I use `dbContext.Set()` which returns an item of DbSet type that implements both IDbSet and IQueryable. – Viktor Arsanov Sep 22 '14 at 09:01
And you seem to access the table through method or property that returns IDbSet. – Viktor Arsanov Sep 22 '14 at 09:02
I'm accessing it through a FilteredDBSet class which handles my multi tenancy. 'public class EFDbContext : DbContext, IDataContext { public IDbSet OMS_Planned_Receipts { get; set; } public IDbSet OMS_System_Forecast { get; set; } public IDbSet OMS_Sales_History { get; set; } public IDbSet Periods { get; set; }' – Randeep Singh Sep 22 '14 at 09:20
Periods = new FilteredDbSet(this, d => d.TenantID == tenantID); OMS_Sales_History = new FilteredDbSet(this, d => d.TenantID == tenantID); OMS_System_Forecast = new FilteredDbSet(this, d => d.TenantID == tenantID); OMS_Planned_Receipts = new FilteredDbSet(this, d => d.TenantID == tenantID); – Randeep Singh Sep 22 '14 at 09:23
Replace IDbSet with DbSet in your properties return rype – Viktor Arsanov Sep 22 '14 at 09:40
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/61663/discussion-between-randeep-singh-and-viktor-arsanov). – Randeep Singh Sep 22 '14 at 09:45

score 0 · Accepted Answer · answered Sep 19 '14 at 17:13

Following example should work for you.

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

This is using LINQ to objects, but the OP's code is an EF question. You're also assuming that all of the tables are of the same type; I see no indication that that's the case. — Servy, Sep 19 '14 at 17:15
Also, you're not properly unioning the results together as you're throwing away the result. — Servy, Sep 19 '14 at 17:16
@loopedcode , based on your example, I have created a method. But am not sure how to call it. It would be great if you can guide there. — Randeep Singh, Sep 22 '14 at 14:45

Simplifying my Linq query and union

2 Answers2