Not able to return JsonResult

问题:

The following query is working successfully.

var tabs = (
                from r in db.TabMasters
                orderby r.colID
                select new { r.colID, r.FirstName, r.LastName })
                .Skip(rows * (page - 1)).Take(rows);

Now I want to return JsonResult as like

var jsonData = new
            {
                total = (int)Math.Ceiling((float)totalRecords / (float)rows),
                page = page,
                records = totalRecords,
                rows = (from r in tabs
                        select new { id = r.colID, cell = new string[] { r.FirstName, r.LastName } }).ToArray()
            };
return Json(jsonData, JsonRequestBehavior.AllowGet);

But it will gives me an error like: The array type 'System.String[]' cannot be initialized in a query result. Consider using 'System.Collections.Generic.List`1[System.String]' instead.

What should I do to get expected result?

回答1:

I suspect that it's as simple as pushing the last part into an in-process query using AsEnumerable():

var jsonData = new
{
    total = (int)Math.Ceiling((float)totalRecords / (float)rows),
    page = page,
    records = totalRecords,
    rows = (from r in tabs.AsEnumerable()
            select new { id = r.colID,
                         cell = new[] { r.FirstName, r.LastName } }
           ).ToArray()
};
return Json(jsonData, JsonRequestBehavior.AllowGet);

You may want to pull that query out of the anonymous type initializer, for clarity:

var rows = tabs.AsEnumerable()
               .Select(r => new { id = r.colID,
                                  cell = new[] { r.FirstName, r.LastName })
               .ToArray();

var jsonData = new { 
    total = (int)Math.Ceiling((float)totalRecords / (float)rows),
    page,
    records = totalRecords,
    rows
};

回答2:

It's because it's adding to the LINQ query that is your tabs IQueryable. That is then trying to turn the LINQ expression into a SQL query and the provider doesn't support projecting arrays.

You can either change the assignment of the tabs variable's LINQ expression to use ToList to materialize the DB results right then and there, or you can add .AsEnumerable() to the LINQ expression assigned to the rows field of the anonymous type that is your JsonResult. AsEnumerable will demote the IQueryable to an IEnumerable which will prevent your second LINQ query from trying to be added to the DB query and just make it a LINQ-to-objects call like it needs to be.