使用Linq将C#数据透视表转换为ViewModel

huangapple
go评论70阅读模式
英文:

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 =&gt; new { e.JobLevel, e.Gender, e.Color })
    .Select(g =&gt; new 
    {
        g.Key.JobLevel,
        g.Key.Gender,
        g.Key.Color,
        Value = g.Sum(x =&gt; 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 =&gt; x.Gender == Gender.Female &amp;&amp; x.Color == Color.Red   ? x.Value : 0),
        FemaleBlue  = g.Sum(x =&gt; x.Gender == Gender.Female &amp;&amp; x.Color == Color.Blue  ? x.Value : 0),
        FemaleGreen = g.Sum(x =&gt; x.Gender == Gender.Female &amp;&amp; x.Color == Color.Green ? x.Value : 0),
        MaleRed     = g.Sum(x =&gt; x.Gender == Gender.Male   &amp;&amp; x.Color == Color.Red   ? x.Value : 0),
        MaleBlue    = g.Sum(x =&gt; x.Gender == Gender.Male   &amp;&amp; x.Color == Color.Blue  ? x.Value : 0),
        MaleGreen   = g.Sum(x =&gt; x.Gender == Gender.Male   &amp;&amp; x.Color == Color.Green ? x.Value : 0),
        OtherRed    = g.Sum(x =&gt; x.Gender == Gender.Other  &amp;&amp; x.Color == Color.Red   ? x.Value : 0),
        OtherBlue   = g.Sum(x =&gt; x.Gender == Gender.Other  &amp;&amp; x.Color == Color.Blue  ? x.Value : 0),
        OtherGreen  = g.Sum(x =&gt; x.Gender == Gender.Other  &amp;&amp; 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&lt;TKey, TValue&gt; ToDefaultDictionary&lt;T, TKey, TValue&gt;(this IEnumerable&lt;T&gt; items, Func&lt;T, TKey&gt; keyFn, Func&lt;T, TValue&gt; valFn) {
        var nd = new DefaultDictionary&lt;TKey, TValue&gt;();
        foreach (var item in items)
            nd.Add(keyFn(item), valFn(item));
        return nd;
    }
    public static DefaultDictionary&lt;TKey, TValue&gt; ToDefaultDictionary&lt;TValue&gt;(this IEnumerable&lt;TValue&gt; items, Func&lt;TValue, TKey&gt; keyFn) {
        var nd = new DefaultDictionary&lt;TKey, TValue&gt;();
        foreach (var item in items)
            nd.Add(keyFn(item), item);
        return nd;
    }
    public static DefaultDictionary&lt;TKey, TValue&gt; ToDefaultDictionary&lt;TKey, TValue&gt;(this IDictionary&lt;TKey, TValue&gt; srcd)
        =&gt; new DefaultDictionary&lt;TKey, TValue&gt;(srcd);
 }

//***
// 增强的字典，对于缺失的条目返回default(TValue)
//***
public class DefaultDictionary&lt;TKey, TValue&gt; : Dictionary&lt;TKey, TValue&gt; {
    public DefaultDictionary(IDictionary&lt;TKey, TValue&gt; 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 =&gt; base[key] = value;
    }
}

一旦你拥有这个，你可以使用LINQ将原始稀疏数据转换为字典。我假设数据可能在任何级别包含重复值。

var jobLevelDict = 
    values
        .GroupBy(v =&gt; v.JobLevel)
        .ToDefaultDictionary(
            vg =&gt; vg.Key,
            vjlg =&gt; vjlg
                    .GroupBy(v =&gt; v.Gender)
                    .ToDefaultDictionary(
                        vgg =&gt; vgg.Key,
                        vgg =&gt; vgg
                                .GroupBy(v =&gt; v.Color)
                                .ToDefaultDictionary(
                                    vcg =&gt; vcg.Key,
                                    vcg =&gt; vcg.Sum(v =&gt; v.Value))));

一旦你拥有字典树，你可以为网格构建完整的`List&lt;EmployeeGroupValueViewModel&gt;`：

```csharp
var ans = Enum.GetValues&lt;JobLevel&gt;()
             .Select(jl =&gt; 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&lt;TKey, TValue&gt; ToDefaultDictionary&lt;T, TKey, TValue&gt;(this IEnumerable&lt;T&gt; items, Func&lt;T, TKey&gt; keyFn, Func&lt;T, TValue&gt; valFn) {
        var nd = new DefaultDictionary&lt;TKey, TValue&gt;();
        foreach (var item in items)
            nd.Add(keyFn(item), valFn(item));
        return nd;
    }
    public static DefaultDictionary&lt;TKey, TValue&gt; ToDefaultDictionary&lt;TKey, TValue&gt;(this IEnumerable&lt;TValue&gt; items, Func&lt;TValue, TKey&gt; keyFn) {
        var nd = new DefaultDictionary&lt;TKey, TValue&gt;();
        foreach (var item in items)
            nd.Add(keyFn(item), item);
        return nd;
    }
    public static DefaultDictionary&lt;TKey, TValue&gt; ToDefaultDictionary&lt;TKey, TValue&gt;(this IDictionary&lt;TKey, TValue&gt; srcd)
        =&gt; new DefaultDictionary&lt;TKey, TValue&gt;(srcd);
 }

//***
// Enhanced Dictionary that returns default(TValue) for missing entries
//***
public class DefaultDictionary&lt;TKey, TValue&gt; : Dictionary&lt;TKey, TValue&gt; {
    public DefaultDictionary(IDictionary&lt;TKey, TValue&gt; 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 =&gt; 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 =&gt; v.JobLevel)
        .ToDefaultDictionary(
            vg =&gt; vg.Key,
            vjlg =&gt; vjlg
                    .GroupBy(v =&gt; v.Gender)
                    .ToDefaultDictionary(
                        vgg =&gt; vgg.Key,
                        vgg =&gt; vgg
                                .GroupBy(v =&gt; v.Color)
                                .ToDefaultDictionary(
                                    vcg =&gt; vcg.Key,
                                    vcg =&gt; vcg.Sum(v =&gt; v.Value))));

One you have the tree of dictionaries, you can build the full List&lt;EmployeeGroupValueViewModel&gt; for the grid:

var ans = Enum.GetValues&lt;JobLevel&gt;()
             .Select(jl =&gt; 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();

huangapple
  • 本文由 发表于 2023年2月9日 00:01:33
  • 转载请务必保留本文链接：https://go.coder-hub.com/75388484.html
  • c#
  • entity-framework
  • entity-framework-core
  • join
  • linq
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定