Linq to Entities error

Question

In MVVM, i am getting an entity set from the repository to viewmodel. While trying to access the elements of the entity, it throws an exception:

LINQ to Entities does not recognize the method 'InsurableRisk.Entities.QueriesParameter ElementAt[QueriesParameter](System.Linq.IQueryable`1[InsurableRisk.Entities.QueriesParameter], Int32)' method, and this method cannot be translated into a store expression.

Here is my code:

Repository:

public IQueryable<QueriesParameter> GetParams(int QKey)
    {
        IQueryable<QueriesParameter> param = (from Q in Context.QueriesParameters
                                              where (Q.QueryKey == QKey)
                                              select Q);
        return param;
    }

Service:

public IQueryable<QueriesParameter> GetParams(int QKey)
    {
        return _repository.GetParams(QKey);
    }

ViewModel:

    paramLabel = new string[] { "ParamLabel1", "ParamLabel2", "ParamLabel3", "ParamLabel4", "ParamLabel5", "ParamLabel6" };
    param = new string[] { "Param1", "Param2", "Param3", "Param4", "Param5", "Param6" };
    paramVisibility = new string[] { "ParamVisiblity1", "ParamVisiblity2", "ParamVisiblity3", "ParamVisiblity4", "ParamVisiblity5", "ParamVisiblity6" };
    paramLabelVisibility = new string[] { "ParamLabelVisiblity1", "ParamLabelVisiblity2", "ParamLabelVisiblity3", "ParamLabelVisiblity4", "ParamLabelVisiblity5", "ParamLabelVisiblity6" };

    private Dictionary<int, string> m_queryNames;
    private Dictionary<int, string> m_ReadOnlyQueryNames;
    private int m_SelectedQueryNames;

    public int SelectedQueryNames
    {
        get
        {
            return m_SelectedQueryNames;
        }
        set
        {
            if (m_SelectedQueryNames != value)
            {
                m_SelectedQueryNames = value;
                OnPropertyChanged("SelectedQueryNames");
                var QKey = m_SelectedQueryNames;
                var sqlQuery = _service.GetQuery(QKey);
                var paramCount = _service.GetParamCount(QKey);
                //code to make the run button visible and the parameters to be visible  
                m_Visibility = true;
                IQueryable<QueriesParameter> param = _service.GetParams(QKey);
                for (int i = 1; i <= paramCount; i++)
                {
                    OnPropertyChanged(paramLabelVisibility[i]);
                    OnPropertyChanged(paramVisibility[i]);
                    QueriesParameter qParam = param.ElementAt(i);  <!-- I get the exception here -->
                    m_LabelName = qParam.ParameterName;
                    OnPropertyChanged(paramLabel[i]);
                    //OnPropertyChanged(param[i]);                        
                }                    
            }
        }

Any help as in why i am getting this error?

Answer 1

You are getting this error because ElementAt is not supported by LINQ to Entities (indeed, how you will translate it into SQL?). Here is list of Supported and Unsupported LINQ Methods.

You can enumerate over params instead:

IQueryable<QueriesParameter> param = _service.GetParams(QKey);
int i = 1; // btw why you are iterating from index 1? It should be zero!
foreach(var p in param)
{
   OnPropertyChanged(paramLabelVisibility[i]);
   OnPropertyChanged(paramVisibility[i]);
   QueriesParameter qParam = p; // here
   m_LabelName = qParam.ParameterName;
   OnPropertyChanged(paramLabel[i]);
   i++;
}    

Another option - move query to client side by calling AsEnumerable() or ToList(). Then Linq to Objects will be used, where you can use ElementAt(index) method or via indexer [index]:

 List<QueriesParameter> param = _service.GetParams(QKey).ToList(); 
 //... your code
 QueriesParameter qParam = param[i];