c# - LINQ to Entities does not recognize the method 'System.Web.Mvc.FileResult'

Question

I am trying to display multiple username with their image So, I have a Json action method like this:

public JsonResult GetUsers()
{
     var ret = (from user in db.Users
                orderby user.UserName
                select new
                {
                    UserName = user.UserName,
                    Pic = GetFileData(user.Id),
                }).AsEnumerable();
     return Json(ret, JsonRequestBehavior.AllowGet);
}

And this is my GetFileData method to get user's image:

public FileResult GetFileData(int Id)
{
    var avatarImage = db.Files.SingleOrDefault(s => s.ApplicationUserId == Id);
    return File(avatarImage.Content, avatarImage.ContentType);
}

Here, ApplicationUserId is the foreign key relating File and ApplicationUser class.

Now, when I run the query, I should get username with their pic But I am getting System.NotSupportedException.The complete error message is:

LINQ to Entities does not recognize the method 'System.Web.Mvc.FileResult GetFileData(Int32)' method, and this method cannot be translated into a store expression.

How to get it resolved.I have seen many stackoverflow question relating it but none of the question was relating to FileResult.The code for view page is here http://pastebin.com/AyrpGgEm

Answer 1

GetFileData couldn't be translated to T-SQL, Linq to Entities couldn't recognize it. You can modify the code as below (Move the GetFileData out of expression):

var pic = GetFileData(user.Id);

public JsonResult GetUsers()
{
    var ret = (from user in db.Users
               orderby user.UserName
               select new
               {
                   UserName = user.UserName,
                   Pic = pic,
               }).AsEnumerable();
    return Json(ret, JsonRequestBehavior.AllowGet);
}

But because user does not exist outside the query you should use .ToList() to defer the use of your function. With .ToList() after data is loaded, any further operation (such as select) is performed using Linq to Objects, on the data already in memory. So your query should be like this:

public JsonResult GetUsers()
{
    var ret = (from user in db.Users.ToList()
               orderby user.UserName
               select new
               {
                   UserName = user.UserName,
                   Pic = GetFileData(user.Id),
               }).AsEnumerable();
    return Json(ret, JsonRequestBehavior.AllowGet);
}