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

What is the definition of not working? Can you share some sample data and expected output? What output you are getting with current code? — Chetan
– Chetan, Commented Nov 4, 2017 at 8:42
Not Working = Json response is empty. I'm clarifying my question further. — Naveed Abbas
– Naveed Abbas, Commented Nov 4, 2017 at 8:49
You should be using a .GroupBy() linq query (not nested loops in a view) and a view model to represent that data. Are you wanting a method that returns a JsonResult, and how are you using that in your view (e.g. making an ajax call)? — user3559349
– user3559349, Commented Nov 6, 2017 at 21:44
I think you would originally have a many-to-many relation but you already redesigned it to one-to-many by creating duplicate entries for the same app in different groups. If not, it would help if you don't name two entries as App 1 for Group XYZ — grek40
– grek40, Commented Nov 7, 2017 at 7:01

Naveed Abbas · Accepted Answer · 2017-11-13 08:18:17Z

3

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.

edited Nov 13, 2017 at 8:18

Naveed Abbas

1,1971 gold badge14 silver badges38 bronze badges

answered Nov 8, 2017 at 14:16

bgarcia

511 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Naveed Abbas Over a year ago

It gave me the closest result. The only unwanted thing is the word "App" in every header instead of AppName but I'm good with it.

score 2 · Accepted Answer · 2018-02-17 05:06:32Z

2

+50

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

edited Feb 17, 2018 at 5:06

answered Nov 13, 2017 at 8:16

user3559349

9 Comments

Naveed Abbas Over a year ago

SIR.... A bundle of thanks. It worked. Since I'm new in this, it will take time for me to understand the viewmodels technique completely and use it efficiently in practical. So far, it looks flawless.

Naveed Abbas Over a year ago

BTW, OrderByGroup was intended for the groups to come in specific order. Here apps are ordered by GroupOrder.

user3559349 Over a year ago

I just went on the first comment in your code Looped List of Age Groups, order by GroupOrder, Looped List of Apps, order by App Order which is what the code above does

Naveed Abbas Over a year ago

LIST of AGE GROUPS, Order by GroupOrder is exactly what I meant but the code is showing List of Apps OrderBy GroupOrder and ThenBy AppOrder. Currently the answer below is what I have to use.

Naveed Abbas Over a year ago

But since your approach is much neater, cleaner and easy to debug, I'd prefer to find a way to make use of it.

|

Leibowitz · Accepted Answer · 2017-11-06 11:57:51Z

1

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.

edited Nov 6, 2017 at 11:57

answered Nov 4, 2017 at 19:16

Leibowitz

113 bronze badges

4 Comments

Naveed Abbas Over a year ago

Thanks for your help but for some reason it is showing me two errors. First in the .Where(ageGroup => ageGroup.Name == name) is showing me an error "The name "name" does not exist in the current context". And the foreach statement can not operate on variable of type '?' because...it doesn't contain public definition of GetEnumerator"

Naveed Abbas Over a year ago

Basically the errors get fixed if I delete the line with .Where clause.

Leibowitz Over a year ago

Then if you don't want to filter AgeGroups by their name you can just delete this line of code.

Naveed Abbas Over a year ago

Thanks a lot for your help but I'm bothering you again. You clearly understood the problem (One to many relationships) but probably I couldn't explain properly. I have rephrased the question completely.

DLL_Whisperer · Accepted Answer · 2017-11-09 07:28:18Z

1

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();

answered Nov 9, 2017 at 7:28

DLL_Whisperer

8379 silver badges22 bronze badges

1 Comment

Naveed Abbas Over a year ago

I just saw your answer but nope. It didn't work either.

Ahmad Khan · Accepted Answer · 2017-11-07 09:48:16Z

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 :)

Collectives™ on Stack Overflow

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

5 Answers 5

1 Comment

9 Comments

4 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

9 Comments

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related