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  
}

Robert · Accepted Answer · 2015-12-30 13:59:32Z

4

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

answered Dec 30, 2015 at 13:59

Robert

1,04210 silver badges12 bronze badges

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

2 Comments

Kgn-web Over a year ago

you are saying about albums or singers to List()? Can you please add a bit of code-snippet..would be great

Robert Over a year ago

Since it's WebAPI just put all the singers into a List() and return that, Json.NET will take care of the rest

Alberto Monteiro · Accepted Answer · 2015-12-31 00:25:50Z

1

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"
      }
    ]
  }
]

edited Dec 31, 2015 at 0:25

answered Dec 30, 2015 at 14:18

Alberto Monteiro

6,2792 gold badges31 silver badges40 bronze badges

7 Comments

Kgn-web Over a year ago

,If I try to what you are saying I get an compile time error Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<AnonymousType#1>' to 'System.Collections.Generic.List<modelClass>

Kgn-web Over a year ago

can you please guide me what should I do to fix, if I do var query = (from c...) I don't get any error, but I don't want to do that I prefer to use IQueryable<MyMapperClass>

Alberto Monteiro Over a year ago

@Chetan I changed my answer, look now, it should fix your problem

Kgn-web Over a year ago

Many thanks mate..your ans has really address my requirment.. +100 for your ans

Alberto Monteiro Over a year ago

@Chetan I am really happy that I help you, if you need more thing, just ask, I will love help you again!!!

|

Collectives™ on Stack Overflow

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

But my need is to form JSON string as below

2 Answers 2

2 Comments

Using lambda

You have to modify your map classes to fit this:

Using linq syntax

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

But my need is to form JSON string as below

2 Answers 2

2 Comments

Using lambda

You have to modify your map classes to fit this:

Using linq syntax

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related