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)

I don't know whether it can convert IQueryable to json string. try using .ToList() instead of AsQueryable() in the second function and also return string instead of IQueryable. — Tanzeel ur Rehman, Jun 29 '15 at 22:24
@Tanzeel .ToList() is not supported in IQueryable.. only in IEnumerable. But this I have also tried. But the problem is remained. — yuro, Jun 29 '15 at 22:25

score 5 · Answer 1 · edited May 23 '17 at 12:14

5

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

edited May 23 '17 at 12:14

Community

1
1

answered Jun 29 '15 at 22:17

Simon Wang

2,843
1
16
32

How can I solve my problem? Your link doesn't help me :( – yuro Jun 29 '15 at 22:26
The link is just about how to return a string as a controller, I've updated my answer by putting some code I think would work. – Simon Wang Jun 29 '15 at 22:48

score 1 · Answer 2 · edited May 23 '17 at 11:58

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

Retrieving Data from LINQ to SQL in JSON format?

2 Answers2