当尝试使用Linq获取数据时，“无法在查询结果中初始化”错误

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?

我有一个asp.net mvc 3的应用，我正在使用EF 4.1并尝试jqGrid。我正在尝试使用下面的GridData方法获取jqGrid的数据。在以'var jsonData = new…'开头的数据组上，我得到以下错误。什么好主意吗?

ERROR:

错误:

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

{“数组类型的系统。字符串[]'不能在查询结果中初始化。考虑使用System.Collections.Generic.List 1(系统。字符串]“相反”。}

GridData Method:

GridData方法:

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

4 个解决方案

#1

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 = question的用法。id代替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.

我建议您阅读答案并从包含更多最新和扩展代码的答案中下载演示。

#2

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

#3

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)

因为MSDN说:“不能直接调用这个函数。这个函数只能出现在LINQ到实体查询中。(虽然下一行在文档中有点混乱)

#4

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.

这应该工作。

#1