DateTime2 conversion problems

6 posts, 0 answers
  1. Murilo
    Murilo avatar
    10 posts
    Member since:
    Feb 2011

    Posted 03 Jan 2015 Link to this post

    Data Acess Version: 2014.3.1027.1
    Visual Studio Version: 2013 Ultimate Update 4
    SQL Server: Microsoft SQL Server 2014 - 12.0.2000.8 (X64)  Enterprise Edition (64-bit) 

    I'm facing some problems while reading data with Telerik Data Access when the table has DateTime2 columns and the user specifies a filter that is not in range (precision or date itself).

    It appears that the component is always converting the data to DATETIME instead of DATETIME2, doing so, it loses precision and throws specific out of range exceptions handled by the DATETIME type.

    I got the following query while tracing the problem in SQL Server:

    SELECT a.[id] AS COL1, a.[datetime2_column] AS COL2 FROM [qa].[datetime2_issue] a WHERE a.[datetime2_column] >= CONVERT(DATETIME, '0001-01-01 00:00:00.000', 121) AND a.[datetime2_column] < CONVERT(DATETIME, '0001-01-02 00:00:00.000', 121) ORDER BY COL2

    Which throws:
    The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

    The correct should be:
    ,SELECT a.[id] AS COL1, a.[datetime2_column] AS COL2 FROM [qa].[datetime2_issue] a WHERE a.[datetime2_column] >= CONVERT(DATETIME2, '0001-01-01 00:00:00.000', 121) AND a.[datetime2_column] < CONVERT(DATETIME2, '0001-01-02 00:00:00.000', 121) ORDER BY COL2

    Table and schema:
    create schema qa
    go
    create table [qa].datetime2_issue
    (
    id int identity(1,1),
    datetime2_column datetime2, 
    constraint pk_qa_datetime2_issue primary key (id)
    )

  2. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 07 Jan 2015 Link to this post

    Hi Murilo,

    I have tried and failed to reproduce the SQL that you have posted using the same column setup as you and the following LINQ query:
    1.DateTime now = DateTime.UtcNow;
    2.var query = from d in context.DateTime2Table
    3.        where d.DateTime2 >= now && d.DateTime2 < now.AddDays(1)
    4.        select d;
    5. 
    6.string sql = query.ToString();
    7.var result = query.ToList();
    The result SQL is:
    1.SELECT a.[id] AS COL1, a.[dateTime2] AS COL2
    2.FROM [DateTime2Table] a
    3.WHERE a.[dateTime2] >= @p0 AND a.[dateTime2] < @p1 , [@p0=2015-01-07T15:51:30.6980783Z @p1=2015-01-08T15:51:30.6980783Z] 

    Can you please post your LINQ query and the mapping that you are using for the DateTime2 column? 
    Have you set any converter manually for it?

    We are looking forward your feedback.

    Regards,
    Viktor Zhivkov
    Telerik
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
  3. DevCraft banner
  4. Murilo
    Murilo avatar
    10 posts
    Member since:
    Feb 2011

    Posted 12 Jan 2015 in reply to Viktor Zhivkov Link to this post

    Hey Viktor, 

    Your sample did fine here aswell, I don't know if it's related but my where clause is created with Dynamic Linq from a string. This process is done by a extension class in our project.

    I'll take a look and see if I can find anything wrong with it and if I can't I will post in a few days a complete working demo (with data access and dynamic linq isolated).

    Thanks for the response.
  5. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 15 Jan 2015 Link to this post

    Hello Murilo,

    Thanks for the clarification about using Dynamic LINQ extensions. Changing my query to use them allowed me to reproduce the issue.
    Unfortunately as far as I can see the problem is in the way Dynamic LINQ implementation handles parameters. It will always create a type conversion expression without taking into account the mapped database type.

    Can you define in greater detail the use case scenario that you are trying to implement so we can try to find another way of doing it that will avoid this limitation of Dynamic LINQ?

    Regards,
    Viktor Zhivkov
    Telerik
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
  6. Murilo
    Murilo avatar
    10 posts
    Member since:
    Feb 2011

    Posted 16 Jan 2015 in reply to Viktor Zhivkov Link to this post

    Viktor,

    I'm using a grid with automated filtering which returns the filter as a Dynamic Linq expression.

    I've managed to extend the Dynamic Linq library to support the DateTime conversion the right way. I'm running some tests for performance and backward compatibility reasons.

    If you want I can post the details of the implementation I did on the Dynamic.cs class for future reference.




  7. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 21 Jan 2015 Link to this post

    Hello Murilo,

    We will be happy if you can share your modifications to the Dynamic LINQ source with us and the Telerik Data Access community.
    The best way to do so will be to use some ready to read code file sharing service (like github or pastebin), but any other way that is convenient for you is welcome.

    Regards,
    Viktor Zhivkov
    Telerik
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
Back to Top
DevCraft banner