c# - 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 the 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 you to invoke the AsEnumerable method for your LINQ to Entities queries prior to calling the LazyList constructor.

Something like this should work:

public IQueryable<Models.Estate> 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<HomeFeatures>(AllHomeFeat),
            AllCommunityFeatures = new LazyList<CommunityFeatures>(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. (Both links are the cached versions because the original website is down)