1
using Microsoft.EntityFrameworkCore;
2

3
public class AppDbContext : DbContext
4
{
5
    /// <summary>
6
    /// 用户实体类
7
    /// </summary>
8
    public DbSet<UserEntity> Users{ get; set; }
9

10
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
11
    {
12
        if (!optionsBuilder.IsConfigured)
13
        {
14
            optionsBuilder.UseSqlite("Data Source=database.db"); // 默认存储在程序根目录
15
        }
16
    }
17
}

1
using Microsoft.EntityFrameworkCore;
2
using Microsoft.EntityFrameworkCore.Metadata;
3
using System.Data.Common;
4
using System.Text;
5

6
public static class SqliteSchemaUpdater
7
{
8
    public static void EnsureSchemaSynced(DbContext context, bool log = false)
9
    {
10
        var connection = context.Database.GetDbConnection();
11
        connection.Open();
12

13
        var entityTypes = context.Model.GetEntityTypes();
14
        var existingTables = GetExistingTableNames(connection);
15

16
        // Step 1: DROP tables that are no longer in model
17
        foreach (var oldTable in existingTables)
18
        {
19
            if (!entityTypes.Any(e => e.GetTableName() == oldTable))
20
            {
21
                Log($"Dropping table: {oldTable}", log);
22
                ExecuteNonQuery(connection, $"DROP TABLE IF EXISTS [{oldTable}];");
23
            }
24
        }
25

26
        // Step 2: CREATE or ALTER
27
        foreach (var entityType in entityTypes)
28
        {
29
            var tableName = entityType.GetTableName()!;
30
            var props = entityType.GetProperties();
31

32
            if (!existingTables.Contains(tableName))
33
            {
34
                var createSql = BuildCreateTableSql(tableName, props);
35
                Log($"Creating table: {tableName}", log);
36
                ExecuteNonQuery(connection, createSql);
37
            }
38
            else
39
            {
40
                var existingColumns = GetExistingColumnNames(connection, tableName);
41
                var modelColumns = props.Select(p => p.GetColumnName()).ToList();
42

43
                // Add missing columns
44
                foreach (var p in props)
45
                {
46
                    var columnName = p.GetColumnName();
47
                    if (!existingColumns.Contains(columnName))
48
                    {
49
                        var columnSql = BuildAddColumnSql(p);
50
                        Log($"Adding column: {columnName} to {tableName}", log);
51
                        ExecuteNonQuery(connection, $"ALTER TABLE [{tableName}] ADD COLUMN {columnSql};");
52
                    }
53
                }
54

55
                // Drop obsolete columns
56
                foreach (var oldCol in existingColumns)
57
                {
58
                    if (!modelColumns.Contains(oldCol))
59
                    {
60
                        Log($"Dropping column: {oldCol} from {tableName}", log);
61
                        RecreateTableWithoutColumn(connection, entityType, oldCol, log);
62
                    }
63
                }
64
            }
65
        }
66
    }
67

68
    private static HashSet<string> GetExistingTableNames(DbConnection conn)
69
    {
70
        var tables = new HashSet<string>();
71
        using var cmd = conn.CreateCommand();
72
        cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';";
73
        using var reader = cmd.ExecuteReader();
74
        while (reader.Read())
75
            tables.Add(reader.GetString(0));
76
        return tables;
77
    }
78

79
    private static HashSet<string> GetExistingColumnNames(DbConnection conn, string tableName)
80
    {
81
        var columns = new HashSet<string>();
82
        using var cmd = conn.CreateCommand();
83
        cmd.CommandText = $"PRAGMA table_info([{tableName}]);";
84
        using var reader = cmd.ExecuteReader();
85
        while (reader.Read())
86
            columns.Add(reader.GetString(1));
87
        return columns;
88
    }
89

90
    private static string BuildCreateTableSql(string tableName, IEnumerable<IProperty> props)
91
    {
92
        var cols = props.Select(BuildColumnDefinition);
93
        return $"CREATE TABLE IF NOT EXISTS [{tableName}] ({string.Join(", ", cols)});";
94
    }
95

96
    private static string BuildColumnDefinition(IProperty prop)
97
    {
98
        var name = prop.GetColumnName();
99
        var type = prop.ClrType;
100
        var sqlType = GetSqliteType(type);
101
        var sb = new StringBuilder($"[{name}] {sqlType}");
102

103
        if (prop.IsPrimaryKey()) sb.Append(" PRIMARY KEY");
104
        if (prop.IsPrimaryKey() && (type == typeof(int) || type == typeof(long))) sb.Append(" AUTOINCREMENT");
105

106
        return sb.ToString();
107
    }
108

109
    private static string BuildAddColumnSql(IProperty prop) => BuildColumnDefinition(prop);
110

111
    private static string GetSqliteType(Type type) =>
112
        type == typeof(int) || type == typeof(long) ? "INTEGER" :
113
        type == typeof(double) || type == typeof(float) ? "REAL" :
114
        type == typeof(string) ? "TEXT" :
115
        type == typeof(bool) ? "INTEGER" :
116
        type == typeof(DateTime) ? "TEXT" : "TEXT";
117

118
    private static void ExecuteNonQuery(DbConnection conn, string sql)
119
    {
120
        using var cmd = conn.CreateCommand();
121
        cmd.CommandText = sql;
122
        cmd.ExecuteNonQuery();
123
    }
124

125
    private static void RecreateTableWithoutColumn(DbConnection conn, IEntityType entityType, string dropColumn, bool log)
126
    {
127
        var tableName = entityType.GetTableName()!;
128
        var tempTable = tableName + "_temp";
129
        var props = entityType.GetProperties().Where(p => p.GetColumnName() != dropColumn).ToList();
130

131
        Log($"Recreating table {tableName} without column {dropColumn}", log);
132

133
        // 1. Create temp table
134
        var createTemp = BuildCreateTableSql(tempTable, props);
135
        ExecuteNonQuery(conn, createTemp);
136

137
        // 2. Copy data
138
        var columns = string.Join(", ", props.Select(p => p.GetColumnName()));
139
        ExecuteNonQuery(conn, $"INSERT INTO [{tempTable}] ({columns}) SELECT {columns} FROM [{tableName}];");
140

141
        // 3. Drop original table
142
        ExecuteNonQuery(conn, $"DROP TABLE [{tableName}];");
143

144
        // 4. Rename temp
145
        ExecuteNonQuery(conn, $"ALTER TABLE [{tempTable}] RENAME TO [{tableName}];");
146
    }
147

148
    private static void Log(string message, bool enableLog)
149
    {
150
        if (enableLog)
151
            Logs.Info($"[SchemaUpdater] {message}"); // Logs类可以看我之前的日志方案文章：https://wc.sb/23
152
    }
153
}

1
using var db = new AppDbContext(); // 单例实现，啥时候用啥时候创建，不用定义成全局什么的
2
var user = db.Users.FirstOrDefault(x=>x.Id == "xxx"); // 查数据库，ORM形式，Linq直接查
3
user.UserName = "李四";
4
db.Update(user); // 更新数据
5

6
UserEntity addUser = new UserEntity();
7
addUser.UserName = "张三";
8
db.Add(addUser); // 插入数据
9

10
db.Remove(user); // 删除数据
11

12
db.SaveChanges(); // 每次操作数据库后，都要执行这个保存操作