DateTime2 conversion problems

[Murilo]

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)
)

[Viktor Zhivkov]

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

 

 

[Murilo]

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.

[Viktor Zhivkov]

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

 

 

[Murilo]

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.

[Viktor Zhivkov]

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