Wednesday, September 23, 2015

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

Problem
I'm executing a LINQ-to-SQL query where I'm trying to format a date field and i'm getting the exception: "Method 'System.String ToString(System.String)' has no supported translation to SQL."

Here's my query
var query = (from p in context.MyTable
                                where  p.EventType == MyEventType
                                &&
                                ( p.SystemDate.ToString("M/dd/yy") + " " +
                          p.SystemTime.ToString("H:mm") == MyDate)

                                select p)


Solution
Split the query into two parts. First query as much as you can without using the methods that are causing the exception. Convert the result to a list. Then query the list using the methods you were wanting to use in the first place. Like this:

//this is the original query without the methods (ToString()) that were causing the exception
var preQuery = (from p in context.MyTable
                                where  p.EventType == MyEventType

                                select p).ToList();

//here's the new query 
var query = (from p in preQuery
                        let FormattedDate = (
                          p.SystemDate.ToString("M/dd/yy") + " " +
                          p.SystemTime.ToString("H:mm"))
                        where
                          FormattedDate = Mydate

                        select p).AsQueryable(); //AsQueryable because I want IQueryable result

Referencehttps://social.msdn.microsoft.com/Forums/en-US/567237c8-0fd0-4d4c-a31e-935ea4d9653f/linqtosql-formatting-datetime?forum=linqtosql 

No comments:

Post a Comment

There was an error in this gadget