LINQ to Entities Does Not Recognize System.Linq.IQueryable Method

Question

When I try to call my repository in Sub Select, I got this error.

IGrpTextRepository rep = new GrpTextRepository(); var query = new DetailViewModel { ViewDet = (from gh in _db.Grp select new MultiDetailViewModel { Header = gh, Txts = rep.FindAllLangTxtById(gh.GrpID) }).ToList(), Lang = _db.Language.ToList(), }; 

My interface

  public interface IGrpTextRepository { IQueryable<GrpText> FindAllLangTxtById(int GrpID); } public class GrpTextRepository : IGrpTextRepository { DBEntities db = new DBEntities(); public IQueryable<GrpText> FindAllLangTxtById(int GrpID) { return (from lang in db.Language join gtxts in db.GrpText on lang.LangID equals gtxts.LangID into jointxt from fintxt in jointxt.DefaultIfEmpty() where fintxt.GrpID == GrpID select fintxt); } } 

Here is the complete error message

System.NotSupportedException: LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1 [aaa.Models.GrpText] FindAllLangTxtById (Int32)', and this method cannot be translated into a storage expression.

Answer 1

A simple solution is to add .ToList () before your choice (...).

  ViewDet = _db.Grp.ToList().Select(gh => new MultiDetailViewModel ...) 

Thus, the initial query will go to the database, return with the results, and you can then reprogram them using the select statement for Linq for objects.

But I have to ask why there is no FK relationship between groups and texts and therefore there is an association of entities between the two, allowing you to simply access gh.GrpText and get into a collection of texts lazily loaded (or eagerly loaded using .Include () )

As @Doguhan Uluca notes in the comments, using ToList() is risky as it will cause everything in this table to be fetched into memory. You should use it only in very small collections. The right approach is to fix your database design so that you can query it efficiently.