This is the solution to get the formatted date when querying to the SQL Server database using Entity Framework in Visual Studio.

Here, I am using ToLongDateString() method to get long date string format as follows:

var list = (from student in db.Students
     select new
	 {
      id = student.ID,
	  lastname = student.LastName,
	  firstmidname = student.FirstMidName,
	  enrollmentdate = student.EnrollmentDate.ToLongDateString()
	 }).ToList();

The error generate is:

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

After few research I came to know according to this source https://stackoverflow.com/questions/5839388/formatting-date-in-linq-to-entities-query-causes-exception , this query syntax that we have return will be translated into SQL first and then it is sent to database. And the attempt to format the date is not supported in the SQL query.

So, what are the solutions?

I found two:

Don’t format the date in the query syntax which is executed against the database in the server. Instead, get the data from the server without any formatting and format it in the client (browser).

OR

At first, retrieve the results by executing the syntax query against the database without any format and then format the results in memory.

I am going to explain solution two as I found this is straightforward and ideal way for me to get the results than solution one which uses confusing javascript codes in the client (at least it was confusing and unreadable for me).

So, this is the solution:

At first, we will retrieve the results from the syntax query without any format and then only we will format the results as requirement.

     var list = from student in db.Students.ToList()
     select new
	 {
      id = student.ID,
	  lastname = student.LastName,
	  firstmidname = student.FirstMidName,
	  enrollmentdate = student.EnrollmentDate.ToLongDateString()
	 };

Here, we use .ToList() method. The queries that are to be sent to the database are executed only when ToList() method is called. This method will first force to execute the portion of the syntax query i.e. db.Students.ToList() against the database which simply returns rows from Student table. After it, we worked with results (in memory). Here, we select the required attributes with formatting on date and this returns anonymous types of IEnumberable<> list.