Method 'System.String GetState(int32)' has no supported translation to SQL

Question

I'm using the below code to fetch the state. I'm getting the error" Method 'System.String GetState(int32)' has no supported translation to SQL".Please let me know where i'm doing a mistake.

    public IQueryable<ViewModel> GetResult()
            {
                IQueryable<ViewModel> result;

                if (isDestinationSite)
                {
                    result = (from table1 in this.db.tblTable1      
                              select new ViewModel
                              {
                                 State= this.GetState(table1.PersonUID),                                
                              });
                }

   private string GetState(int PersonUID)
        {
            using ( PersonPref pref = new PersonPref ())
            {
                pref .selectPref(ApplicationCode.MyApp,                                                  PersonPref .preference);
                if (pref.PesronValue== "True")
                {

                    return "Successfull";
                }
                else
                {
                    return "Failure";
                }
            }            
        }

Side note: surprising set of tags - error looks like coming from LINQ-to-SQL, but tags explicitly state otherwise... Consider editing. — Alexei Levenkov, Feb 27 '15 at 17:09
The error message seems pretty self explanatory. The LINQ query provider can't translate that method into SQL... — Servy, Feb 27 '15 at 17:16

aw04 · Answer 1 · 2015-02-27T17:28:54.433

4

SQL doesn't know anything about your function so you just need to move it outside of your linq query.

List<ViewModel> result;

var personUID = (from table1 in this.db.tblTable1 select table1.PersonUID).ToList();

foreach (var id in personUID)
{
    result.Add(new ViewModel { State = GetState(id) });
}

edited Feb 27 '15 at 17:28

answered Feb 27 '15 at 17:10

aw04

10,857
10
56
89

Good explanation/shows the idea for fix just fine. Please consider to edit `result.State = GetState(result.State);` into something that is close to valid C# - `result` is enumerable of {int State}, and does not really have `result.State`. – Alexei Levenkov Feb 27 '15 at 17:14
@AlexeiLevenkov yep i realized that as soon as i posted, i think it's good now let me know if i missed something – aw04 Feb 27 '15 at 17:17
1

Looks ok. It may be good idea to add notice that you really can't get real `IQueryable` that way as one have to run query client side to convert to collection of `ViewModel`... – Alexei Levenkov Feb 27 '15 at 17:24
Side note: there are probably many similar questions already (search for http://www.bing.com/search?q=Method+has+no+supported+translation+to+SQL gives some) - so avoid spending too much time on this one :) – Alexei Levenkov Feb 27 '15 at 17:25

score 0 · Answer 2 · answered Feb 27 '15 at 17:25

You can write iterate your query with AsEnumerable and then do the selection like:

  result = (from table1 in this.db.tblTable1      
            .AsEnumerable()
              select new ViewModel
              {
                 State= this.GetState(table1.PersonUID),                                
              });

Method 'System.String GetState(int32)' has no supported translation to SQL

2 Answers2