[System.DateTime])' has no supported translation to SQL

Question

I want to execute a query like this:

List<supervisorAnswerQuesttionPres> temp =
    (from i in dbconnect.tblAnswerLists
    where i.StudentNum == studentNumber
    select new supervisorAnswerQuesttionPres
    {
        answerList = _resAnswerList,
        questionList = _resQuestionist,
        date = ConvertToPersianToShow(i.dateOfAnswer.Value.Date)
    }).OrderBy(i => i.date).ToList();

My class that this query is returned is something like this :

public class supervisorAnswerQuesttionPres
{
    public string date { set; get; }
    public List<string> questionList { set; get; }
    public List<string> answerList { set; get; }
}

In this query i use a function to convert my Datetime to another presentation i use this function for this :

public string ConvertToPersianToShow(DateTime? datetime)
{
    string date;
    DateTime dt;

    if (!datetime.HasValue) return "";
    dt = datetime.Value;
    //  dt = datetime;

    string year = Convert.ToString(persian_date.GetYear(dt));
    string month = Convert.ToString(persian_date.GetMonth(dt));
    string day = Convert.ToString(persian_date.GetDayOfMonth(dt));

    if (month.Length == 1)
    {
        month = "0" + Convert.ToString(persian_date.GetMonth(dt));
    }

    if (day.Length == 1)
    {
        day = "0" + Convert.ToString(persian_date.GetDayOfMonth(dt));
    }

    Convert.ToString(persian_date.GetMonth(dt)) + "/" +
            +                 dt.Minute + ")";
    date = year + "/" + month + "/" + day;
    return date;
}

This function just convert my DateTime ,But when i execute the query i got this error:

Method 'System.String ConvertToPersianToShow(System.Nullable`1[System.DateTime])' has no supported translation to SQL. 

Answer 1

It's trying to convert the query into SQL, but doesn't know how to convert the ConvertToPersianToShow method.

The solution is to call ToList() after the where clause to bring the entities into memory, then do the select:

var temp = dbconnect.tblAnswerLists
    .Where(i => i.StudentNum == studentNumber)
    .ToList() // <-- This will bring the data into memory.
    .Select(i => new supervisorAnswerQuesttionPres
        {
            answerList = _resAnswerList,
            questionList = _resQuestionist,
            date = ConvertToPersianToShow(i.dateOfAnswer.Value.Date)
        })
    .OrderBy(i => i.date)
    .ToList()

When calling ToList(), the query is translated into SQL, e.g. something like

SELECT * FROM <table> WHERE StudentNum = '<studentNumber>'

and executed against the database. When the data returns and you have it in memory, you can use LINQ to Objects to query and manipulate the data further.

NOTE! Generally you should be careful to call ToList before you've added at least a where clause, otherwise you'll end up fetching way too much data into memory.