Jan Kruse Jan Kruse - 2 months ago 10
C# Question

LINQ Join usage data from two data sets into one

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!


Answer
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
Comments