string linq to entities error - json

Question

I am trying to map various markers on google map with a info window. It is all working fine till I try to pass a string through the controller.

I get the error below:

"LINQ to Entities does not recognize the method 'System.String GetMainPhoto(Int32)' method, and this method cannot be translated into a store expression."

I have read this is mainly caused because ToString method or some other methods are not valid to use. But, I am not really sure how to correct this error in this case.

Basically, I have a db - PropertyPhoto that holds the filename of the pictures. GetMainPhoto basically looks up all the rows and returns the main pic file name.

public string GetMainPhoto(int id)
        {
            return db.PropertyPhotos.Single(p => p.PropertyId == id && p.MainPic == true).PhotoLocation;

        }

Controller is as follows:

public ActionResult Map()
        {
            if (Request.IsAjaxRequest())
            {
                var properties = websiteRepository.FindAllProperties();

                var jsonProperties = from property in properties
                                     select new JsonProperty
                                     {
                                         PropertyId = property.PropertyId,
                                         NoOfBedroom = property.NoOfBedrooms,
                                         Price = property.Price,
                                         Address1 = property.PropertyAddress.Address1,
                                         MainPicSrc = websiteRepository.GetMainPhoto(property.PropertyId),
                                         Latitude = property.PropertyAddress.Latitude,
                                         Longitude = property.PropertyAddress.Longitude
                                     };

                return Json(jsonProperties.ToList(), JsonRequestBehavior.AllowGet);
            }
            else 
            {
                return View();
            }
        }

score 2 · Accepted Answer · edited May 23 '17 at 11:43

Try to eagerly load the data first by calling .ToList() before projecting into an anonymous type, otherwise EF doesn't know how to translate the websiteRepository.GetMainPhoto call to SQL:

var properties = websiteRepository.FindAllProperties().ToList();

Be careful though. By doing this you might be experiencing the SELECT N+1 problem because for each element of the initial resultset you will be sending a SQL query to fetch the MainPicSrc property.

A better approach would be to perform this join directly by the database and don't use the websiteRepository.GetMainPhoto call.

string linq to entities error - json

1 Answers1