英文:
C# Pivot table data into a ViewModel using Linq
问题
我可以帮你将你的问题进行翻译成中文,代码部分将保持不变。以下是翻译好的内容:
我有一个使用案例,其中包含以以下方式存储的数据的数据表,需要基于工作级别(JobLevel)进行数据透视,并在DataGridControl中显示。但是,为了使其正常工作,首先需要将从数据库接收的数据表转换为ViewModel对象,以将结果显示为行,以便用户可以在DataGridControl上修改值列。该表存储了与不同级别的员工有关的信息,以及每个级别的员工数量以及其颜色和性别。如果某个值没有数据,它应该默认为零。
工作级别枚举:
```cs
public enum JobLevel
{
Top, // 高级别
Mid, // 中级别
Low // 低级别
}
颜色枚举:
```cs
public enum Color
{
Red, // 红色
Blue, // 蓝色
Green // 绿色
}
性别枚举:
```cs
public enum Gender
{
Female, // 女性
Male, // 男性
Other // 其他
}
值实体类:
public class EmployeeGroupValue
{
[Key, Column(Order = 0)]
[Required]
public JobLevel JobLevel { get;set; }
[Key, Column(Order = 1)]
[Required]
public Gender Gender { get;set; }
[Key, Column(Order = 2)]
[Required]
public Color Color { get;set; }
[Required]
public int Value { get;set; }
}
'Values'数据示例:
Id | JobLevel | Gender | Color | Value |
---|---|---|---|---|
1 | Top | Male | Red | 10 |
2 | Top | Other | Red | 5 |
3 | Top | Female | Blue | 20 |
4 | Mid | Other | Blue | 5 |
5 | Mid | Female | Green | 50 |
6 | Low | Male | Green | 5 |
7 | Low | Other | Red | 7 |
8 | Low | Female | Green | 12 |
员工组值数据表视图:
JobLevel | Female | Male | Other |
---|
xxxxxxxxxxxx | R | B | G | R | B | G | R | B | G |
---|---|---|---|---|---|---|---|---|---|
Top | 0 | 20 | 0 | 10 | 0 | 0 | 5 | 0 | 0 |
Mid | 0 | 0 | 50 | 0 | 0 | 0 | 0 | 5 | 0 |
Low | 0 | 0 | 12 | 0 | 0 | 5 | 7 | 0 | 0 |
建议的视图模型:
public class EmployeeGroupValueViewModel
{
public string JobLevel { get;set; }
public int FemaleRed { get;set; }
public int FemaleBlue { get;set; }
public int FemaleGreen { get;set; }
public int MaleRed { get;set; }
public int MaleBlue { get;set; }
public int MaleGreen { get;set; }
public int OtherRed { get;set; }
public int OtherBlue { get;set; }
public int OtherGreen { get;set; }
}
如何使用LINQ将我的数据转换为这样的视图模型,并在数据库中没有行的情况下将Value默认为零?
<details>
<summary>英文:</summary>
I have a use case that contains data stored in a values tables as shown below, that needs to be pivot based on a JobLevel and displayed in a DataGridControl, however in order to get this working I firstly need to transform the data table I receive from the database into a ViewModel object that can display the result as a row, to allow users can be able to modify the values column on the DataGridControl. The table stores information pertaining to employees in various levels and a count of employees in each level and what their color and gender are. If a value has no data, it should default to zero.
Job Level enum:
```cs
public enum JobLevel
{
Top,
Mid,
Low
}
public enum Color
{
Red,
Blue,
Green
}
public enum Gender
{
Female,
Male,
Other
}
Values entity class:
public class EmployeeGroupValue
{
[Key, Column(Order = 0)]
[Required]
public JobLevel JobLevel { get;set; }
[Key, Column(Order = 1)]
[Required]
public Gender Gender { get;set; }
[Key, Column(Order = 2)]
[Required]
public Color Color { get;set; }
[Required]
public int Value { get;set; }
}
Example of 'Values' data:
Id | JobLevel | Gender | Color | Value |
---|---|---|---|---|
1 | Top | Male | Red | 10 |
2 | Top | Other | Red | 5 |
3 | Top | Female | Blue | 20 |
4 | Mid | Other | Blue | 5 |
5 | Mid | Female | Green | 50 |
6 | Low | Male | Green | 5 |
7 | Low | Other | Red | 7 |
8 | Low | Female | Green | 12 |
Employee Group Values DataGrid View:
JobLevel | Female | Male | Other |
---|
xxxxxxxxxxxx | R | B | G | R | B | G | R | B | G |
---|---|---|---|---|---|---|---|---|---|
Top | 0 | 20 | 0 | 10 | 0 | 0 | 5 | 0 | 0 |
Mid | 0 | 0 | 50 | 0 | 0 | 0 | 0 | 5 | 0 |
Low | 0 | 0 | 12 | 0 | 0 | 5 | 7 | 0 | 0 |
Suggested View Model:
public class EmployeeGroupValueViewModel
{
public string JobLevel { get;set; }
public int FemaleRed { get;set; }
public int FemaleBlue { get;set; }
public int FemaleGreen { get;set; }
public int MaleRed { get;set; }
public int MaleBlue { get;set; }
public int MaleGreen { get;set; }
public int OtherRed { get;set; }
public int OtherBlue { get;set; }
public int OtherGreen { get;set; }
}
How can I transform my data using linq into such a view model and default Value to zero where there are no rows in the database?
答案1
得分: 1
以下是翻译好的部分:
尝试以下查询:
var databaseData = context.EmployeeGroupValues
.GroupBy(e => new { e.JobLevel, e.Gender, e.Color })
.Select(g => new
{
g.Key.JobLevel,
g.Key.Gender,
g.Key.Color,
Value = g.Sum(x => x.Value)
})
.ToList();
var enrichedData =
from jobLevel in new [] { JobLevel.Top, JobLevel.Mid, JobLevel.Low }
from gender in new [] { Gender.Female, Gender.Male, Gender.Other }
from color in new [] { Color.Red, Color.Blue, Color.Green }
join d in databaseData on
new { JobLevel = jobLevel, Gender = gender, Color = color }
equals new { d.JobLevel, d.Gender, d.Color } into gj
from d in gj.DefaultIfEmpty(new { JobLevel = jobLevel, Gender = gender, Color = color, Value = 0 })
group d by jobLevel into g
select new EmployeeGroupValueViewModel
{
JobLevel = g.Key.ToString(),
FemaleRed = g.Sum(x => x.Gender == Gender.Female && x.Color == Color.Red ? x.Value : 0),
FemaleBlue = g.Sum(x => x.Gender == Gender.Female && x.Color == Color.Blue ? x.Value : 0),
FemaleGreen = g.Sum(x => x.Gender == Gender.Female && x.Color == Color.Green ? x.Value : 0),
MaleRed = g.Sum(x => x.Gender == Gender.Male && x.Color == Color.Red ? x.Value : 0),
MaleBlue = g.Sum(x => x.Gender == Gender.Male && x.Color == Color.Blue ? x.Value : 0),
MaleGreen = g.Sum(x => x.Gender == Gender.Male && x.Color == Color.Green ? x.Value : 0),
OtherRed = g.Sum(x => x.Gender == Gender.Other && x.Color == Color.Red ? x.Value : 0),
OtherBlue = g.Sum(x => x.Gender == Gender.Other && x.Color == Color.Blue ? x.Value : 0),
OtherGreen = g.Sum(x => x.Gender == Gender.Other && x.Color == Color.Green ? x.Value : 0),
};
var result = enrichedData.ToList();
您可以在 dotnetfiddle 中使用此代码。
英文:
Try the following query:
var databaseData = context.EmployeeGroupValues
.GroupBy(e => new { e.JobLevel, e.Gender, e.Color })
.Select(g => new
{
g.Key.JobLevel,
g.Key.Gender,
g.Key.Color,
Value = g.Sum(x => x.Value)
})
.ToList();
var enrichedData =
from jobLevel in new [] { JobLevel.Top, JobLevel.Mid, JobLevel.Low }
from gender in new [] { Gender.Female, Gender.Male, Gender.Other }
from color in new [] { Color.Red, Color.Blue, Color.Green }
join d in databaseData on
new { JobLevel = jobLevel, Gender = gender, Color = color}
equals new { d.JobLevel, d.Gender, d.Color } into gj
from d in gj.DefaultIfEmpty(new { JobLevel = jobLevel, Gender = gender, Color = color, Value = 0 })
group d by jobLevel into g
select new EmployeeGroupValueViewModel
{
JobLevel = g.Key.ToString(),
FemaleRed = g.Sum(x => x.Gender == Gender.Female && x.Color == Color.Red ? x.Value : 0),
FemaleBlue = g.Sum(x => x.Gender == Gender.Female && x.Color == Color.Blue ? x.Value : 0),
FemaleGreen = g.Sum(x => x.Gender == Gender.Female && x.Color == Color.Green ? x.Value : 0),
MaleRed = g.Sum(x => x.Gender == Gender.Male && x.Color == Color.Red ? x.Value : 0),
MaleBlue = g.Sum(x => x.Gender == Gender.Male && x.Color == Color.Blue ? x.Value : 0),
MaleGreen = g.Sum(x => x.Gender == Gender.Male && x.Color == Color.Green ? x.Value : 0),
OtherRed = g.Sum(x => x.Gender == Gender.Other && x.Color == Color.Red ? x.Value : 0),
OtherBlue = g.Sum(x => x.Gender == Gender.Other && x.Color == Color.Blue ? x.Value : 0),
OtherGreen = g.Sum(x => x.Gender == Gender.Other && x.Color == Color.Green ? x.Value : 0),
};
var result = enrichedData.ToList();
You can play with this in dotnetfiddle
答案2
得分: 1
使用一个自定义增强的Dictionary
,对于缺失的条目返回default(TValue)
,你可以将源数据重新映射为一组字典,避免为每个属性值重新扫描数据。
以下是增强的Dictionary
以及一些扩展方法,使其与LINQ一起更容易使用:
#region 增强的字典
public static class DictExt {
// DefaultDictionary,对于缺失的条目返回default(TValue)
public static DefaultDictionary<TKey, TValue> ToDefaultDictionary<T, TKey, TValue>(this IEnumerable<T> items, Func<T, TKey> keyFn, Func<T, TValue> valFn) {
var nd = new DefaultDictionary<TKey, TValue>();
foreach (var item in items)
nd.Add(keyFn(item), valFn(item));
return nd;
}
public static DefaultDictionary<TKey, TValue> ToDefaultDictionary<TValue>(this IEnumerable<TValue> items, Func<TValue, TKey> keyFn) {
var nd = new DefaultDictionary<TKey, TValue>();
foreach (var item in items)
nd.Add(keyFn(item), item);
return nd;
}
public static DefaultDictionary<TKey, TValue> ToDefaultDictionary<TKey, TValue>(this IDictionary<TKey, TValue> srcd)
=> new DefaultDictionary<TKey, TValue>(srcd);
}
//***
// 增强的字典,对于缺失的条目返回default(TValue)
//***
public class DefaultDictionary<TKey, TValue> : Dictionary<TKey, TValue> {
public DefaultDictionary(IDictionary<TKey, TValue> d) : base() {
foreach (var kvp in d)
Add(kvp.Key, kvp.Value);
}
public DefaultDictionary() : base() { }
public new TValue this[TKey key] {
get {
TryGetValue(key, out var val);
return val;
}
set => base[key] = value;
}
}
一旦你拥有这个,你可以使用LINQ将原始稀疏数据转换为字典。我假设数据可能在任何级别包含重复值。
var jobLevelDict =
values
.GroupBy(v => v.JobLevel)
.ToDefaultDictionary(
vg => vg.Key,
vjlg => vjlg
.GroupBy(v => v.Gender)
.ToDefaultDictionary(
vgg => vgg.Key,
vgg => vgg
.GroupBy(v => v.Color)
.ToDefaultDictionary(
vcg => vcg.Key,
vcg => vcg.Sum(v => v.Value))));
一旦你拥有字典树,你可以为网格构建完整的`List<EmployeeGroupValueViewModel>`:
```csharp
var ans = Enum.GetValues<JobLevel>()
.Select(jl => new EmployeeGroupValueViewModel {
JobLevel = jl.ToString(),
FemaleRed = jobLevelDict?[jl]?[Gender.Female]?[Color.Red] ?? 0,
FemaleBlue = jobLevelDict?[jl]?[Gender.Female]?[Color.Blue] ?? 0,
FemaleGreen = jobLevelDict?[jl]?[Gender.Female]?[Color.Green] ?? 0,
MaleRed = jobLevelDict?[jl]?[Gender.Male]?[Color.Red] ?? 0,
MaleBlue = jobLevelDict?[jl]?[Gender.Male]?[Color.Blue] ?? 0,
MaleGreen = jobLevelDict?[jl]?[Gender.Male]?[Color.Green] ?? 0,
OtherRed = jobLevelDict?[jl]?[Gender.Other]?[Color.Red] ?? 0,
OtherBlue = jobLevelDict?[jl]?[Gender.Other]?[Color.Blue] ?? 0,
OtherGreen = jobLevelDict?[jl]?[Gender.Other]?[Color.Green] ?? 0
})
.ToList();
英文:
Using a custom enhanced Dictionary
that returns default(TValue)
for missing entries, you can remap the source data to a tree of dictionaries and avoid re-scanning the data for each property value.
Here is the enhanced Dictionary
and some extension methods to make using it easier with LINQ:
#region Enhanced Dictionaries
public static class DictExt {
// DefaultDictionary that returns default(TValue) for missing entries
public static DefaultDictionary<TKey, TValue> ToDefaultDictionary<T, TKey, TValue>(this IEnumerable<T> items, Func<T, TKey> keyFn, Func<T, TValue> valFn) {
var nd = new DefaultDictionary<TKey, TValue>();
foreach (var item in items)
nd.Add(keyFn(item), valFn(item));
return nd;
}
public static DefaultDictionary<TKey, TValue> ToDefaultDictionary<TKey, TValue>(this IEnumerable<TValue> items, Func<TValue, TKey> keyFn) {
var nd = new DefaultDictionary<TKey, TValue>();
foreach (var item in items)
nd.Add(keyFn(item), item);
return nd;
}
public static DefaultDictionary<TKey, TValue> ToDefaultDictionary<TKey, TValue>(this IDictionary<TKey, TValue> srcd)
=> new DefaultDictionary<TKey, TValue>(srcd);
}
//***
// Enhanced Dictionary that returns default(TValue) for missing entries
//***
public class DefaultDictionary<TKey, TValue> : Dictionary<TKey, TValue> {
public DefaultDictionary(IDictionary<TKey, TValue> d) : base() {
foreach (var kvp in d)
Add(kvp.Key, kvp.Value);
}
public DefaultDictionary() : base() { }
public new TValue this[TKey key] {
get {
TryGetValue(key, out var val);
return val;
}
set => base[key] = value;
}
}
Once you have this, you can use it with LINQ to convert the original sparse data into dictionaries. I assumed the data might contain duplicate values at any level.
var jobLevelDict =
values
.GroupBy(v => v.JobLevel)
.ToDefaultDictionary(
vg => vg.Key,
vjlg => vjlg
.GroupBy(v => v.Gender)
.ToDefaultDictionary(
vgg => vgg.Key,
vgg => vgg
.GroupBy(v => v.Color)
.ToDefaultDictionary(
vcg => vcg.Key,
vcg => vcg.Sum(v => v.Value))));
One you have the tree of dictionaries, you can build the full List<EmployeeGroupValueViewModel>
for the grid:
var ans = Enum.GetValues<JobLevel>()
.Select(jl => new EmployeeGroupValueViewModel {
JobLevel = jl.ToString(),
FemaleRed = jobLevelDict?[jl]?[Gender.Female]?[Color.Red] ?? 0,
FemaleBlue = jobLevelDict?[jl]?[Gender.Female]?[Color.Blue] ?? 0,
FemaleGreen = jobLevelDict?[jl]?[Gender.Female]?[Color.Green] ?? 0,
MaleRed = jobLevelDict?[jl]?[Gender.Male]?[Color.Red] ?? 0,
MaleBlue = jobLevelDict?[jl]?[Gender.Male]?[Color.Blue] ?? 0,
MaleGreen = jobLevelDict?[jl]?[Gender.Male]?[Color.Green] ?? 0,
OtherRed = jobLevelDict?[jl]?[Gender.Other]?[Color.Red] ?? 0,
OtherBlue = jobLevelDict?[jl]?[Gender.Other]?[Color.Blue] ?? 0,
OtherGreen = jobLevelDict?[jl]?[Gender.Other]?[Color.Green] ?? 0
})
.ToList();
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论