c# - ASP.NET LINQ查询以筛选和循环通过多个表

我为“ App”和“ AgeGroup”有两个单独的域模型类。 App类几乎不包含基本的整数和字符串属性，AgeGroup类也是如此。

我想要实现的是通过AppOrder的所有应用程序及其属性的JSON输出，这些属性嵌套在其关联的AgeGroups中，并按其GroupOrder属性排序

必需的示例JSON输出结构

"Looped List of Age Groups, order by GroupOrder"
      "Looped List of Apps, order by App Order"

 First Age Group
    Foo App Name
        Foo App Icon
        Foo App Store URL
    Bar App Name
        Bar App Icon
        Bar App Store URL
 Second Age Group
    Tur App Name
        Tur App Icon
        Tur App Store URL
    Puk App Name
        Puk App Icon
        Puk App Store URL
  and so on...

到目前为止，我的方法：

这是我的“ App.cs”类中的内容

public int Id { get; set; }
public string Name { get; set; }
public string StoreURL { get; set; }
public int AppOrder { get; set; }
public string AppIcon { get; set; }

public AgeGroup AgeGroup { get; set; }    // Linked to AgeGroup Class
public int AgeGroupId { get; set; }

在“ AgeGroup.cs”类中，我有以下内容

public int Id { get; set; }
public int GroupOrder { get; set; }
public string Name { get; set; }

并在AppsController.cs中

    [HttpGet]
    [Route("api/groups")]

    public IHttpActionResult AppsByGroup ()
    {
        var apps = _context.Apps
            .OrderBy(a => a.AppOrder)
            .Select(a => new
            {
                a.Name,
                a.StoreURL,
                a.AppIcon,
                AgeGroup = a.AgeGroup.Name
            }).GroupBy(a => a.AgeGroup);

       return Json(apps);
    }

它给我正确的输出，但没有AgeGroup Names。

[
  [ // Here I wanted the AgeGroup Name from the AgeGroup Table (Group A)
    {
        "Name": "First App for Group A",
        "StoreURL": "some string",
        "AppIcon": "icon string",
        "AgeGroup": "Group A"
    },
    {
        "Name": "2nd App Group A",
        "StoreURL": "aslfdj",
        "AppIcon": "asljf",
        "AgeGroup": "Group A"
    },
    {
        "Name": "3rd App Group A",
        "StoreURL": "aslfdj",
        "AppIcon": "alsfdj",
        "AgeGroup": "Group A"
    }
  ],
  [ // Here I wanted the AgeGroup Name from the AgeGroup Table (Group B)
    {
        "Name": "1st App GroupB",
        "StoreURL": "alsfdj",
        "AppIcon": "alsdjf",
        "AgeGroup": "Group B"
    },

//and so on...

最佳答案

我建议使代码更易于理解和调试，从视图模型开始代表要返回的数据

public class AgeGroupVM
{
    public string Name { get; set; }
    public IEnumerable<AppVM> Apps { get; set; }
}
public class AppVM
{
    public string Name { get; set; }
    public string StoreURL { get; set; }
    public string AppIcon { get; set; }
}

然后，您需要对数据进行排序并按Name的AgeGroup属性进行分组

var data = _context.Apps
    .OrderBy(x => x.AgeGroup.GroupOrder) // order by AgeGroup first
    .ThenBy(x => x.AppOrder) // then by the AppOrder
    .GroupBy(x => x.AgeGroup.Name) // group by the AgeGroup
    .Select(x => new AgeGroupVM // project into the view model
    {
        Name = x.Key,
        Apps = x.Select(y => new AppVM
        {
            Name = y.Name,
            StoreURL = y.StoreURL,
            AppIcon = y.AppIcon
        })
    });
return Json(data);