How to get data in string array or JSON format using linq to entities?

Question

I have an ASP.Net MVC5 site and using EF 6.0

One to Many relationship

Here are my models

public class Singer
{
    [Key]
    public int SingerID { get; set; }

    public string SingerName { get; set; }

    public virtual List<Album> Albums { get; set; }
}

public class Album
{
    [Key]
    public int AlbumID { get; set; }

    public string AlbumName { get; set; }

    public string AlbumDate { get; set; }

    [ForeignKey("Singer")]
    public int SingerID { get; set; }

    public virtual Singer Singer { get; set; }
}

Now my Linq is as below

public IEnumerable<T> GetAlbums()
{
    using (dbContext db = new dbContext())
    {
        IQueryable<T> query = (from c in db.Albums
                               group c.AlbumId by c.SingerId into albums
                               select new AlbumMapper()
                               {
                                   AlbumID = albums.Key,
                                   Total = albums.Count()
                               })
        }
}

In the current scenario I get all the albums grouped by albumId and the count of the albums.

But my need is to form JSON string as below

[
   {
      "SingerID":1,
      "Albums":[
         {
            "AlbumName":"This is Album 1",
            "AlbumDate":"Dec 30,2015"
         },
         {
            "AlbumName":"This is Album 2",
            "AlbumDate":"Dec 30 2015"
         }
      ]
   },
   {
      "SingerID":2,
      "Albums":[
         {
            "AlbumName":"This is Album 1",
            "AlbumDate":"Dec 30,2015"
         },
         {
            "AlbumName":"This is Album 2",
            "AlbumDate":"Dec 30 2015"
         }
      ]
   }
]

Adding Mapper Classes

public class AlbumDetails
{
   public DateTIme AlbumDate

   public string AlbumName

}

public class AlbumMapper
{
    public int AlbumID

    public IEnumerable<AlbumDetails> Albums  
}

Answer 1

Just put all the Singers into a list and serialize it using Json.NET (http://www.newtonsoft.com/json)

If you want to leave out SingerName be sure to add a [JsonIgnore] data attribute to the property

Answer 2

Then you want just this, combination of GroupBy with Select using anonymous object.

Using lambda

public IEnumerable<AlbumMapper> GetAlbums()
{
    using(dbContext db = new dbContext())
    { 
        return db.Albums.GroupBy(a => a.SingerID)
                        .Select(g => new AlbumMapper
                        {
                            SingerID = g.Key,
                            Albums = g.Select(a => new AlbumDetails { AlbumName = a.AlbumName, AlbumDate = a.AlbumDate })
                        });
    }
}

You have to modify your map classes to fit this:

public class AlbumDetails
{
   public DateTime AlbumDate { get; set; }

   public string AlbumName { get; set; }
}

public class AlbumMapper
{
    public int SingerID { get; set; }

    public IEnumerable<AlbumDetails> Albums { get; set; }
}

Using linq syntax

public IEnumerable<T> GetAlbums()
{
    using(dbContext db = new dbContext())
    { 
        return from a in db.Albums
               group a by a.SingerID into albums
               select new AlbumMapper
               {
                    SingerID = albums.Key,
                    Albums = albums.Select(album => new AlbumDetails { AlbumName = album.AlbumName, AlbumDate = album.AlbumDate })
               };
    }
}

With this sample data:

var albums = new List<Album>();
var singer = new Singer(1, "Singer 1");
var singer2 = new Singer(2, "Singer 2");

albums.Add(new Album(1,"This is Album 1", "Dec 30,2015", singer));
albums.Add(new Album(2,"This is Album 2", "Dec 30,2015", singer));

albums.Add(new Album(1,"This is Album 1", "Dec 30,2015", singer2));
albums.Add(new Album(2,"This is Album 2", "Dec 30,2015", singer2));

The result of this

albums.GroupBy(a => a.SingerID)
        .Select(g => new 
        {
            SingerID = g.Key,
            Albums = g.Select(a => new { a.AlbumName, a.AlbumDate })
        }) 

Is

[
  {
    "SingerID": 1,
    "Albums": [
      {
        "AlbumName": "This is Album 1",
        "AlbumDate": "Dec 30,2015"
      },
      {
        "AlbumName": "This is Album 2",
        "AlbumDate": "Dec 30,2015"
      }
    ]
  },
  {
    "SingerID": 2,
    "Albums": [
      {
        "AlbumName": "This is Album 1",
        "AlbumDate": "Dec 30,2015"
      },
      {
        "AlbumName": "This is Album 2",
        "AlbumDate": "Dec 30,2015"
      }
    ]
  }
]