how to convert datetime to string in linqtosql?

Question

I am using linqtosql and inside of linq query, I tried to convert datetime type column to string like 'dd-MM-yy'. However, I got error as following :

NotSupportedException: Method 'System.String ToString(System.String)' has no supported translation to SQL.

following is my linq query :

from ffv in Flo_flowsheet_values
where ffv.Flowsheet_key == 2489
&& ffv.Variable_key == 70010558
&& ffv.Status == 'A'
&& ffv.Column_time >= DateTime.ParseExact("2010-06-13 00:00", "yyyy-MM-dd HH:mm", null)
&& ffv.Column_time <= DateTime.ParseExact("2010-06-13 22:59", "yyyy-MM-dd HH:mm", null)
select new  { 
ColumnTime = ffv.Column_time
,ColumnTimeForXCategory = ffv.Column_time.Value.ToString("dd-MM-yy") ***====> this statement invoke error***
,BTValue = Convert.ToDouble( ffv.Value) }

what is problem?

Thanks in advance

Answer 1

You are parsing the DateTime strings in the expression itself and the LINQ to SQL provider cannot translate that C# code into equivalent T-SQL code.

Try something like this instead:

DateTime start 
    = DateTime.ParseExact(
        "2010-06-13 00:00", 
        "yyyy-MM-dd HH:mm", 
        CultureInfo.InvariantCulture);
DateTime end 
    = DateTime.ParseExact(
        "2010-06-13 22:59", 
        "yyyy-MM-dd HH:mm", 
        CultureInfo.InvariantCulture);

from ffv in Flo_flowsheet_values
where ffv.Flowsheet_key == 2489
    && ffv.Variable_key == 70010558
    && ffv.Status == 'A'
    && ffv.Column_time >= start
    && ffv.Column_time <= end
select new 
{ 
    ColumnTime = ffv.Column_time,
    ColumnTimeForXCategory = ffv.Column_time.Value.ToString("dd-MM-yy")
    BTValue = Convert.ToDouble( ffv.Value)
};

Answer 2

As mentioned above, you can linq wants to translate what you are doing to sql. Thats not possible in this case. But you could transform it to a string before your query and then pass the string into the linq expression.

Answer 3

Don't make the database do string formatting for the UI. Parse it on the client side.