链接LINQ中不同数据集上的多个JOIN或GroupJOIN

mkshixfv 于 2023-10-13 发布在其他

关注(0)|答案(2)|浏览(102)

我期待的最终响应应该是这样的：

[
    {
        "ID": 4,
        "Floor": "BackOffice Floor",
        "OverallDescription": "Pretty Pretty Good",
        "Permit": "Admin",
        "TabOne": [
            {
                "Color": "Blue",
                "status": "ReadWrite"
            },
            {
                "Color": "Purple",
                "status": "ReadOnly"
            },
        ],
        "TabTwo": [
            {
                "Color": "Black",
                "status": "Offline"
            },
            {
                "Color": "Green",
                "status": "Online"
            },
            {
                "Color": "White",
                "status": "Blinking"
            },          
        ],      
        
    }
]

我为class建模如下：

public class AllSheets
{
    // from parent calss
    public int ID { get; set; }
    public string Floor { get; set; } = string.Empty;
    public string OverallDescription { get; set; } = string.Empty;
    public string Permit { get; set; } = string.Empty;
    
    // from children classes
    public IEnumerable<SheetChildOne> TabOne { get; set; } = Enumerable.Empty<SheetOne>();
    public IEnumerable<SheetChildTwo> TabTwo { get; set; } = Enumerable.Empty<SheetTwo>();
}

这里有三个classes，一个是parent，另外两个是children。他们的JOIN是在ID领域。

public class SheetMain
{
    public int ID { get; set; }
    public string Floor { get; set; } = string.Empty;
    public string OverallDescription { get; set; } = string.Empty;
    public string Permit { get; set; } = string.Empty;
}

public class SheetChildOne
{
    public int ID { get; set; }
    public string Floor { get; set; } = string.Empty;
    public string Color { get; set; } = string.Empty;
    public string Status { get; set;} = string.Empty;
}

public class SheetChildTwo
{
    public int ID { get; set; }
    public string Floor { get; set; } = string.Empty;
    public string Color { get; set; } = string.Empty;
    public string Status { get; set;} = string.Empty;
}

注意，我不想重复**一些在子节点和父节点中通用的字段，例如ID和Floor。
我可以自己开始写，但我有两件事需要帮助：**1：**如何在查询中加入多个group-join，以便将SheetChildTwo也包括在内？**2：**我如何告诉它不包括一些在parent和child中重复的字段，例如本例中的ID和Floor。
以下是我尝试的：

var result =
            sheetMain.GroupJoin
            (sheetChildOne,
                work1 => work1.ID,
                work2 => work2.ID,
                (t1, t2) => new AllSheets
                {
                    ID = t1.ID,
                    Floor = t1.Floor,
                    OverallDescription = t1.OverallDescription,
                    Permit = t1.Permit,                        
                    TabOne = t2.Select
                    (b => new SheetChildOne
                        {
                            Color = b.Color,
                            Status = b.Status,
                        }
                    )
                }
            );

linq

来源：https://stackoverflow.com/questions/77197258/chain-multiple-join-or-groupjoin-on-different-datasets-in-linq

2条答案

按热度按时间

68bkxrlz1#

尝试以下查询。使用查询语法，因为它在这里更可读：

var query = 
    from s in sheetMain
    join co in sheetChildOne on new { s.ID, s.Floor } equals { co.ID, co.Floor } into coG
    join ct in sheetChildTwo on new { s.ID, s.Floor } equals { ct.ID, ct.Floor } into ctG
    select new AllSheets
    {
        ID = s.ID,
        Floor = s.Floor,
        OverallDescription = s.OverallDescription,
        Permit = s.Permit,
        TabOne = coG.ToList(),
        TabTwo = ctG.ToList()
    };

