[EFCore 5, SQLite3]: how to create table without ROWID

Question

I have a model with non-integer primary key. TEXT can't be a alias for ROWID and sqlite creates a ROWID column automatically. I can disable ROWID when I make a scheme manually, but in EF I do not know how to do it.

EF creates table like this:

CREATE TABLE text_data (
    id          TEXT NOT NULL PRIMARY KEY,
    text        TEXT NOT NULL
);

But I want to table be like this:

CREATE TABLE text_data (
    id          TEXT NOT NULL PRIMARY KEY,
    text        TEXT NOT NULL
) WITHOUT ROWID;

What can I tell EF to disable ROWID?

At the moment I don't think there's a way: https://github.com/dotnet/efcore/blob/main/src/EFCore.Sqlite.Core/Migrations/SqliteMigrationsSqlGenerator.cs line 702. Your best bet is to create a subsequent migration that executes sql that drops and rebuilds the table, though this will be problematic for future migrations. — nh43de, Mar 30 '21 at 14:50

Maxim Mikhisor · Answer 1 · 2022-03-17T01:32:48.130

public class MyRelationalCommandBuilder : RelationalCommandBuilder
{
    static readonly String _beginCreateTable = "CREATE TABLE";
    static readonly String _endCreateTable = ");";
    static readonly String _withoutRowId = ") WITHOUT ROWID;";

    public MyRelationalCommandBuilder(RelationalCommandBuilderDependencies dependencies) : base(dependencies)
    {           
    }

    public override IRelationalCommand Build() => new RelationalCommand(Dependencies, _fixCreateTableCommand(), Parameters);        

    String _fixCreateTableCommand()
    {
        String originalCommandText = base.ToString();

        Int32 startCreateTableIndex = originalCommandText.IndexOf(_beginCreateTable);

        if (startCreateTableIndex < 0 || originalCommandText.Contains(_withoutRowId) || originalCommandText.Contains("AUTOINCREMENT"))
            return originalCommandText;            

        Int32 endCreateTableIndex = originalCommandText.IndexOf(_endCreateTable, startCreateTableIndex);

        String createTableSubstring = originalCommandText.Substring(startCreateTableIndex, endCreateTableIndex - startCreateTableIndex + _endCreateTable.Length);

        String newCreateTableSubstring = createTableSubstring.Replace(_endCreateTable, _withoutRowId);

        String newCommandText = originalCommandText.Replace(createTableSubstring, newCreateTableSubstring);

        return newCommandText;
    }
}

public class MyRelationalCommandBuilderFactory : RelationalCommandBuilderFactory
{
    public MyRelationalCommandBuilderFactory(RelationalCommandBuilderDependencies dependencies) : base(dependencies)
    {
    }

    public override IRelationalCommandBuilder Create() => new MyRelationalCommandBuilder(Dependencies);
}

public class MyDbContext : DbContext
{
    protected void OnConfiguring(DbContextOptionsBuilder optionsBuilder, String dbFilePath)
    {
        base.OnConfiguring(optionsBuilder);

        optionsBuilder               
            .UseSqlite()            
            .ReplaceService<IRelationalCommandBuilderFactory, MyRelationalCommandBuilderFactory>();
    }
}

[EFCore 5, SQLite3]: how to create table without ROWID

1 Answers1