LINQ to Entities Does Not Recognize Int32 Method

Question

public ActionResult ReadXMLDevices(int groupID) { var query = from k in XMLEntities.unassigneditems where k.DevOrAcc == true && k.Group == groupID select k; var view_query = from i in query select new GetFreeDevices { MArticleNumber = i.ArticleNumber, MFirmware = i.Firmware, MGroup = i.Group, MName = i.Name, MSoftware = i.SoftwareVersion, SA = GetNumberOfDevices(i.ArticleNumber,2), STH = GetNumberOfDevices(i.ArticleNumber,3), SASTH = GetNumberOfDevices(i.ArticleNumber,7) }; return PartialView(view_query); } public int GetNumberOfDevices(string artNo,int loc) { var num_dev = (from k in XMLEntities.deviceview where k.Reserved == false && k.Sold == false && k.LocationNameId == loc && k.ArticleNumber == artNo select k).Count(); return num_dev; } 

Mistake:

LINQ to Entities does not recognize the Int32 GetNumberOfDevices (System.String, Int32) 'method, and this method cannot be translated into a storage expression. How to solve this problem?

Answer 1

You can split the query in two to make sure the list is in memory before calling the GetNumberOfDevices() method. You can query in memory by converting the results to List or in this case a List<GetFreeDevices> . Thus, LinQ to Entities does not need to translate from and from XML, and you can use your GetNumberOfDevices() method.

 var view_query = (from i in query select new GetFreeDevices { MArticleNumber = i.ArticleNumber, MFirmware = i.Firmware, MGroup = i.Group, MName = i.Name, MSoftware = i.SoftwareVersion }).ToList(); var result_query = from i in query select new GetFreeDevices { MArticleNumber = i.MArticleNumber, MFirmware = i.MFirmware, MGroup = i.MGroup, MName = i.MName, MSoftware = i.MSoftware, SA = GetNumberOfDevices(i.MArticleNumber,2), STH = GetNumberOfDevices(i.MArticleNumber,3), SASTH = GetNumberOfDevices(i.MArticleNumber,7) }; return PartialView(result_query); 

Note that the last statement requires that PartialView accept a list or IEnumerable instead of IQueryable .

Answer 2

There is another easy way.

First load the data from the database into memory:

 // ... var query_view = from i in query select i; query_view.Load(); // ... 

Then do whatever you want with linq-to-objects, not L2E:

 var view_query_1 = from i in DbContext.myEntities.Local select new GetFreeDevices { MArticleNumber = i.ArticleNumber, MFirmware = i.Firmware, MGroup = i.Group, MName = i.Name, MSoftware = i.SoftwareVersion, SA = GetNumberOfDevices(i.ArticleNumber,2), // STH = GetNumberOfDevices(i.ArticleNumber,3), // These are now ok! SASTH = GetNumberOfDevices(i.ArticleNumber,7) // }; return PartialView(view_query_1.AsEnumerable()); 

You can use this trick for any methods that L2E does not support.