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

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.

Wish I could explain this succinctly... How much do you already know about the IQueryable interface, deferred execution, and expression trees? — John Farrell, Aug 20 '10 at 23:04

Ian Mercer · Accepted Answer · 2014-04-17T21:14:14.493

14

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.

edited Apr 17 '14 at 21:14

answered Aug 21 '10 at 06:15

Ian Mercer

38,490
8
97
133

I Already have a FK between group and text. . I want to make a subquery with a left join with my Language Table. I want the list of each group text in different lang. If the group text is not traduct for a specific language, I want the result as null. – Jean-Francois Aug 21 '10 at 17:06
7

I'd advise against calling .ToList() on your database table, that is unless you want to load the whole table in memory. – Doguhan Uluca Feb 28 '13 at 16:04
@DoguhanUluca - So what is the alternative to calling .ToList() in a situation like this? – oonyalo Apr 17 '14 at 18:00
In this case the correct alternative is the one I proposed - use an FK relationship so the texts can be lazily (or eagerly) loaded. Sometimes `ToList()` is fine if you know it's a small table but usually, as @DoguhanUluca points out it's the wrong thing to do. You might also be able to project to an anonymous type as this can be translated into a SELECT that returns just some of the columns in the database but you will still have to convert that to a non-anonymous type at some point in order to return it from your method. – Ian Mercer Apr 17 '14 at 21:11
@oonyalo Lately I've been creating views that I later use with LINQ to avoid overly complicated LINQ statements that really don't work well. Any operation that'd require a .ToList() like a Distinct, which also requires dynamic LINQ manipulation in code later, then I recommend using a database view. – Doguhan Uluca Apr 18 '14 at 01:41

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

1 Answers1

Linked