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

    Question

  • Hi

    i want to display my data with some formats in my linq projection, but i don't know how to accomplish this. for example i want to display my datetime column as time only. here is my linq projection :

    var query = from c in db.Contacts select new { c.FullName, c.Category.CategoryName, c.DateCreated };
    

    The DateCreated is column which i want to display as Time only. also i could accomplish this via EntityFunctions.CreateTime as follow :

    var query = from c in db.Contacts select new { c.FullName, c.Category.CategoryName, Time = EntityFunctions.CreateTime(c.DateCreated.Value.Hour, c.DateCreated.Value.Minute, c.DateCreated.Value.Second) };
    

    but i don't know is this true or not!!!

    can anybody help me which how to convert my fields dataTypes to another type in linq projection ?

    thanks in advance


    http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx
    Saturday, November 13, 2010 9:56 PM

Answers

  • Long answer:

    Some things don't translate well from CLR methods to TSQL. Date formatting is a good example since the .ToString(string s), .ToShort... etc methods rely on a whole lot of locale-specific settings to format the end-result. (Separators, order of the different date portions, what calendar era is used, month/day names etc). T-SQL doesn't have all those locale specific formatting things supported in detail like .net has, nor does other RDBMSes SQL dialects. In other words, translating the .net method DateTime.ToShortDateString into TSQL would result in a very large chunk of SQL to take all the locale-specific formatting factors into account, or alternatively would result in a method returning a different result than the .net equivalent. (Which would be even more confusing).

    To illustrate that a fair amount of locale-specific logic is involved in .net's date and time formatting, I have included the output of DateTime.ToShortDateString and DateTime.ToLongDateString under a couple of different culture/locale settings for today's date:

    en-us (US English):
    11/15/2010
    Monday, November 15, 2010

    sv-se (Swedish):
    2010-11-15
    den 15 november 2010

    zh-cn (Chinese):
    2010/11/15
    2010年11月15日

    ar-sa (Arabic / Saudi Arabia):
    09/12/31
    09/ذو الحجة/1431

    th-th (Thai):
    15/11/2553
    15 พฤศจิกายน 2553

    Yes, that's right, all five examples above are the same date (15 November 2010) with just different culture/locale settings. Imagine the size of the T-SQL representation of all the date formatting code needed to do that. Probably not something you would want to hit your database with... :)

    So to answer your question: Your best bet is to do the date formatting and other things that .net does good but that can't be translated to T-SQL in .net code. Either (as suggested by @darnold in his replies above) in a separate projection class that has a property that do the formatting, or by projecting the L2E query into .net types and then do a second L2O (linq-to-objects) query that project into a new type with the formatting and other transformations you may want to do.

     

    Short answer:

     

    Do it in two steps - one L2E query that does the L2E supported part, and a L2O query that does the date formatting and other stuff that is best done in .net code...

    //Linq-to-entities query to get the fullname, categoryname, and date
    var query = from c in db.Contacts select new { c.FullName, c.Category.CategoryName, c.DateCreated };

    //Linq-to-objects query (.AsEnumerable will separate the L2E from L2O part) that call date formatting methods and other stuff that isn't supported by L2E
    var r = from c in query.AsEnumerable() select new { c.FullName, c.CategoryName, ShortDate = c.DateCreated.ToShortDateString() };


     


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    Monday, November 15, 2010 3:50 AM

