.Net DateTime to DateTime2

[aerotog]

Using Devart LinqConnect 4.5.1024

Recently I've encountered bugs due to DateTime precision errors in my SQL queries. I've narrowed the problem to the LINQ to SQL translation layer. While the relevant database columns are of type DateTime2, when I query the DataContext, the arguments are passed as a DbType DateTime parameter.

I believe this is caused by the default mapping of .Net DateTime to DbType DateTime in LINQ to SQL.

I am using attribute mapping and have double checked that affected columns have the column attribute DbType = "DATETIME2 NOT NULL".

Example:

Code: Select all

using (var dc = new DataContext(connectionString))
{
    barEntity = (from bar in dc.Bars
                 select bar).FirstOrDefault();
}

using (var dc = new DataContext(connectionString))
{
    var foo = (from bar in dc.Bars
               where bar.Time == barEntity.Time
               select bar).FirstOrDefault();
}

Assuming the Bar table is not empty, I expect the value of "foo" to be not null, but it is null because the barEntity.Time argument is passed as DbType DateTime which does not equal the in database value with precision DateTime2.

The only way I've found to force the parameter in the SQL query to be DbType DateTime2 is to either:
- Manually alter the parameter in the command (as seen here)
- Create and call a database function that has a parameter of type DateTime2

This seems like such a trivial issue as one would think the mapping could easily be changed to .Net DateTime -> DbType DateTime2 but I have yet to find a good way of overriding the default.

Any help is appreciated!

[Shalex]

Thank you for your report. We will investigate the issue and notify you about the result.

[Shalex]

We cannot reproduce the issue. Please send us a small test project with the corresponding DDL/DML script for reproducing.

In case of >2MB attachment, we recommend either using some file exchange server (send us the corresponding link) or uploading a test project to our FTP server (the credentials will be provided by email request).

[aerotog]

I've opened a support ticket, but here is the example project since it's so simple.

The Linq query looks like:

Code: Select all

(from bar in context.Bars
where bar.EventTime == targetTime
select bar).ToList();

The logged SQL should look like:

Code: Select all

SELECT [t1].[Id], [t1].[EventTime]
FROM [foo].[Bar] [t1]
WHERE [t1].[EventTime] = @p0
-- @p0: Input DateTime (Size = 0; DbType = DateTime) [7/25/2016 5:37:57 PM]
-- Context: Devart.Data.SqlServer.Linq.Provider.SqlDataProvider Mapping: AttributeMappingSource Build: 4.5.1024.0

Note that the DbType for the argument is DateTime when it should be DateTime2 as specified by the attribute DbType = "DATETIME2 NOT NULL".

I've repeated this in both 4.4.563 and 4.5.1024.

[aerotog]

The same query using the built in LINQ to SQL ORM works as expected. Logged query:

Code: Select all

SELECT [t0].[Id], [t0].[EventTime]
FROM [foo].[Bar] AS [t0]
WHERE [t0].[EventTime] = @p0
-- @p0: Input DateTime2 (Size = -1; Prec = 0; Scale = 0) [7/25/2016 9:00:33 PM]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.6.1055.0

Note the argument is passed as a DateTime2.

[Shalex]

Thank you for the test project. We will investigate the issue and notify you about the result.

[Shalex]

The bug with mapping DateTime2 database columns via DbType.DateTime2 parameter is fixed in the newest (4.5.1051) build of LinqConnect.