Jibin Balachandran Jibin Balachandran - 2 months ago 22
ASP.NET (C#) Question

Convert datetime2 to string("dd/MM/yyyy") in LINQ

I have a datetime2 column in the database.I'm trying to fetch that from database and I want to display it in the view as string.

I tried:


But it gave an error.

An exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

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

I tried
String.Format("{0:y yy yyy yyyy}", dt);
also, even this throwed an error.



I think I realize your problem, after you posted the screenshot. Some functions aren't allowed to run inside the linq, as linq needs to translate them to sql.

Since I can't copy paste your code with my modifications (you need to perform the date formatting outside the linq statement, whether using a foreach loop, or any other method). A rough idea:

allclient = (from ....
             select new AllClient()
              PurchaseDT = "",
              PurchaseDate = k.PurchaseDate, //the formatting will be done later
foreach (AllClient c in allclient)
      c.PurchaseDT = String.Format("{0:y yy yyy yyyy}", c.PurchaseDate); //or whatever other formatting you want

where PurchaseDate is of type DateTime and PurchaseDT is of type string.

First save the value coming from db to a datetime variable and later loop through the list using a foreach loop,convert the datetime variable into string and assign it to the string variable.


The only way will involve you adding one more variable to the AllClient class, if you can. This will be a DateTime to temporarily store the date, with another one as string (as shown in the code snippet above), which will be updated with the formatted date after the linq statement.


Keep a single DateTime PurchaseDT, and instead of formatting it in linq or immediately afterwards, only format it when you are displaying it. For example, if you're displaying the records on a table of some kind, while displaying the date column, do the formatting on PurchaseDT there.