LINQ to Entities does not recognize the method and this method cannot be translated into a store expression

Question

I call some date in my database using entity frame work. but My below code giving this error

LINQ to Entities does not recognize the method 'SchoolBreifcase.Compliance get_Item(Int32)' method, and this method cannot be translated into a store expression.

Here is my full code

FinancialCompliance financialCompliance = new FinancialCompliance();
            List<Compliance> compliance = null;
            if (HttpContext.Current.User.IsInRole("SchoolAdmin"))
            {

                compliance = datamodel.Compliances.Where(u => u.UserId == userId).OrderBy(c => c.AddedDate).ToList();

            }
            if (HttpContext.Current.User.IsInRole("User"))
            {
                compliance = datamodel.Compliances.Where(u => u.VerifierId == userId || u.OwnerId == userId).OrderBy(c => c.AddedDate).ToList();
            }
            if (compliance != null)
            {
                for (int i = 1; i < compliance.Count; i++)
                {
                    financialCompliance = datamodel.FinancialCompliances.Where(f => f.ComplianceId == compliance[i].ComplianceId).SingleOrDefault();
                    if (compliance.Count == i)
                    {
                        return financialCompliance;
                    }
                }
            }
            return financialCompliance;
        }

This line give that error:

financialCompliance = datamodel.FinancialCompliances.Where(f => f.ComplianceId == compliance[i].ComplianceId).SingleOrDefault();

Does not help stack over flow answer I have find some answers in this stack overflow site for

LINQ to Entities does not recognize the method

etc..But does not help to me .So I asked this question . Please don't any one close this question for reason of already asked

Solution 1

You need to create a variable to refer to compliance[i].ComplianceId then use it later.

for (int i = 1; i < compliance.Count; i++)
{
    var complianceId = compliance[i].ComplianceId;
    financialCompliance = datamodel.FinancialCompliances.Where(f => f.ComplianceId == complianceId ).SingleOrDefault();
    if (compliance.Count == i)
    {
        return financialCompliance;
    }
}

Solution 2

It's about the compliance[i].ComplianceId. Create a variable first:

var id = compliance[i].ComplianceId;

financialCompliance = datamodel.FinancialCompliances
                      .Where(f => f.ComplianceId == id).SingleOrDefault();

Comments

[bazza]

Thanks Satpal, this is a very useful answer. This was driving me nuts!

[router]

What if you are accessing something like compliance[f.Id].ComplianceId i.e dependent on the parameter of the lambda expression

[Ishara Madawa]

Yes, this is the correct answer. Can you please explain the exact reason for this scenario? Thank you.