Linq2db create table with DateTime2(3) fails when using in memory db and SQLiteDataProvider

Question

I am trying to create table in memory db using Linq2Db, and SQLiteDataProvider in a netcore3.1 application. And if mapping class has a property with attribute

[Column(DataType=DataType.DateTime2, Precision=3), Nullable ]

it gives me the following syntax error :

Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'near ")": syntax error'.

I dig for the query it generates and its this:

CREATE TABLE [testTable]
(
    [Id]      INTEGER         NOT NULL PRIMARY KEY AUTOINCREMENT,
    [Created] DateTime2(3, )      NULL
)

Here is an example that I'm trying:

using System;
using LinqToDB;
using LinqToDB.Data;
using LinqToDB.Mapping;

namespace InMemoryDb
{
    class Program
    {
        static void Main(string[] args)
        {
            DataConnection.AddConfiguration("default", "Data Source=Sharable;Mode=Memory;Cache=Shared", 
                new LinqToDB.DataProvider.SQLite.SQLiteDataProvider("SQLite.MS"));
            DataConnection.DefaultConfiguration = "default";
            using var db = new DataConnection("default");
            db.CreateTable<TestTable>();
        }
        
        [Table(Schema="dbo", Name="testTable")]
        public class TestTable
        {
            [Column(DataType=DataType.Int32), PrimaryKey, Identity] 
            public int       Id             { get; set; }
            [Column(DataType=DataType.DateTime2, Precision=3), Nullable] 
            public DateTime? Created { get; set; } 
        }
    }
}

why its generates query with DateTime2(3, ) and not a correct one DateTime2(3)?

score 0 · Answer 1 · answered Dec 22 '20 at 01:09

0

Try this as workaround

[Column(DbType="DateTime2(3)",    Nullable          ]

answered Dec 22 '20 at 01:09

IT.

859
4
11

The problem is that the model is autogenerated, and Im trying to write tests to service that use this model, so I rather not touch it. Of course I can fix the model generator. But I thought maybe there is a way to configure the provider so it generate right query – MonstriK Dec 22 '20 at 10:29

Svyatoslav Danyliv · Answer 2 · 2020-12-24T10:52:45.490

You can use possibility of linq2db to define schema for several databases. Note that there is no DateTime type in SQLite.

[Table(Schema="dbo", Name="testTable")]
public class TestTable
{
    [Column(DataType=DataType.Int32), PrimaryKey, Identity] 
    public int       Id             { get; set; }

    [Column(Configuration=ProviderName.SQLite, DataType=DataType.DateTime2), Nullable] 
    [Column(DataType=DataType.DateTime2, Precision=3), Nullable] 
    public DateTime? Created { get; set; } 
}

Linq2db create table with DateTime2(3) fails when using in memory db and SQLiteDataProvider

2 Answers2