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

Question

I am trying to execute a query like this :

  List<UserAssitanceView> AllAssitance = objFirstIdeaAssistanceRepository.GetAll().Select(i => new UserAssitanceView()
    {
        assitanceId = i.Assistance.Id.ToString(),
        assitanceInternalTell = i.Assistance.AssistanceTell,
        assitanceName = i.Assistance.AssistanceName,
        experteName = objAssistanceRepository.ReturnExpertOfAssitance(i.AssistanceId).Name,
        expertFamily = objAssistanceRepository.ReturnExpertOfAssitance(i.AssistanceId).Family,
        ExperteMobile = objAssistanceRepository.ReturnExpertOfAssitance(i.AssistanceId).Mobile
    }).ToList();

public class UserAssitanceView
    {
        public string assitanceId { set; get; }
        public string assitanceName { set; get; }
        public string experteName { set; get; }
        public string expertFamily { set; get; }
        public string assitanceInternalTell { set; get; }
        public string ExperteMobile { set; get; }


    }

FirstIdeaAssistance class :

namespace DomainClass
{
    using System;
    using System.Collections.Generic;

    public partial class FirstIdeaAssistance
    {
        public int Id { get; set; }
        public string Date { get; set; }
        public long FirstIdeaId { get; set; }
        public int AssistanceId { get; set; }

        public virtual FirstIdea FirstIdea { get; set; }
        public virtual Assistance Assistance { get; set; }
    }
}

In my query i call another repository method called objAssistanceRepository.ReturnExpertOfAssitance and the function is like this :

public class AssistanceRepository : GenericRepository<InModelContainer, Assistance>
{
    public User ReturnExpertOfAssitance(int assitanceId)
    {
        return
            FindBy(i => i.Id == assitanceId && i.AssistanceJurors.Any(j => j.User.Permission == "Assistance"))
                .First().AssistanceJurors.Select(i => i.User).First();
    }
}

After executing i got this error:

LINQ to Entities does not recognize the method 'DomainClass.User ReturnExpertOfAssitance(Int32)' method, and this method cannot be translated into a store expression.

When i remove these 3 lines from my code it works fine :

experteName = objAssistanceRepository.ReturnExpertOfAssitance(i.AssistanceId).Name,
            expertFamily = objAssistanceRepository.ReturnExpertOfAssitance(i.AssistanceId).Family,
            ExperteMobile = objAssistanceRepository.ReturnExpertOfAssitance(i.AssistanceId).Mobile

I did before like this and it worked fine but now i don't know why i get this error .

Every ideas will be apprciated

best regards

Answer 1

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(/* */);