All replies

  • On 11/13/2010 3:56 PM, Hamed_1983 wrote:
    > Hi
    >
    > i want to display my data with some formats in my linq projection, but i
    > don't know how to accomplish this. for example i want to display my
    > datetime column as time only. here is my linq projection :
    >
    > var query =from cin db.Contactsselect new { c.FullName, c.Category.CategoryName, c.DateCreated };
    >
    > The DateCreated is column which i want to display as Time only. also i
    > could accomplish this via EntityFunctions.CreateTime as follow :
    >
    > var query =from cin db.Contactsselect new { c.FullName, c.Category.CategoryName, Time = EntityFunctions.CreateTime(c.DateCreated.Value.Hour, c.DateCreated.Value.Minute, c.DateCreated.Value.Second) };
    >
    > but i don't know is this true or not!!!
    >
    > can anybody help me which how to convert my fields dataTypes to another
    > type in linq projection ?
    >
    > thanks in advance
     
     
     
    So why can you not use the ToString() -- c.DateCreated.ToString(format
    parameter)
     
    http://msdn.microsoft.com/en-us/library/aa326721(VS.71).aspx
     
    Sunday, November 14, 2010 8:30 AM
  • On 11/13/2010 3:56 PM, Hamed_1983 wrote:
    > Hi
    >
    > i want to display my data with some formats in my linq projection, but i
    > don't know how to accomplish this. for example i want to display my
    > datetime column as time only. here is my linq projection :
    >
    > var query =from cin db.Contactsselect new { c.FullName, c.Category.CategoryName, c.DateCreated };
    >
    > The DateCreated is column which i want to display as Time only. also i
    > could accomplish this via EntityFunctions.CreateTime as follow :
    >
    > var query =from cin db.Contactsselect new { c.FullName, c.Category.CategoryName, Time = EntityFunctions.CreateTime(c.DateCreated.Value.Hour, c.DateCreated.Value.Minute, c.DateCreated.Value.Second) };
    >
    > but i don't know is this true or not!!!
    >
    > can anybody help me which how to convert my fields dataTypes to another
    > type in linq projection ?
    >
    > thanks in advance
     
     
     
    So why can you not use the ToString() -- c.DateCreated.ToString(format
    parameter)
     
    http://msdn.microsoft.com/en-us/library/aa326721(VS.71).aspx
     


    Hi

    i've used this line :

    var query = from c in db.Contacts select new { c.FullName, c.Category.CategoryName, Time = c.DateCreated.ToString() };
    

    but i got the same error :

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


    http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx
    Sunday, November 14, 2010 11:29 AM
  • On 11/14/2010 5:29 AM, Hamed_1983 wrote:
    > On 11/13/2010 3:56 PM, Hamed_1983 wrote:
    > > Hi
    > >
    > > i want to display my data with some formats in my linq
    > projection, but i
    > > don't know how to accomplish this. for example i want to display my
    > > datetime column as time only. here is my linq projection :
    > >
    > > var query =from cin db.Contactsselect new { c.FullName,
    > c.Category.CategoryName, c.DateCreated };
    > >
    > > The DateCreated is column which i want to display as Time only.
    > also i
    > > could accomplish this via EntityFunctions.CreateTime as follow :
    > >
    > > var query =from cin db.Contactsselect new { c.FullName,
    > c.Category.CategoryName, Time =
    > EntityFunctions.CreateTime(c.DateCreated.Value.Hour,
    > c.DateCreated.Value.Minute, c.DateCreated.Value.Second) };
    > >
    > > but i don't know is this true or not!!!
    > >
    > > can anybody help me which how to convert my fields dataTypes to
    > another
    > > type in linq projection ?
    > >
    > > thanks in advance
    > So why can you not use the ToString() -- c.DateCreated.ToString(format
    > parameter)
    > http://msdn.microsoft.com/en-us/library/aa326721(VS.71).aspx
    >
    >
    > Hi
    >
    > i've used this line :
    >
    > var query =from cin db.Contactsselect new { c.FullName, c.Category.CategoryName, Time = c.DateCreated.ToString() };
    >
    > but i got the same error :
    >
    > /LINQ to Entities does not recognize the method 'System.String
    > ToString()' method, and this method cannot be translated into a store
    > expression./
    >
     
    The easiest thing I would do is make a custom object.
     
    <http://msdn.microsoft.com/en-us/library/w86s7x04(VS.80).aspx>
    <http://msdn.microsoft.com/en-us/library/bb384054.aspx>
     
    I would take the ToString() off the C.DateCreateed and use your Linq
    query above to return 'query'.
     
    I would do var holdobjs = new List<CustomObject>() // which would be at
    the top of the method
     
    Then I would use Foreach loop on 'query'..
     
    foreach(var q in query)
    {
    var obj = new CustomObject();
    obj.Fullname = q.Fullname;
    obj.Time = q.DateCreated.ToString()
    holdobjs.add(obj);
     
    }
     
    Then you can use holdobjs to do whatever you want, bind it to a control.
     
    It's the simple thing to do.
     
    Sunday, November 14, 2010 5:25 PM
  • Sorry darnold92

    but i didn't understood how u accomplish this!

    i think there is a better and easier way to solve this problem.


    http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx
    Sunday, November 14, 2010 5:32 PM
  • On 11/14/2010 11:32 AM, Hamed_1983 wrote:
    > Sorry darnold92
    >
    > but i didn't understood how u accomplish this!
    >
    > i think there is a better and easier way to solve this problem.
    >
     
    You can't make something do something that it can't do. Linq-2-Entities
    is telling you that it can't use a ToString() in any shape form or
    fashion when using it in a "new" Shape statement to create a "new" object.
     
    Not to be smart here, but if you don't understand it, then you don't
    understand Object Oriented Programming at its rudimentary level, which
    is a must when programming with a OOP language such as C#.Net.
     
    Using the ADO.NET Entity Framework, nHibernate, any other type of ORM
    solution or even Linq, you must understand objects, because everything
    is encapsulated in objects. Everything in .NET languages are derived
    from System.Object.
     
    May I make this suggestion to you, which leads to you understanding OOP(s).
     
    The book is in java, which is not that hard to understand
     
    http://headfirstlabs.com/books/hfdp/
     
    The HeadFirst examples are in VB and C# in the link, which you can get
    the 3.5 versions or any previous versions based off the .Net Framework too.
     
    http://www.dofactory.com/Framework/Framework.aspx
     
    Sunday, November 14, 2010 6:18 PM
  • Long answer:

    Some things don't translate well from CLR methods to TSQL. Date formatting is a good example since the .ToString(string s), .ToShort... etc methods rely on a whole lot of locale-specific settings to format the end-result. (Separators, order of the different date portions, what calendar era is used, month/day names etc). T-SQL doesn't have all those locale specific formatting things supported in detail like .net has, nor does other RDBMSes SQL dialects. In other words, translating the .net method DateTime.ToShortDateString into TSQL would result in a very large chunk of SQL to take all the locale-specific formatting factors into account, or alternatively would result in a method returning a different result than the .net equivalent. (Which would be even more confusing).

    To illustrate that a fair amount of locale-specific logic is involved in .net's date and time formatting, I have included the output of DateTime.ToShortDateString and DateTime.ToLongDateString under a couple of different culture/locale settings for today's date:

    en-us (US English):
    11/15/2010
    Monday, November 15, 2010

    sv-se (Swedish):
    2010-11-15
    den 15 november 2010

    zh-cn (Chinese):
    2010/11/15
    2010年11月15日

    ar-sa (Arabic / Saudi Arabia):
    09/12/31
    09/ذو الحجة/1431

    th-th (Thai):
    15/11/2553
    15 พฤศจิกายน 2553

    Yes, that's right, all five examples above are the same date (15 November 2010) with just different culture/locale settings. Imagine the size of the T-SQL representation of all the date formatting code needed to do that. Probably not something you would want to hit your database with... :)

    So to answer your question: Your best bet is to do the date formatting and other things that .net does good but that can't be translated to T-SQL in .net code. Either (as suggested by @darnold in his replies above) in a separate projection class that has a property that do the formatting, or by projecting the L2E query into .net types and then do a second L2O (linq-to-objects) query that project into a new type with the formatting and other transformations you may want to do.

     

    Short answer:

     

    Do it in two steps - one L2E query that does the L2E supported part, and a L2O query that does the date formatting and other stuff that is best done in .net code...

    //Linq-to-entities query to get the fullname, categoryname, and date
    var query = from c in db.Contacts select new { c.FullName, c.Category.CategoryName, c.DateCreated };

    //Linq-to-objects query (.AsEnumerable will separate the L2E from L2O part) that call date formatting methods and other stuff that isn't supported by L2E
    var r = from c in query.AsEnumerable() select new { c.FullName, c.CategoryName, ShortDate = c.DateCreated.ToShortDateString() };


     


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    Monday, November 15, 2010 3:50 AM