Error 'cannot be initialized in a query result' when trying to get data with Linq

Question

ISSUE:

I have an asp.net mvc 3 app. I'm using EF 4.1 and trying out jqGrid. I'm trying to get data for my jqGrid using the GridData method below. I get the following error on the group of data starting at 'var jsonData = new...'. Any ideas?

ERROR:

{"The array type 'System.String[]' cannot be initialized in a query result. Consider using 'System.Collections.Generic.List`1[System.String]' instead."}

GridData Method:

    public JsonResult GridData(string sidx, string sord, int page, int rows)
    {                        
        var result = from a in db.t_harvest_statistics_elk
                     where a.year == "2008" && a.unit_number == 1
                     orderby a.id
                     select a;


        int pageIndex = Convert.ToInt32(page) - 1;
        int pageSize = rows;
        int totalRecords = result.Count();   // context.Questions.Count();
        int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);

        var questions = result.Skip(pageIndex * pageSize).Take(pageSize);

        var jsonData = new
        {
            total = totalPages,
            page,
            records = totalRecords,
            rows = (
                from question in questions
                select new
                {
                    i = question.id,                                                
                    cell = new string[] { SqlFunctions.StringConvert((double)question.id), SqlFunctions.StringConvert((double)question.total_rec_days), question.year }

                }).ToArray()
        };

        return Json(jsonData);
    }

HERE IS AN EXAMPLE THAT DOES WORK

public JsonResult DynamicGridData(string sidx, string sord, int page, int rows)
    {
        var context = new HaackOverflowDataContext();
        int pageIndex = Convert.ToInt32(page) - 1;
        int pageSize = rows;
        int totalRecords = context.Questions.Count();
        int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);

        var questions = context.Questions.OrderBy(sidx + " " + sord).Skip(pageIndex * pageSize).Take(pageSize);

        var jsonData = new
        {
            total = totalPages,
            page,
            records = totalRecords,
            rows = (
                from question in questions
                select new
                {
                    i = question.Id,
                    cell = new string[] { question.Id.ToString(), question.Votes.ToString(), question.Title }
                }).ToArray()
        };
        return Json(jsonData);
    }

score 1 · Answer 1 · edited May 16 '12 at 17:03

var jsonData = new {
    total = totalPages,
    page,
    records = totalRecords,
    rows = (
        from question in queryDetails
        select new
        {
            id = question.Id,
            cell = new IComparable[]{

                question.Id.ToString(),
                question.total_rec_days.ToString(),
                question.year.ToString()
            }
        }).ToArray()
};
return Json(jsonData, JsonRequestBehavior.AllowGet);

score 1 · Accepted Answer · edited May 23 '17 at 11:52

The easiest way to fix the code will be to use something like the following

// to be able to use ToString() below which is NOT exist in the LINQ to Entity
// so we should get the data from the database and save the result locally before
// the next step. One can use .ToList() or to .AsEnumerable(), but one should
// choose only the fields of questions which we will need later
var queryDetails = (from item in questions
                    select new { item.id, item.total_rec_days, item.year }).ToList();
var jsonData = new {
        total = totalPages,
        page,
        records = totalRecords,
        rows = (
            from question in queryDetails
            select new
            {
                id = question.Id,
                cell = new [] {
                    question.Id.ToString(),
                    question.total_rec_days.ToString(),
                    question.year.ToString()
                }
            }).ToArray()
    };
return Json(jsonData, JsonRequestBehavior.AllowGet);

Your current code contain some small problems like the usage of i = question.id instead of id = question.id.

I would recommend you to read the answer and download the demo from the answer which contains more recent and extended code.

I had to use 'queryDetails.AsEnumerable()' to make the above example work. It does work thanks. — KeelRisk, Mar 03 '12 at 21:44
@KeelRisk: You are welcome! I didn't tested the code which I wrote :-) — Oleg, Mar 03 '12 at 21:51
My bad...you had it correct!! You do not need AsEnumerable(). — KeelRisk, Mar 03 '12 at 22:19
@KeelRisk: OK, no problem. I think the both ways will produce the same results. I used `ToList()` to show, that from the point we have real data and not only the SELECT statement typical for `IQuerable` or `AsQueryable`. — Oleg, Mar 03 '12 at 22:31

score 0 · Answer 3 · answered Mar 03 '12 at 18:49

Can you try modifying your code like :

rows = ( 
                from question in questions.AsEnumerable() //AsEnumerable() is added to switch to LINQ to Entites to eager load the data.
                select new 
                { 
                    i = question.id,                                                 
                    cell = new string[] { SqlFunctions.StringConvert((double)question.id), SqlFunctions.StringConvert((double)question.total_rec_days), question.year } 

                }).ToArray()

Because the MSDN says that : "You cannot call this function directly. This function can only appear within a LINQ to Entities query." (Though the next line is little confusing in documentation)

`questions.AsEnumerable()` is nice short, but it will produce `SELECT * FROM Questions` instead of `SELECT id, total_rec_days, year FROM Questions`. So it's better to use `(from item in questions select new { item.id, item.total_rec_days, item.year }).AsEnumerable()` or just simple `(from item in questions select new { item.id, item.total_rec_days, item.year }).ToList()` instead of `questions.AsEnumerable()`. — Oleg, Mar 03 '12 at 21:00

score 0 · Answer 4 · answered Mar 03 '12 at 20:06

0

You can't use custom functions inside direct queries to you database. Instead you can do something like this:

rows = questions.AsEnumerable() 
//or select just that you want  questions.Select(q=> new {g.Id, q.Votes,q.Title})
       .Select(p=> new {
                         id = p.Id,
                         cell = new string[] { SqlFunctions.StringConvert((double)p.id), SqlFunctions.StringConvert((double)p.total_rec_days), p.year }
                }).ToArray()

That should work.

answered Mar 03 '12 at 20:06

Matija Grcic

12,963
6
62
90

`questions.AsEnumerable()` is nice short, but it will produce `SELECT * FROM Questions` instead of `SELECT id, total_rec_days, year FROM Questions`. So it's better to use `(from item in questions select new { item.id, item.total_rec_days, item.year }).AsEnumerable()` or just simple `(from item in questions select new { item.id, item.total_rec_days, item.year }).ToList()` instead of `questions.AsEnumerable()`. – Oleg Mar 03 '12 at 20:59
Did you even read my answer, in the comment i said "or select just what you want questions.Select(q=> new {g.Id, q.Votes,q.Title})" meaning he can first select what he needs than use AsEnumerable() and the other code on this selection. – Matija Grcic Mar 03 '12 at 22:41
OK, I understood you in another way. Just one `AsEnumerable` *or* `questions.Select(q=> new {g.Id, q.Votes,q.Title})` is not enough. One should first `questions.Select(q=> new {g.Id, q.Votes,q.Title})`, then call `AsEnumerable()` and then use `Select` one more time where one can use `ToString()`. I think that one will can't able to use `SqlFunctions.StringConvert` which works with LINQ to Entities queries, but not with `IEnumerable`. – Oleg Mar 03 '12 at 22:50
Read the comment please!! rows = questions.Select(p=> new {p.Id, p.total_rec_days,p.year}).AsEnumerable() .Select(p=> new { id = p.Id, cell = new string[] { SqlFunctions.StringConvert((double)p.id), SqlFunctions.StringConvert((double)p.total_rec_days), p.year } }).ToArray() – Matija Grcic Mar 03 '12 at 22:53

Error 'cannot be initialized in a query result' when trying to get data with Linq

4 Answers4