c# linq-to-sql EF查询来匹配特定的JSON结构

本文关键字：JSON 结构 linq-to-sql EF 查询 | 更新日期: 2023-09-27 18:11:58

我有以下结构的JSON:

[
  {
    "ID": 1,
    "Label": "Reg Scheme",
    "Colours": [
      {
        "ID": 1,
        "Value": "0x3333cc",
        "Result": 1,
        "Label": null
      },
      {
        "ID": 2,
        "Value": "0x666699",
        "Result": 2,
        "Label": null
      },
      {
        "ID": 3,
        "Value": "0x009966",
        "Result": 3,
        "Label": null
      }
    ]
  },
  {
    "ID": 2,
    "Label": "Spesh Scheme",
    "Colours": [
      {
        "ID": 11,
        "Value": "0x59699c",
        "Result": 1,
        "Label": null
      },
      {
        "ID": 12,
        "Value": "0x0070ff",
        "Result": 2,
        "Label": null
      },
      {
        "ID": 13,
        "Value": "0x90865e",
        "Result": 3,
        "Label": null
      }
    ]
  },

和我有一个实体数据集，我已经加入了所有相关信息，并试图通过一个单一的link -to-sql EF查询返回到webapi方法产生JSON结构。

目前我的查询是:

return
    DbContext.Schemes
            .Join(
                DbContext.SchemeColours,
                s => s.SchemeID,
                sc => sc.SchemeID,
                (s, sc) => new
                    {
                        s.SchemeID,
                        s.Label,
                        sc.Colour,
                        sc.Result,
                        sc.ColourID
                    })
            .Select(a =>
                    new Overlay.ReportColourScheme
                        {
                            ID = a.SchemeID,
                            Label = a.Label,
                            Colours = new List<Overlay.ReportColour>
                                {
                                    new Overlay.ReportColour
                                        {
                                            ID = a.ColourID,
                                            Value = a.Colour,
                                            Result = a.Result
                                        }
                                }
                        })
            .ToArray();

几乎在那里，但不完全:

[
  {
    "ID": 1,
    "Label": "Regular Scheme",
    "Colours": [
      {
        "ID": 1,
        "Value": "0x3333cc",
        "Result": 1,
        "Label": null
      }
    ]
  },
  {
    "ID": 1,
    "Label": "Regular Scheme",
    "Colours": [
      {
        "ID": 2,
        "Value": "0x666699",
        "Result": 2,
        "Label": null
      }
    ]
  },
  {
    "ID": 1,
    "Label": "Regular Scheme",
    "Colours": [
      {
        "ID": 3,
        "Value": "0x009966",
        "Result": 3,
        "Label": null
      }
    ]
  },
  {
    "ID": 2,
    "Label": "Protanopia adjusted Scheme",
    "Colours": [
      {
        "ID": 11,
        "Value": "0x59699c",
        "Result": 1,
        "Label": null
      }
    ]
  },
  {
    "ID": 2,
    "Label": "Protanopia adjusted Scheme",
    "Colours": [
      {
        "ID": 12,
        "Value": "0x0070ff",
        "Result": 2,
        "Label": null
      }
    ]
  },
  {
    "ID": 2,
    "Label": "Protanopia adjusted Scheme",
    "Colours": [
      {
        "ID": 13,
        "Value": "0x90865e",
        "Result": 3,
        "Label": null
      }
    ]
  },

当然，它为每个resultID创建一个新列表。顶级ID是SchemeID-我正在寻找的是沿以下行逻辑:"使用特定SchemeID获取前3个结果，将它们添加到颜色列表中，然后移动到下一个SchemeID"

我相信这将产生与我开始这篇文章时相同的JSON。

如果您有任何帮助，我将不胜感激，谢谢。

c# linq-to-sql EF查询来匹配特定的JSON结构

尝试以下代码:

return
    DbContext.Schemes
            .Join(
                DbContext.SchemeColours,
                s => s.SchemeID,
                sc => sc.SchemeID,
                (s, sc) => new
                    {
                        s.SchemeID,
                        s.Label,
                        sc.Colour,
                        sc.Result,
                        sc.ColourID
                    })
            // After joining you group by SchemeID, in this way you have 
            // for each SchemeID the group of related items
            .GroupBy(a => a.SchemeID)
            // You then create your result, starting from the main object
            .Select(g =>
                    new Overlay.ReportColourScheme
                        {
                            ID = g.Key,
                            // I suppose you have at least a child for each SchemeID, 
                            // otherwise you can check if the list is empty
                            Label = g.FirstOrDefault().Label,
                            // For each group you create a list of child object
                            Colours = g.Select(v => new Overlay.ReportColour
                                        {
                                            ID = v.ColourID,
                                            Value = v.Colour,
                                            Result = v.Result
                                        }).ToList()
                        })
            .ToArray();

主要问题是您正在使用Join，实际上您需要Group Join:

return DbContext.Schemes
    .GroupJoin(DbContext.SchemeColours,
        s => s.SchemeID,
        sc => sc.SchemeID,
        (s, colours) => new Overlay.ReportColourScheme
        {
            ID = s.SchemeID,
            Label = s.Label,
            Colours = colours
                .Select(sc => new Overlay.ReportColour
                {
                    ID = sc.ColourID,
                    Value = sc.Colour,
                    Result = sc.Result,
                })
                .ToList()
        })
    .ToArray();

但是由于你正在使用实体框架，如果你定义(如果你还没有)并使用导航属性:

会更好更容易
class Scheme
{
    // ...
    public ICollection<SchemeColour> Colours { get; set; }
}

和

return DbContext.Schemes
    .Select(s => new Overlay.ReportColourScheme
    {
        ID = s.SchemeID,
        Label = s.Label,
        Colours = s.Colours
            .Select(sc => new Overlay.ReportColour
            {
                ID = sc.ColourID,
                Value = sc.Colour,
                Result = sc.Result,
            })
           .ToList()
    })
    .ToArray();
相关文章：