SqlServer Operand type clash: time is incompatible with numeric

[dlj]

Hello

There is an issue when running with a SQL Server provider. Time columns are converted to numeric values and that causes errors both when creating an querying the tables. Included is a breaking test

OrmLite.TimeProblem.zip

[mythz]

OrmLite is only supported to work with the RDBMS Column types it created. Do you have an example that fails using OrmLite's CreateTable APIs?

If you want to use a Custom Schema you'll likely need to modify built-in Type Converters to handle your C# class to RDBMS column type.

[dlj]

But Time is a default column type in MS SQL.

And CreateTable convert TimeSpan into bigint Columns. That is why it is creating the table as a sql string.

[mythz]

You should be using DropAndCreateTable when submitting repros so you can make sure that the Table is being created by the code and it's not using an existing table created previously elsewhere. I just tried running the example and it failed with a duplicate key exception when trying to add Person2 since it's Id isn't using a [AutoIncrement] or populating an Id manually which suggests it never used OrmLite's table.

After changing it to ensure it uses OrmLite created tables with:

db.DropAndCreateTable<Person>();
db.DropAndCreateTable<Person2>();

// And updating Person2 with:
public class Person2
{
        [AutoIncrement]
        public int Id { get; set; }
}

It ran without issues outputting:

Seed Data 1;Seed Data 2;Seed Data 3;Seed Data 4;Seed Data 5;Seed Data 6;Seed Data 7;Seed Data 8;Seed Data 9;Seed Data 10

Please paste a new Program.cs if you can get it to fail with OrmLite tables.

[mythz]

It seems your issue is wanting to use SQL Server TIME column instead of the default int type which works consistently across all RDBMS providers.

You can use TIME column by registering the appropriate SqlServerTimeConverter Type Converter:

var dbFactory = new OrmLiteConnectionFactory(connectionString, SqlServerDialect.Provider);
SqlServerDialect.Provider.RegisterConverter<TimeSpan>(new SqlServerTimeConverter());

You'll need to use an appropriate Type Converter whenever you want to work with a different RDBMS column type that OrmLite uses by default.

Also you'll want to use the appropriate SQL Server Dialect Provider to match your SQL Server version so it can use the most optimal SQL. The SqlServerDialect assumes you're using the oldest support SQL Server i.e. 2008, if you have SQL Server 2012 use SqlServer2012Dialect instead.