LINQ Join usage data from two data sets into one

Question

What I have:

Two lists of the following model:

int    SubscriptionId
int    ItemId
double Usage
double EffectiveRate
string ResourceName
string UnitOfMeasure

The first contains usage data of the last month like this:

SubscriptionId  ItemId  Usage  EffectiveRate  ResourceName  UnitOfMesaure
_________________________________________________________________________
1               1       2      2,75           R1            U1
1               2       3      1,50           R2            U2

The seconds contains usage data of the current month like this:

SubscriptionId  ItemId  Usage  EffectiveRate  ResourceName  UnitOfMesaure
_________________________________________________________________________
1               1       5      2,75           R1            U1
1               3       2      1,50           R3            U3

What I want:

This should be merge in a list like this:

SubscriptionId  ItemId  UsageThis  UsageLast  EffRate  ResName  UOM
_________________________________________________________________________
1               1       5          2          2,75     R1       U1
1               2       0          3          1,50     R2       U2
1               3       2          0          1,50     R3       U3

What I have:

//data for both months available
if (resourcesThisMonth.Any() && resourcesLastMonth.Any())
{
    //join both months
    resources = from resourceLastMonth in resourcesLastMonth
                join resourceThisMonth in resourcesThisMonth
                on new { resourceLastMonth.SubscriptionId, resourceLastMonth.ItemId } equals new { resourceThisMonth.SubscriptionId, resourceThisMonth.ItemId }
                select new Resource
                {
                    SubscriptionId = resourceThisMonth.SubscriptionId,
                    ItemId = resourceThisMonth.ItemId,
                    UsageThisMonth = resourceThisMonth.Usage,
                    UsageLastMonth = resourceLastMonth.Usage,
                    EffectiveRate = resourceThisMonth.EffectiveRate,
                    ResourceName = resourceThisMonth.ResourceName,
                    UnitOfMeasure = resourceThisMonth.UnitOfMeasure
                };

    //resources only last month available
    var resourcesOnlyLastMonth = resourcesLastMonth.Where(r => !resourcesThisMonth.Where(s => s.ItemId == r.ItemId && s.SubscriptionId == r.SubscriptionId).Any())
                                 .Select(r => new Resource
                                 {
                                     SubscriptionId = r.SubscriptionId,
                                     ItemId = r.ItemId,
                                     UsageThisMonth = 0.0,
                                     UsageLastMonth = r.Units,
                                     EffectiveRate = r.EffectiveRate,
                                     ResourceName = r.ResourceName,
                                     UnitOfMeasure = r.UnitOfMeasure
                                 });

    //resources only this month available
    var resourcesOnlyThisMonth = resourcesThisMonth.Where(r => !resourcesLastMonth.Where(s => s.ItemId == r.ItemId && s.SubscriptionId == r.SubscriptionId).Any())
                                 .Select(r => new Resource
                                 {
                                     SubscriptionId = r.SubscriptionId,
                                     ItemId = r.ItemId,
                                     UsageThisMonth = r.Usage,
                                     UsageLastMonth = 0.0,
                                     EffectiveRate = r.EffectiveRate,
                                     ResourceName = r.ResourceName,
                                     UnitOfMeasure = r.UnitOfMeasure
                                 });

    //union data
    resources = resources.Union(resourcesOnlyLastMonth);
    resources = resources.Union(resourcesOnlyThisMonth);
}
//data for last month available
else if (resourcesLastMonth.Any())
{
    resources = from resource in resourcesLastMonth
                select new Resource
                {
                    SubscriptionId = resource.SubscriptionId,
                    ItemId = resource.ItemId,
                    UsageThisMonth = 0.0,
                    UsageLastMonth = resource.Usage,
                    EffectiveRate = resource.EffectiveRate,
                    ResourceName = resource.ResourceName,
                    UnitOfMeasure = resource.UnitOfMeasure
                };
}
//data for this month available
else if (resourcesThisMonth.Any())
{
    resources = from resource in resourcesThisMonth
                select new Resource
                {
                    SubscriptionId = resource.SubscriptionId,
                    ItemId = resource.ItemId,
                    UsageThisMonth = resource.Usage,
                    UsageLastMonth = 0.0,
                    EffectiveRate = resource.EffectiveRate,
                    ResourceName = resource.ResourceName,
                    UnitOfMeasure = resource.UnitOfMeasure
                };
}
//no data available
else
{
    resources = new List<Resource>();
}

Problem:

This is very much code - should be less, any possible solutions failed so far

Thanks for helping!

