Retrieving Data from LINQ to SQL in JSON format?

Question

I have a problem with Serialization of Data from database to JSON format. I'm using WebAPI 2 and Entity Framework 6. I've created with EF a Model. The database and the tables with content are even created. When I use the code below I'm getting an error when I type http://localhost:54149/api/qr_group .

Controller:

private EfContext db = new EfContext();

// GET api/<controller>
public IEnumerable<QR_Group> GetGroups()
{
    var result = from g in db.QR_Groups
                 select g;
    return result.ToList();
}

I don't know how to use Newtonsoft.Json to serialize the table with the content in JSON format.

I've tried the following code instead of the code above:

public IQueryable<QR_Group> GetGroups()
{
  var groupList = (from g in db.QR_Groups
                   select new
                   {
                     name = g.name,
                     code = g.code
                   }).AsQueryable();

  var json = JsonConvert.SerializeObject(groupList);

  return json; //error CS0029: Cannot implicitly convert type `string' to `System.Linq.IQueryable<RestApi.Models.QR_Group>'
}

I get the error CS0029.. How can I solve this to return the data in json? As reminder: The QR_Group entity has 3 columns (Id, name, code)

Answer 1

Specifically to your second function, JsonConvert.SerializeObject would just serialize any object into a JSON format string, which means you should return a string instead of an IQueryable<>.

So for the controller there are quite a few ways to return it back, like: In MVC, how do I return a string result?

EDIT:

Following code would be one way that should working:

Controller:

private EfContext db = new EfContext();

// GET api/<controller>
public ActionResult GetGroups()
{
    var groupList = (from g in db.QR_Groups
    select new
    {
        name = g.name,
        code = g.code
    }).AsQueryable();

    return Content(JsonConvert.SerializeObject(groupList));
}

Answer 2

you didn't mention the error when you are returning list from the api controller. as i assume that you are returning a database entity that in simple case not allow you as json result, because your entity may be linked with other entities in the database.

you can check this link for reference

Now modify your code to something like below. It is just to make you understand.

Define a Type say QR_Group_Return

public class QR_Group_Return{

    public string name{ get; set;}
    public string code{ get; set;}
    // Some other properties will go here.             
}

Now instead of returning IEnumerable<QR_Group> we will return IEnumerable<QR_Group_Return>

private EfContext db = new EfContext();

// GET api/<controller>
public IEnumerable<QR_Group_Return> GetGroups()
{
    var result = (from g in db.QR_Groups
                 select new QR_Group_Return {
                           name = g.name,
                           code = g.code,
                           // map your other properties also
                           }).ToList();
    return result;
}

Hope this helps