LINQ连接使用数据从两个数据集到一个
本文关键字：两个 一个 数据集 连接 数据 LINQ | 更新日期: 2023-09-27 18:08:11
我有什么:
下列模型的两个列表:
int SubscriptionId
int ItemId
double Usage
double EffectiveRate
string ResourceName
string UnitOfMeasure
第一个包含上个月的使用数据，如下所示:
SubscriptionId ItemId Usage EffectiveRate ResourceName UnitOfMesaure
_________________________________________________________________________
1 1 2 2,75 R1 U1
1 2 3 1,50 R2 U2
秒包含当前月份的使用数据，如下所示:
SubscriptionId ItemId Usage EffectiveRate ResourceName UnitOfMesaure
_________________________________________________________________________
1 1 5 2,75 R1 U1
1 3 2 1,50 R3 U3
我想要的:
应该像这样合并到一个列表中:
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
我有什么:
//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>();
}
问题:
这是非常多的代码-应该更少，任何可能的解决方案都失败了
谢谢你的帮助!
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
在我看来，您正在寻找的是一个完整的外部连接。不幸的是，LINQ似乎没有这样的构造。所以，这里有几个选项:LINQ -完全外连接
对于您的场景，看起来您有一些冗余代码。您应该能够使用两个外部连接进行联合以获得正确的结果集。例如:
// 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);