ASP.NET LINQ query for filter and loop through multiple tables

Question

I have two separate domain model classes for "App" and "AgeGroup". The App Class is containing few basic integer and strings properties and so does the AgeGroup class.

What I'm trying to achieve is JSON output of all the apps by AppOrder with their properties, nested in their associated AgeGroups that are ordered by their GroupOrder property

Required Example JSON Output Structure

"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...

My Approach so far:

Here is what I have in my "App.cs" Class

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; }

In the "AgeGroup.cs" Class, I have following

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

and in the 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);
    }

It gives me correct output but without 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...

Answer 1

I suggest to make the code a bit easier to understand and debug, you start with view models to represent the data you want to return

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; }
}

Then you need to order the data and group it by the Name property of 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);

Answer 2

Try this

public IHttpActionResult AppsByGroup() {

   var apps = _context.Apps.OrderBy(a => a.AppOrder);
   var groups = _context.AgeGroups.OrderBy(g => g.GroupOrder);

   var result = groups.Select(x => new {
         AgeGroupName = x.Name,
         Apps = apps
           .Where(y => x.Id == y.AgeGroupId)
           .Select(y => new {
              AppName = y.Name,
              AppIcon = y.AppIcon,
              StoreURL = y.StoreURL
            })
         });

   return Json(result);
}

It gives the following output

[
{
    "AgeGroupName": "Group C",
    "Apps": [
        {
            "AppName": "C Group App",
            "AppIcon": "Some string",
            "StoreURL": "64"
        }
    ]
},
{
    "AgeGroupName": "Group A",
    "Apps": [
        {
            "AppName": "App 2nd for Group AA",
            "AppIcon": "Some string",
            "StoreURL": "654"
        },
        {
            "AppName": "App 1 for Group A",
            "AppIcon": "Some string",
            "StoreURL": "654"
        }
    ]
},
{
    "AgeGroupName": "Group B",
    "Apps": [
        {
            "AppName": "App 1 for Group B",
            "AppIcon": "Some string",
            "StoreURL": "31"
        }
    ]
}
]

Hope this will help you.

Answer 3

I had to use _context.Apps because dont have the Apps navigation collection in your AgeGroup class. But this should be working.

_context.AgeGroups 
.OrderBy(t=> GroupOrder)
.Select(t=> new AppFormViewModel 
{
      App = t,
      AgeGroup = _context.Apps.Where(x=> c.AgeGroupId == t.Id)
      .Select(x=> new 
      {
          AppIcon = x.AppIcon,
          AppStoreURL = x.StoreURL
      }).AsEnumerable()
}.ToList();

Answer 4

This is a classical scenario of two classes having association relationship between each other.Please go through the definition of association aggregation and composition so that you get a clear idea behind creating your classes in future.Coming to your problem the way I will solve it is first I will start making its skeleton.

public class AgeGroup 
{
    //Age Group Class 
    [JsonProperty("id")]
    public Guid? Id { get; set; }
    [Jsonproperty("AgeGroup")]
    public string AgeGroupName { get; set; }
    [JsonProperty("App")]
    public List<Appclass> app { get; set; }

}

public class Appclass 
{
    [JsonProperty("id")]
    public Guid? Id { get; set; }
    [Jsonproperty("AppName")]
    public string AppName { get; set; }
}

string yourjsonMethod(List<AgeGroup> ageGroup)
{
    string json=JsonConvert.SerializeObject(agegroup);
    return json;          
}

List<AgeGroup> yourmethod2(string json){
    List<AgeGroup> list=JsonConvert.DeserializeObject<List<AgeGroup>>(json);
    return list;
}

Hope this helps :)

Answer 5

It looks like you have one to many relationship (each AgeGroup contains many Apps, but each App can have only one AgeGroup). So you can change your AgeGroup class to something like this:

class AgeGroup {
 public ICollection<Apps> Apps { get; set; }
 // your other properties here

  public AgeGroup() {
   Apps = new Collection<Apps>();
 }
}

Add AgeGroup to DbContext(if you didn't do it before) and then in your AppsController you can do this:

public ActionResult GetAgeGroupsWithApps() {
 var ageGroups = _context.AgeGroups
     .Include(ageGroup => ageGroup.Apps)
     .OrderBy(ageGroup => ageGroup.Id)
     .ToList();

 foreach(var ageGroup in ageGroups) {
  foreach(var app in ageGroup.Apps) {
   app.AgeGroup = null;
  }
 }

 return Json(ageGroups);
}

Firstly we include AgeGroups Apps collection and order them by their IDs.

The interesting part is that now we have list of AgeGroups, each AgeGroup has a collection of App objects and each App object has a reference to AgeGroup.

If you try to serialize it to JSON, serializer will get stuck in a loop endlessly following the references between the objects. To avoid this we iterate through Apps in each AgeGroup and for every App object we set AgeGroup reference to null.

When you do that you can serialize ageGroups collection without any problems. It will return JSON array of AgeGroup objects with nested associated Apps.

Hope this will help you.