Problem with Json

Question

Im having this weird problem with creating Json dynamically.... for some reason this doesnt work

var jsonData = new
        {
            total = totalPages,
            page = page,
            records = totalRecords,
            rows = (
                from company in companies
                select new
                {
                    i = company.Id,
                    cell = new string[] { company.Id.ToString(), "<a href=\""+Url.Action("Edit",new{id=company.Id})+"\">"+company.Name.ToString()+"</a>" }
                }).ToArray()
        };

Its giving me a weird "Could not translate expression....... into sql" exception

but with this minor change it works just fine

var jsonData = new
            {
                total = totalPages,
                page = page,
                records = totalRecords,
                rows = (
                    from company in companies
                    select new
                    {
                        i = company.Id,
                        cell = new string[] { company.Id.ToString(), "<a href=\""+Url.Action("Edit",new{id=5})+"\">"+company.Name.ToString()+"</a>" }
                    }).ToArray()
            };

Notice that the change is just to make id=5 instead of dynamic.

also, this works correctly but I dont like it.

var jsonData = new
        {
            total = totalPages,
            page = page,
            records = totalRecords,
            rows = (
                from company in companies
                select new
                {
                    i = company.Id,
                    cell = new string[] { company.Id.ToString(), "<a href=\"Edit/"+company.Id+"\">"+company.Name.ToString()+"</a>" }
                }).ToArray()
        };

Answer 1

It's trying to translate the i to SQL and failing. The query that gets generated is something close to

SELECT companies.Id,
    '<a href=\"Edit/' + companies.Id + '\">' + companies.Name + '</a>'
FROM companies

Basically, your string concatenation is being sent to your SQL server.

Answer 2

I'm not sure if this will solve your problem, but assuming companies is an IQueryable from the DataContext, try calling ToList() on it so that the select expression doesn't get sent to the database.

var jsonData = new
{
 total = totalPages,
 page = page,
 records = totalRecords,
 rows = (
  from company in companies.ToList()
  select new
  {
   i = company.Id,
   cell = new string[] { company.Id.ToString(), "<a href=\""+Url.Action("Edit",new{id=company.Id})+"\">"+company.Name.ToString()+"</a>" }
  }).ToArray()
};