LINQ to Entities: Method cannot be translated into a store expression.

Question

I saw this code work with LINQ to SQL but when I use Entity Framework, it throws this error:

LINQ to Entities does not recognize the method 'System.Linq.IQueryable'1[MyProject.Models.CommunityFeatures] GetCommunityFeatures()' method, and this method cannot be translated into a store expression.

The repository code is this:

    public IQueryable<Models.Estate> GetEstates()
    {
        return from e in entity.Estates
               let AllCommFeat = GetCommunityFeatures()
               let AllHomeFeat = GetHomeFeatures()
               select new Models.Estate
                          {
                                    EstateId = e.EstateId,
                                    AllHomeFeatures = new LazyList<HomeFeatures>(AllHomeFeat),
                                    AllCommunityFeatures = new LazyList<CommunityFeatures>(AllCommFeat)
                          };
    }

    public IQueryable<Models.CommunityFeatures> GetCommunityFeatures()
    {
        return from f in entity.CommunityFeatures
               select new CommunityFeatures
                          {
                              Name = f.CommunityFeature1,
                              CommunityFeatureId = f.CommunityFeatureId
                          };
    }

    public IQueryable<Models.HomeFeatures> GetHomeFeatures()
    {
        return from f in entity.HomeFeatures
               select new HomeFeatures()
               {
                   Name = f.HomeFeature1,
                   HomeFeatureId = f.HomeFeatureId
               };
    }

LazyList is a List that extends the power of IQueryable.

Could someone explain why this error occurs?

Answer 1

Reason: By design, LINQ to Entities requires the whole LINQ query expression to be translated to a server query. Only a few uncorrelated subexpressions (expressions in the query that do not depend on the results from the server) are evaluated on the client before the query is translated. Arbitrary method invocations that do not have a known translation, like GetHomeFeatures() in this case, are not supported.
To be more specific, LINQ to Entities only support Parameterless constructors and Initializers.

Solution: Therefore, to get over this exception you need to merge your sub query into main one for GetCommunityFeatures() and GetHomeFeatures() instead of directly invoking methods from within the LINQ query. Also, there is an issue on the lines that you were trying to instantiate a new instance of LazyList using its parameterized constructors, just as you might have been doing in LINQ to SQL. For that the solution would be to switch to client evaluation of LINQ queries (LINQ to Objects). This will require to invoke AsEnumrable method for your LINQ to Entities queries prior to call the LazyList constructor.

Something like this should work:

public IQueryable GetEstates() {
    return from e in entity.Estates.AsEnumerable()
           let AllCommFeat = from f in entity.CommunityFeatures
                             select new CommunityFeatures {
                                 Name = f.CommunityFeature1,
                                 CommunityFeatureId = f.CommunityFeatureId
                             },
           let AllHomeFeat = from f in entity.HomeFeatures
                             select new HomeFeatures() {
                                 Name = f.HomeFeature1,
                                 HomeFeatureId = f.HomeFeatureId
                             },
           select new Models.Estate {
                EstateId = e.EstateId,
                AllHomeFeatures = new LazyList(AllHomeFeat),
                AllCommunityFeatures = new LazyList(AllCommFeat)
           };
}

More Info: Please take a look at LINQ to Entities, what is not supported? for more info. Also check out LINQ to Entities, Workarounds on what is not supported for a detailed discussion on the possible solutions.