Issue Details (XML | Word | Printable)

Key: DNET-932
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Jiri Cincura
Reporter: Daniel Richter
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
.NET Data provider

Explicit CAST for Timestamp values

Created: 13/Mar/20 03:52 PM   Updated: 16/Mar/20 03:49 PM
Component/s: Entity Framework
Affects Version/s: 7.5.0.0
Fix Version/s: vNext


 Description  « Hide
For DateTime values, the generated SQL statement contains the formatted value in quotation marks. E.g.
   dataContext.Orders.Where(o => o.OrderDate==new DateTime(2020,1,2));
generates SQL such as
   SELECT * FROM "ORDERS" WHERE "ORDERDATE" = '2020-01-02 00:00:00.0000'

That does _not_ work in case the DateTime value is used as a parameter for a function/procedure. E.g.
    dataContext.Orders.Select(o => new { Days = DbFunctions.DiffDays(o.OrderDate,new DateTime(2020,1,2) })
generates SQL such as
   SELECT DATEDIFF(DAY, "ORDERDATE", '2020-01-02 00:00:00.0000') AS "DAYS" FROM "ORDERS"
results in the following error message: "expression evaluation not supported. Expected DATE/TIME/TIMESTAMP type as first and second argument to DATEDIFF"

The solution is quite simple - explicitly casting as TIMESTAMP in SQL (SqlGenerator.cs):

   internal static string FormatDateTime(DateTime value)
   {
      var result = new StringBuilder();
      result.Append("CAST(");
      result.Append("'");
      result.Append(value.ToString("yyyy-MM-dd HH:mm:ss.ffff", CultureInfo.InvariantCulture));
      result.Append("' AS TIMESTAMP)");
      return result.ToString();
   }

   internal static string FormatTime(DateTime value)
   {
      var result = new StringBuilder();
      result.Append("CAST(");
      result.Append("'");
      result.Append(value.ToString("HH:mm:ss.ffff", CultureInfo.InvariantCulture));
      result.Append("' AS TIME)");
      return result.ToString();
   }
   
The resulting SQL will be as follows:
    SELECT DATEDIFF(DAY, "ORDERDATE", CAST('2020-01-02 00:00:00.0000' AS TIMESTAMP)) AS "DAYS" FROM "ORDERS"
    
I don't know any cases where an explicit cast (in comparison to the current implementation) should be an issue.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Jiri Cincura added a comment - 16/Mar/20 11:45 AM
Yeah, looks like a safe change to me too. Care to do a PR?

Daniel Richter added a comment - 16/Mar/20 12:07 PM
All changes are mentioned above, but if you want me to create a PR of course I can create one.

Which one is the "correct" repo to create a PR? FirebirdSQL/NETProvider or cincuranet/FirebirdSql.Data.FirebirdClient?

Jiri Cincura added a comment - 16/Mar/20 12:42 PM
cincuranet/FirebirdSql.Data.FirebirdClient is easier for me.