Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

c#

datetime

linq

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. 
like image 402
SSC Avatar asked Mar 20 '23 10:03

SSC


1 Answers

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.

like image 90
khellang Avatar answered Mar 23 '23 01:03

khellang