c# - Linq 添加缺失的不同值

Question

我有一些执行以下操作的 linq 查询:

...
CarsCategories = new ObservableCollection<CarsCategory>(y
                                        .GroupBy(x1 => new { a = x1.Field<string>("CategoryName") })
                                        .Select(z => new CarCategory
                                        {
                                            CategoryName = z.Key.a,
                                            CarGroups =  new ObservableCollection<CarGroup>(z
                                                .GroupBy(x1 => new { a = x1.Field<string>("GroupName") })
                                                .Select(z1 => new CarGroup
                                                    {
                                                        GroupName = z1.Key.a,
                                                        CarTests = new ObservableCollection<CarTest>(z1
                                                            .GroupBy(x2 => new { a = x2.Field<string>("TestName") })
                                                            .Select(z2 => new CarTest
                                                            {
                                                                TestName = z2.Key.a,
                                                                CarTestNumbers = new ObservableCollection<CarTestNumber>(z2
                                                                    .Select(z3 => new CarTestNumber
                                                                    {
                                                                       TestNumber = z3.Field<String>("TestNumber"),
                                                                       TestVisitCode = z3.Field<String>("TestVisitCode")
                                                                    }))

                                                            }))
                                                    }

                                        ))}))
...

基本上我有一个大表，按 CarCategory、CarGroup、CarTest 分组，每个 CarTest 都有多个 TestNumber 和 TestVisitCode 参数。

一切都按预期进行，但我想执行以下操作:

此表中的所有测试都有不同的 TestVisitCode 值。我希望对于分组类别 CarTestNumbers 中的每个测试，表中都存在所有不同的 TestVisitCode ，并且我有一个值来显示它，否则显示一个空字符串。

我的 CarTestNumber 模型如下所示:

public class CarTestNumber
    {
        public string TestNumber { get; set; }

        public string TestVisitCode { get; set; }

        public string VisitCodeAndTestNumber
        {
            get { return TestVisitCode +" - " +  TestNumber; }
        }

    }

目前我只显示现有的 TestVisitCode 和 TestNumber。

例如:

Row1: CategoryName - FirstCategory GroupName - FirstGroup TestName - "FirstTest" TestNumber - "12" TestVisitCode - "W1"
Row2: CategoryName - FirstCategory GroupName - FirstGroup TestName - "FirstTest" TestNumber - "13" TestVisitCode - "W2"
Row3: CategoryName - FirstCategory GroupName - FirstGroup TestName - "SecondTest" TestNumber - "14" TestVisitCode - "W1"
Row4: CategoryName - FirstCategory GroupName - FirstGroup TestName - "SecondTest" TestNumber - "15" TestVisitCode - "W2"
Row5: CategoryName - FirstCategory GroupName - FirstGroup TestName - "SecondTest" TestNumber - "16" TestVisitCode - "W3"

After executing linq the data will look like this

FirstCategory
    FirstGroup
        FirstTest - {(W1,12), (W2,13)}
        SecondTest - {(W1,14), (W2,15),(W3, 16)}

What I would like to have

FirstCategory
    FirstGroup
        FirstTest - {(W1,12), (W2,13),(W3, "")}
        SecondTest - {(W1,14), (W2,15),(W3, 16)}

Answer 1

您可以先选择所有TestVisitCodes。在您的 linq 查询中，您可以与当前 CarTestNumber 中不存在的所有这些 VisitCode 进行联合。这有点令人困惑。

我创建一个 DataTable 并用您的测试数据填充它:

DataTable loDt = new DataTable();
loDt.Columns.Add("CategoryName", typeof(string));
loDt.Columns.Add("GroupName", typeof(string));
loDt.Columns.Add("TestName", typeof(string));
loDt.Columns.Add("TestNumber", typeof(string));
loDt.Columns.Add("TestVisitCode", typeof(string));

var loRow = loDt.NewRow();
loRow["CategoryName"] = "FirstCategory";
loRow["GroupName"] = "FirstGroup";
loRow["TestName"] = "FirstTest";
loRow["TestNumber"] = "12";
loRow["TestVisitCode"] = "W1";
loDt.Rows.Add(loRow);

loRow = loDt.NewRow();
loRow["CategoryName"] = "FirstCategory";
loRow["GroupName"] = "FirstGroup";
loRow["TestName"] = "FirstTest";
loRow["TestNumber"] = "13";
loRow["TestVisitCode"] = "W2";
loDt.Rows.Add(loRow);

...    

然后我列出了所有 TestVisitCodes 的列表:

var loTestVisitCodes = loDt.AsEnumerable().Select(item => item.Field<string>("TestVisitCode")).Distinct();

最后是 linq 查询(查看 union 语句):

var loCarsCategories = loDt.AsEnumerable()
    .GroupBy(row => new { CategoryName = row.Field<string>("CategoryName") })
    .Select(catGroup => new CarCategory
    {
        CategoryName = catGroup.Key.CategoryName,
        CarGroups = new List<CarGroup>(catGroup
            .GroupBy(row => new { GroupName = row.Field<string>("GroupName") })
            .Select(groupGroup => new CarGroup
            {
                GroupName = groupGroup.Key.GroupName,
                CarTests = new List<CarTest>(groupGroup
                    .GroupBy(row => new { TestName = row.Field<string>("TestName") })
                    .Select(groupTest => new CarTest
                    {
                        TestName = groupTest.Key.TestName,
                        CarTestNumbers = new List<CarTestNumber>(groupTest
                            .Select(row => new CarTestNumber
                            {
                                TestNumber = row.Field<String>("TestNumber"),
                                TestVisitCode = row.Field<String>("TestVisitCode")
                            })
                            .Union(new List<CarTestNumber>(loTestVisitCodes
                                .Except(groupTest.Select(row => row.Field<string>("TestVisitCode")))
                                .Select(VisitCode => new CarTestNumber
                                {
                                    TestNumber = string.Empty,
                                    TestVisitCode = VisitCode
                                }))))
                    }))
            }))
    });