No, it's more than a union operation... - have a look at the example above — Jan Kruse, Sep 21 '16 at 13:29

score 1 · Accepted Answer · answered Sep 21 '16 at 13:53

public class ExampleClass
{
    public int Id1 { get; set; }
    public int Id2 { get; set; }
    public int Usage { get; set; }
    public int UsageThis { get; set; }
    public int UsageLast { get; set; }
}


        List<ExampleClass> listThisMonth = new List<ExampleClass>
        {
            new ExampleClass{Id1=1, Id2=1,Usage=7, UsageThis=1, UsageLast=0},
            new ExampleClass{Id1=2, Id2=2,Usage=4, UsageThis=2, UsageLast=0},
            new ExampleClass{Id1=3, Id2=3,Usage=1, UsageThis=3, UsageLast=0},
        };

        List<ExampleClass> listLastMonth = new List<ExampleClass>
        {
            new ExampleClass{Id1=1, Id2=1,Usage=3, UsageThis=1, UsageLast=1},
            new ExampleClass{Id1=4, Id2=4,Usage=3, UsageThis=4, UsageLast=3},
            new ExampleClass{Id1=2, Id2=2,Usage=1, UsageThis=8, UsageLast=6},
        };

        var result = listThisMonth.Select(a=>new {value=a, list=1})
            .Union(listLastMonth.Select(a => new { value = a, list = 2 }))
            .GroupBy(a => new { Id1 = a.value.Id1, Id2 = a.value.Id2 })
            .Select(x => new ExampleClass
            {
                Id1 = x.Key.Id1,
                Id2 = x.Key.Id2,
                UsageThis = x.Any(o => o.list == 1) ? x.First(o => o.list == 1).value.Usage : 0,
                UsageLast = x.Any(o => o.list == 2) ? x.First(o => o.list == 2).value.Usage : 0,
                Usage = x.Sum(o=>o.value.Usage)
            }).ToList();

        //id1   id2 current last    sum
        //1     1   7       3       10
        //2     2   4       1       5
        //3     3   1       0       1
        //4     4   0       3       3

Wyatt Earp · Answer 2 · 2016-09-21T13:57:02.873

It looks to me that what you're looking for is a full outer join. Unfortunately, it looks like LINQ doesn't have a construct for that. So, there are a few options: LINQ - Full Outer Join

For your scenario, it looks like you have some redundant code. You should be able to do the union using two outer joins to get the correct result set. For example:

// Left join the current month with the last month
var currentMonth = 
    from current in resourcesThisMonth
    join last in resourcesLastMonth on new { current.SubscriptionId, current.ItemId } equals new { last.SubscriptionId, last.ItemId } into outer
    from o in outer.DefaultIfEmpty()
    select new Resource
    {
        SubscriptionId = current.SubscriptionId,
        ItemId = current.ItemId,
        UnitsThisMonth = current.Units,
        UnitsLastMonth = o?.Units ?? 0, // Replace NULL with 0
        EffectiveRate = current.EffectiveRate,
        ResourceName = current.ResourceName,
        UnitOfMeasure = current.UnitOfMeasure
    };

// Reverse of the first join.  Last month LEFT JOIN Current month
var lastMonth = 
    from last in resourcesLastMonth
    join current in resourcesThisMonth on new { last.SubscriptionId, last.ItemId } equals new { current.SubscriptionId, current.ItemId } into outer
    from o in outer.DefaultIfEmpty()
    select new Resource
    {
        SubscriptionId = last.SubscriptionId,
        ItemId = last.ItemId,
        UnitsThisMonth = o?.Units ?? 0, // Replace NULL with 0
        UnitsLastMonth = last.Units,
        EffectiveRate = o?.EffectiveRate ?? last.EffectiveRate,
        ResourceName = o?.ResourceName ?? last.ResourceName,
        UnitOfMeasure = o?.UnitOfMeasure ?? last.UnitOfMeasure
    };

// Union them together to get a full join
var resources = currentMonth.Union(lastMonth);

not bad - but resources used in both months are now twice in the resource list — Jan Kruse, Sep 21 '16 at 13:44
Sorry, but that doesn't fix this - current isn't even available in the lastMonth select... — Jan Kruse, Sep 21 '16 at 13:51
Okay, I opened up LinqPad and actually tested it this time. :). It should work correctly now. — Wyatt Earp, Sep 21 '16 at 13:58

LINQ Join usage data from two data sets into one

What I have:

What I want:

What I have:

Problem:

Thanks for helping!

2 Answers2