Not able to use Computed/Generated columns.

[chravikishore]

Not able to use Computed/Generated columns.

I am using the HasComputedColumnSql extension like below

entityTypeBuilder
               .Property(e => e.ConversionRate)
               .HasComputedColumnSql("(BaseUnitQuantity * 1.0)/Quantity")
               .IsRequired();

and the migration generated the script successfully as below

migrationBuilder.CreateTable(
                name: "MyTable",
                columns: table => new
                {
                    ...............
                    ConversionRate = table.Column<decimal>(nullable: false, computedColumnSql: "(BaseUnitQuantity * 1.0)/Quantity")
                },

But when I update the database it is giving following error

Failed executing DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "MyTable" (
    "ColID" INTEGER NOT NULL CONSTRAINT "PK_MyTable" PRIMARY KEY AUTOINCREMENT,
    "Col1" INTEGER NOT NULL,
    "Quantity" INTEGER NOT NULL,
    "Col2" INTEGER NOT NULL,
    "BaseUnitQuantity" INTEGER NOT NULL,
    "ConversionRate" AS (CAST(BaseUnitQuantity AS DECIMAL)/Quantity),
...........
);
Disposing transaction.
Closing connection to database 'main' on server '.......\bin\Debug\netcoreapp3.1\.....db'.
Closed connection to database 'main' on server '......\bin\Debug\netcoreapp3.1\.....db'.
'....DataContext' disposed.
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'near "AS": syntax error'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteCommand.GetStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
SQLite Error 1: 'near "AS": syntax error'.

EF Core version: 5.0.7
Database provider: Microsoft.EntityFrameworkCore.Sqlite
Target framework: netcoreapp3.1
Operating system: Windows
IDE: Visual Studio 2019 16.3

[AndriySvyryd]

It seems that "(BaseUnitQuantity * 1.0)/Quantity" was transformed to (CAST(BaseUnitQuantity AS DECIMAL)/Quantity). There's nothing in EF Core that could be doing this. Can you create a small repro project to illustrate the issue?

[chravikishore]

Not sure but I still see the same error.

Here I am attaching repro project.
EFMig.zip

[AndriySvyryd]

You need to change

entityTypeBuilder
   .Property(e => e.ConversionRate)
   .HasComputedColumnSql("CAST(BaseUnitQuantity AS DECIMAL)/Quantity")
   .IsRequired();

to

entityTypeBuilder
   .Property(e => e.ConversionRate)
   .HasComputedColumnSql("(BaseUnitQuantity * 1.0)/Quantity")
   .IsRequired();

Also uninstall SQLitePCLRaw.bundle_sqlcipher, seems that it's not compatible with computed columns.

@bricelam Is this a known limitation?

[bricelam]

@bricelam Is this a known limitation?

The SQLCipher package lagged behind the e_sqlite3 ones for a bit, but I think the latest pre-release package are all up-to-date and support computed columns.

[chravikishore]

@AndriySvyryd @bricelam
Thank you. Updating to SQLCipher package to pre-release worked.