赞(0）回复(0）举报 2023-10-13

tgabmvqs2#

这感觉就像一个XY problem。我对这个主题的快速实验。
在这两种情况下，控制台显示：

[
  {
    "ID": 4,
    "Floor": "BackOffice Floor",
    "OverallDescription": "Pretty Pretty Good",
    "Permit": "Admin",
    "TabOne": [
      {
        "Color": "Blue",
        "Status": "ReadWrite"
      },
      {
        "Color": "Purple",
        "Status": "ReadOnly"
      }
    ],
    "TabTwo": [
      {
        "Color": "Black",
        "Status": "Offline"
      },
      {
        "Color": "Green",
        "Status": "Online"
      },
      {
        "Color": "White",
        "Status": "Blinking"
      }
    ]
  }
]

1.匿名对象

using System.Text.Json;

internal class Program
{
    private static void Main(string[] args)
    {
        var sheetMain = new List<SheetMain>();
        var tabOne = new List<SheetChildOne>();
        var tabTwo = new List<SheetChildTwo>();

        sheetMain.Add(new()
        {
            ID = 4,
            Floor = "BackOffice Floor",
            OverallDescription = "Pretty Pretty Good",
            Permit = "Admin",
        });

        tabOne.Add(new() { ID = 4, Floor = "BackOffice Floor", Color = "Blue", Status = "ReadWrite", });
        tabOne.Add(new() { ID = 4, Floor = "BackOffice Floor", Color = "Purple", Status = "ReadOnly", });

        tabTwo.Add(new() { ID = 4, Floor = "BackOffice Floor", Color = "Black", Status = "Offline", });
        tabTwo.Add(new() { ID = 4, Floor = "BackOffice Floor", Color = "Green", Status = "Online", });
        tabTwo.Add(new() { ID = 4, Floor = "BackOffice Floor", Color = "White", Status = "Blinking", });

        var result = sheetMain.Select(sm => new {
            sm.ID,
            sm.Floor,
            sm.OverallDescription,
            sm.Permit,
            TabOne = tabOne
                .Where(t1 => t1.ID == sm.ID)
                .Select(t1 => new { t1.Color, t1.Status }),
            TabTwo = tabTwo
                .Where(t2 => t2.ID == sm.ID)
                .Select(t2 => new { t2.Color, t2.Status }),
        });

        var json = JsonSerializer.Serialize(result, new JsonSerializerOptions { WriteIndented = true });

        Console.WriteLine(json);
    }
}

1.专用类

using System.Text.Json;

public class SheetChildDTO
{
    required public string Color { get; set; }
    required public string Status { get; set; }
}

public class AllSheetsDTO
{
    // from parent calss
    required public int ID { get; set; }
    required public string Floor { get; set; }
    required public string OverallDescription { get; set; }
    required public string Permit { get; set; }

    // from children classes
    required public IEnumerable<SheetChildDTO> TabOne { get; set; }
    required public IEnumerable<SheetChildDTO> TabTwo { get; set; }
}

internal class Program
{
    private static void Main(string[] args)
    {
        var sheetMain = new List<SheetMain>();
        var tabOne = new List<SheetChildOne>();
        var tabTwo = new List<SheetChildTwo>();

        sheetMain.Add(new()
        {
            ID = 4,
            Floor = "BackOffice Floor",
            OverallDescription = "Pretty Pretty Good",
            Permit = "Admin",
        });

        tabOne.Add(new() { ID = 4, Floor = "BackOffice Floor", Color = "Blue", Status = "ReadWrite", });
        tabOne.Add(new() { ID = 4, Floor = "BackOffice Floor", Color = "Purple", Status = "ReadOnly", });

        tabTwo.Add(new() { ID = 4, Floor = "BackOffice Floor", Color = "Black", Status = "Offline", });
        tabTwo.Add(new() { ID = 4, Floor = "BackOffice Floor", Color = "Green", Status = "Online", });
        tabTwo.Add(new() { ID = 4, Floor = "BackOffice Floor", Color = "White", Status = "Blinking", });

        var result = sheetMain.Select(sm => new AllSheetsDTO() {
            ID = sm.ID,
            Floor = sm.Floor,
            OverallDescription = sm.OverallDescription,
            Permit = sm.Permit,
            TabOne = tabOne
                .Where(t1 => t1.ID == sm.ID && t1.Floor == sm.Floor)
                .Select(t1 => new SheetChildDTO() { Color = t1.Color, Status = t1.Status }),
            TabTwo = tabTwo
                .Where(t2 => t2.ID == sm.ID && t2.Floor == sm.Floor)
                .Select(t2 => new SheetChildDTO() { Color = t2.Color, Status = t2.Status }),
        });

        var json = JsonSerializer.Serialize(result, new JsonSerializerOptions { WriteIndented = true });

        Console.WriteLine(json);
    }
}

赞(0）回复(0）举报 2023-10-13

我来回答

链接LINQ中不同数据集上的多个JOIN或GroupJOIN

2条答案

相关问题

热门标签

最新问答