LINQ to Entities does not recognize the method 'System.Linq.IQueryable [duplicate]

Question

When I try to call my repository in a 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 is

 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 full Error Message
System.NotSupportedException: LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[aaa.Models.GrpText] FindAllLangTxtById(Int32)' method, and this method cannot be translated into a store expression.

Answer 1

The simple solution is to add a .ToList() before your select(...).

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

That way the initial request will go to the database, come back with the results and you can then reproject them using a select statement against Linq to objects.

But I have to ask why there isn't an FK relationship between the groups and the texts and thus an Entity Association between the two allowing you to just access gh.GrpText and get to the collection of texts lazily loaded (or eagerly loaded using .Include()).

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