Efcore: .HasDefaultValueSql("getdate()"); doesn't work with sqlite

[papamamadoii]

When I run unit test using xUnit with .HasDefaultValueSql("getdate()") the test always failed. The error is:

Message: Microsoft.EntityFrameworkCore.DbUpdateException : An error occurred while updating the entries. See the inner exception for details.
---- Microsoft.Data.Sqlite.SqliteException : SQLite Error 1: 'unknown function: getdate()'.

I follow this guide https://docs.microsoft.com/en-us/ef/core/miscellaneous/testing/sqlite and https://docs.microsoft.com/en-us/ef/core/modeling/relational/default-values.

It works just fine in SQL server though.

[ralmsdeveloper]

I believe you should use .HasDefaultValueSql ("date('now')")

Sqlite does not support this command. That's why you would not.
Some commands are peculiar to databases.
This is a simple way to explain.
Take a look at the link below.

https://sqlite.org/lang_datefunc.html

c/ @bricelam

[papamamadoii]

solved. Thank you!

[bricelam]

@ralmsdeveloper Sounds like you're ready to take on #4108 😉

[arunprasathv]

@bricelam
Does it work for both SQL Server and SQL Lite ?
.HasDefaultValueSql("date('now')");

changed from .HasDefaultValueSql("getdate()") in context.

[bricelam]

CURRENT_TIMESTAMP is available on both SQL Server and SQLite.

[arunprasathv]

is this valid ?
entity.Property(e => e.CreateDate)
.HasColumnType("datetime")
.HasDefaultValueSql("CURRENT_TIMESTAMP");

[JakePorter05]

I am using HasDefaultValueSql("newid()"); which works for SQL server but not SQLite since SQLite doesn't have this function. Any tips for a way to get it too work in both SQL server and also SQLite?

[bricelam]

It's not technically a Guid, but randomblob(16) will give you 16 random bytes on SQLite...

[bricelam]

See this answer on Stack Overflow for a more correct solution.

[jsgoupil]

I don't have the possibility to change the C# code generated because I use Scaffold-DbContext. However, I can make some changes to the database. So I tried the following:

ALTER TABLE [dbo].[Projects] DROP CONSTRAINT [DF_projects_date_created]
ALTER TABLE [dbo].[Projects] ADD CONSTRAINT [DF_projects_date_created] DEFAULT CURRENT_TIMESTAMP FOR [date_created]

However, when I search what is my COLUMN_DEFAULT, I always get (getdate())

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='projects' and COLUMN_NAME='date_created'

Is there a way to go around this?