LINQ to Entities does not recognize the method

Question

I wrote the following code in C# and the following query execution error occurs:

using (famloanEntities db = new famloanEntities())
            {
                var sand = db.Sandough.Select(x => new SandoughViewModel
                {
                    ID = x.Id,
                    OnvanSandough = x.OnvanSandough,
                    Masoul = x.Masoul,
                    Phone = x.Phone,
                    SDate = EightToTenDigitShamsi(x.SDate),
                    EDate = EightToTenDigitShamsi(x.EDate)
                }
                ).ToList();
            }

and this is function:

public static string EightToTenDigitShamsi(string Value)
    {
        string retStr = "";
        if (Value.Length==8)
        {
            retStr = Value.Substring(0, 4) + StrSeperator + Value.Substring(4, 2) + StrSeperator + Value.Substring(6, 2);
        }
        return retStr;
    }

and This error occurs:

System.NotSupportedException: 'LINQ to Entities does not recognize the method 'System.String EightToTenDigitShamsi(System.String)' method, and this method cannot be translated into a store expression.'

Please advise me how to use my function in the query.

Answer 1

Try one of approaches:

var sand = db.Sandough.Select(x => new SandoughViewModel
                 {
                     ID = x.Id,
                     OnvanSandough = x.OnvanSandough,
                     Masoul = x.Masoul,
                     Phone = x.Phone,
                     SDate = x.SDate,
                     EDate = x.EDate
                 }
                 ).ToList().Select( m =>
                 {
                    m.SDate = EightToTenDigitShamsi(m.SDate),
                    m.EDate = EightToTenDigitShamsi(m.EDate)
                 }.ToList( );

Also check if your original code works if you replace StrSeparator with a literal string.

Answer 2

The way that Entity Framework gets data from the database is the same as we use ADO.Net. It parses the C# code we write into a SQL statement and then gets the data.

EightToTenDigitShamsi is a method we wrote ourselves, and Entity Framework cannot parse it into any equivalent SQL, resulting in the current exception.

There are two ways to solve this problem, one is to get the data in c# first, and then use Select to change the query from the database level to the C# level.

The second is to save the string to a local variable first, and then use it in the expression.

             using (famloanEntities db = new famloanEntities())  
             {  
                 string sDate =EightToTenDigitShamsi(x.SDate);  
                 string eDate = EightToTenDigitShamsi(x.EDate);  
                 var sand = db.Sandough.Select(x => new SandoughViewModel  
                 {  
                     ID = x.Id,  
                     OnvanSandough = x.OnvanSandough,  
                     Masoul = x.Masoul,  
                     Phone = x.Phone,  
                     SDate = sDate,  
                     EDate = eDate  
                 }  
                 ).ToList();  
             }  

The premise of this is that the SandoughViewModel class is not your mapping entity, which is not allowed. If it is a class you created separately that's okay.

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.