Method Cannot Be Translated into a Store Expression

Method cannot be translated into a store expression

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)

LINQ to Entities - method cannot be translated into a store expression

You need to use "LINQ to Objects" to perform string.Format or interpolated strings by using AsEnumerable() or ToList() before using Select:

var chwWorker = (from c in db.PatientContacts
                where c.UserName == userFN &&
                     (c.PCP_Status == "Appointment Made" || c.PCP_Status_AWC == "Appointment Made"
                      || c.PCP_Status_AWDV == "Appointment Made" ||
                     (c.PCP_Status == "RX for Mamogram" && c.Status == "Appointment Made")) 
                orderby c.PCP_Status_Date descending select c) 
                .AsEnumerable() // or 'ToList()'
                .Select(c => new 
                {
                     Id = c.MemberID,
                     Name = c.PatientFirstName + " " + c.PatientLastName, 
                     PCP_Appt = $"{c.PCP_Status_Date:d}",
                     Mammogram_Appt = $"{c.StatusDate:d}",
                     Phone = GetPhone(c.MemberID)
                });

Note that string.Format method is not recognized by LINQ to Entities to translate it as an SQL command, hence query result materialization to memory is necessary.

NB: You can use SqlFunctions.StringConvert if you still want LINQ to Entities query before using Any() method, but not all SQL providers able to translate it into SQL statement.

Related issue:

LINQ to Entities does not recognize the method 'System.String Format

Linq: method cannot be translated into a store expression

You need to call GetUserId() outside the lambda expression, as Linq to Entities is not able to translate it to corresponding SQL (of course there is no SQL alternative for GetUserId()):

var userId = User.Identity.GetUserId();
var groupUser = db.GroupUsers.Where(u => u.ApplicationUserId == userId)
                             .Select(gr => gr.GroupId);

Method cannot be translated into a store expression (LINQ)

Decrypt the password outside the query:

var p=EncryptDecrypt.Decrypt(model.Password, "a15s8f5s6e2s3g1w5");
using (var db = new Entities())
{
            var erg = from s in db.Employees
                      where s.LastName == model.UserName && s.Password == p
                      select s;
            slogin = erg.FirstOrDefault();

}

An easier way:

using (var db = new Entities())
{
  slogin = db.Employees.FirstOrDefault(s=>s.LastName == model.UserName && s.Password == p);
}

Method cannot be translated into a store expression syntax error

Since hash value should be computed on the .NET side, you can add AsEnumerable() call after the portion that produces the "raw data", and then compute the rest outside your RDBMS, like this:

var rawStrings = from keys in keyTable
                join values in valuesTable
                on keys.ID equals values.FK_TableKey
                select new {
                    Value = values.Value,                                        
                    keys.Key,
                    keys.Context
                };
var myStrings = rawStrings.AsEnumerable().Select(t => new NewModel {
     Value = t.Value,
     Hash = CalculateHash(string.Format("{0}_{1}", t.Key, t.Context))
});

The first query runs on the RDBMS side, and produces the key and the context for computing the Hash. The second query uses the raw data to compute the desired output.

Linq with EF method cannot be translated into a store expression(in repository pattern)

The problem is using the ToList function in your LINQ query, that is not what you want to do, since that cannot be translated to a proper SQL query. You want to use ToList only outside the actual LINQ query. To get the count inside, use the LINQ Count function instead, for instance :

select new                                                       
{

StudentSections.ClassID

}).Count()

this method cannot be translated into a store expression when i call another repository in my query

Linq doesn't recognize that method. It can only be translated from expression tree and some SQL function extensions.

You need to execute the query first.

List<UserAssitanceView> AllAssitance = objFirstIdeaAssistanceRepository.GetAll().Select(i => new UserAssitanceView()
    {
        assitanceId = i.Assistance.Id.ToString(),
        assitanceInternalTell = i.Assistance.AssistanceTell,
        assitanceName = i.Assistance.AssistanceName,
    })
   .ToList();

And then set some properties later.

foreach (var item in AllAssitance)
{
    item.experteName = objAssistanceRepository.ReturnExpertOfAssitance(i.AssistanceId).Name;
    item.expertFamily = objAssistanceRepository.ReturnExpertOfAssitance(i.AssistanceId).Family;
    item.ExperteMobile = objAssistanceRepository.ReturnExpertOfAssitance(i.AssistanceId).Mobile;
}

Or you can just execute it first for the whole query.

List<UserAssitanceView> AllAssitance = 
     objFirstIdeaAssistanceRepository.GetAll().AsEnumerable().Select
                                                  ^^^^
                                                add this

PS

AsEnumerable will change the type from IQueryable<T> to IEnumerable<T> and cause a differed execution only when it's iterated. This will make sure any execution after this call will not execute the database, but simply iterate from the enumerable. So it's safe now to use non linq expression, like CLR method.

This code only will not execute the query.

var result = objFirstIdeaAssistanceRepository.GetAll().AsEnumerable();

But adding Select method after it, will execute the query.

var result = objFirstIdeaAssistanceRepository.GetAll().AsEnumerable().Select(/* */);

---

Related Topics