使用 Entity Framework 7 进行 SQLite CRUD 操作
4.94/5 (23投票s)
Chinook 数字媒体商店数据库示例
引言
虽然我乐于使用传统的 SQL 查询风格，解析和转换 SQL 查询的非类型化结果，但 EF 带来了更多的便利和生产力：使用强类型对象（例如 Employee、Student 等，而不是 row["FirstName"]、Int32.Parse(reader["Id"].ToString()) 等），Visual Studio 的代码提示，以及编译时而非运行时错误。
您可能想看看我是如何使用 SQLite 的传统 ADO.NET 包装器。
在阅读了 Entity Framework 5 on SQLite 和 CodeFirst with SQLite using Entity Framework 7 后，我决定尝试自己编写代码。本文将介绍我的成果。
存在 一些限制，例如建模和迁移。但这并非大问题（请参阅一些变通方法提示）。
通过 NuGET 安装 EntityFramework.SQLite 即可。该程序包已包含 SQLite 引擎（支持 x86 和 x64 机器）。然后包含 using Microsoft.Data.Entity; 和 using Microsoft.Data.Sqlite;，我们就可以开始工作了。
入门
该项目是关于一个数字媒体商店。首先，让我们看看 Chinook 数据库的模式
我们正在“逆向工程”数据库。目前，我们只关注以下表：Artist、Album、Track、Playlist、MediaType 和 Genre。请注意，PlaylistTrack（具有复合键）只是为了使两个表（Playlist 和 Track）之间建立多对多关系。一个 artist 有多个 album，一个 album 有多个 track，一个 track 必须属于多种媒体类型之一，一个 genre 也有多个 track。最后，一个 playlist 可以包含多个 track，正如我们之前注意到的，一个 track 也可以出现在多个 playlist 中。
启动一个新的控制台项目，命名为 ChinookMediaStore。在 NuGet 包中搜索“Entity Framework SQLite”，勾选“包含预发行版”复选框，然后按如下方式安装程序包
截至目前（2015 年 2 月 3 日），EF7 for SQLite 的最新版本是 **EntityFramework.SQLite 7.0.0-beta8**。
模型
根据以上模式的观察，以下是我们的实体
#region Models
public class Artist
{
public int ArtistId { get; set; }
public string Name { get; set; }
public virtual ICollection<album> Albums { get; set; }
= new HashSet<album>();
}
public class Album
{
public int AlbumId { get; set; }
public string Title { get; set; }
public int ArtistId { get; set; }
public virtual Artist Artist { get; set; }
public virtual ICollection<track /> Tracks { get; set; }
= new HashSet<track />();
}
public class MediaType
{
public int MediaTypeId { get; set; }
public string Name { get; set; }
public virtual ICollection<track /> Tracks { get; set; }
= new HashSet<track />();
}
public class Genre
{
public int GenreId { get; set; }
public string Name { get; set; }
public virtual ICollection<track /> Tracks { get; set; }
= new HashSet<track />();
}
public class Track
{
public int TrackId { get; set; }
public string Name { get; set; }
public double UnitPrice { get; set; } = 0.99;
public int AlbumId { get; set; }
public Album Album { get; set; }
public int GenreId { get; set; }
public Genre Genre { get; set; }
public int MediaTypeId { get; set; }
public MediaType MediaType { get; set; }
public virtual ICollection<playlisttrack> PlaylistTracks { get; set; }
= new HashSet<playlisttrack>();
}
public class Playlist
{
public int PlaylistId { get; set; }
public string Name { get; set; }
public virtual ICollection<playlisttrack> PlaylistTracks { get; set; }
= new HashSet<playlisttrack>();
}
public class PlaylistTrack
{
// Composite key (PlaylistId & TrackId)
// Many-to-many relationship between Playlist and Track table
public int PlaylistId { get; set; }
public Playlist Playlist { get; set; }
public int TrackId { get; set; }
public Track Track { get; set; }
}
#endregion
DbContext
我在 EF7 中注意到的两个不同之处是：(1) 表名的复数形式不是默认的。以及 (2) 多对多关系默认不被约定识别（目前）。至于表名，我个人更喜欢单数名称。所以，这是我们的 DbContext
#region DbContext
public class MyDbContext : DbContext
{
#region DbSet
public DbSet<artist> Artists { get; set; }
public DbSet<album> Albums { get; set; }
public DbSet<mediatype> MediaTypes { get; set; }
public DbSet<genre> Genres { get; set; }
public DbSet<track /> Tracks { get; set; }
public DbSet<playlist> Playlists { get; set; }
public DbSet<playlisttrack> PlaylistTracks { get; set; }
#endregion
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<playlisttrack>()
.HasKey(pT => new { pT.PlaylistId, pT.TrackId });
base.OnModelCreating(modelBuilder);
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
var sqliteConn = new SqliteConnection(@"DataSource = Chinook.db");
optionsBuilder.UseSqlite(sqliteConn);
}
}
#endregion
如我们的 DbContext 所示，我们手动设置了 PlaylistTrack 表的复合键，作为通过 Fluent API 实现两个实体（Playlist 和 Track）之间多对多关系的信号。
此外，连接字符串只是显示 SQLite 数据库文件的位置，该文件被硬编码为在可执行文件的同一输出目录（通常是 Debug（或有时是 Release）文件夹）中命名为“Chinook.db”。我们也可以设置相对路径或绝对路径。
首次运行
现在，在 Main() 中添加一些代码来测试根据我们的模型创建数据库及其模式
static void Main(string[] args)
{
using (var context = new MyDbContext())
{
context.Database.EnsureCreated();
}
//Console.ReadKey();
}
运行 Main()，然后查看输出文件夹，我们将看到一个新创建的名为 Chinook.db 的数据库。使用您的 SQLite 工具（我使用的是 FireFox 扩展“SQLite Manager”）浏览此数据库，查看其模式
请注意，如果不存在相同的数据库，则 EF7 将根据我们的 DBContext（和我们的模型类）的定义创建一个。如果已存在，则不会采取任何措施来确保其与我们当前的上下文和模型兼容，程序将继续使用已存在的数据库。
向我们的数据库表添加数据
创建一个名为 InsertData() 的方法，并从 Main() 调用它，以插入一些虚拟数据
private static void InsertData()
{
Artist aArtist = new Artist { Name = "Artist A" };
List<artist> someArtists = new List<artist>
{
new Artist { Name = "Artist B" },
new Artist { Name = "Artist C" }
};
Artist anotherArtist = new Artist
{
Name = "D",
// Making user of 'new HashSet<album>()' initialized in Artist model
Albums =
{
new Album { Title = "D's 1st Album" },
new Album { Title = "D's 2nd Album" }
}
};
List<album> someAlbums = new List<album>
{
new Album { Title = "Album X", ArtistId = 1 },
new Album { Title = "Album Y", ArtistId = 3 },
new Album { Title = "Album Z", ArtistId = 2 }
};
List<mediatype> someMediaTypes = new List<mediatype>
{
new MediaType { Name = "Mp3 Type" },
new MediaType { Name = "AAC Type" }
};
List<genre> someGenres = new List<genre>
{
new Genre { Name = "Genre A" },
new Genre { Name = "Genre B" }
};
List<playlist> somePlaylists = new List<playlist>
{
new Playlist { Name = "Playlist A" },
new Playlist { Name = "Playlist B" }
};
List<track /> someTracks = new List<track />
{
new Track { Name = "Track 001",
AlbumId = 1, MediaTypeId = 1, GenreId = 1 },
new Track { Name = "Track 002",
AlbumId = 1, MediaTypeId = 1, GenreId = 2 },
new Track { Name = "Track 003",
AlbumId = 2, MediaTypeId = 2, GenreId = 1, UnitPrice = 2.99 },
new Track { Name = "Track 004",
AlbumId = 1, MediaTypeId = 2, GenreId = 1 },
new Track { Name = "Track 005",
AlbumId = 3, MediaTypeId = 1, GenreId = 2, UnitPrice = 3.99 }
};
List<playlisttrack> somePlaylistTracks = new List<playlisttrack>
{
new PlaylistTrack { PlaylistId = 2, TrackId = 1 }
};
using (var context = new MyDbContext())
{
context.Artists.Add(aArtist);
context.Artists.AddRange(someArtists);
context.SaveChanges(); // Persist data to database
context.Albums.AddRange(someAlbums);
context.MediaTypes.AddRange(someMediaTypes);
context.Genres.AddRange(someGenres);
context.Playlists.AddRange(somePlaylists);
context.Tracks.AddRange(someTracks);
context.SaveChanges(); // Persist data to database
context.PlaylistTracks.AddRange(somePlaylistTracks);
context.Artists.Add(anotherArtist);
context.SaveChanges(); // Persist data to database
}
}
运行程序，我们将看到新插入的数据，如下所示
* 注意
如果您尝试添加一个 Album，其外键 (ArtistId) 与现有 Artist 的主键不对应，您将收到一个 **SQLite 'FOREIGN KEY' 约束异常**。所以基本思想是，我们先输入“父”表（Artist）的数据，保存它，然后输入“子”表（Album）的数据，限制是每个新的 album 都必须有一个外键指向一个现有的 artist 的主键。通常，这可以通过下拉列表（HTML）或组合框（WPF）来实现。因此，与（一个 Artist 对应多个 Album）、（一个 Album 包含多个 Track）、（一个 track 必须属于多种现有媒体类型之一）以及（Genre - Track：多个 Track 属于特定的 Genre）的关系相同。
这就是为什么您会看到多次调用上下文的 SaveChanges()。EF7 默认启用外键约束（以及唯一约束）是一件好事。
检索数据
继续从 CodePlex 下载一份示例 SQLite Chinook 数据库。解压 zip 文件，您要使用的是“Chinook_Sqlite_AutoIncrementPKs.sqlite”。将其重命名为“Chinook.db”（根据我们在 DbContext 中的连接字符串），并将其复制到您的 Debug 文件夹（如果已存在则覆盖）。原因在于，示例数据库包含许多可供玩弄的优秀数据。
创建一个名为 SelectData() 的方法，然后从 Main() 调用它。
private static void SelectData()
{
using (var context = new MyDbContext())
{
#region Get all albums that contain the track with the word 'Love' in its title
var query = context.Tracks
.Include(t => t.Album)
.Where(t => t.Name.Contains("Love"))
.Select(t => t.Album)
.Distinct();
Console.WriteLine($"Number of albums satisfied the condition: {query.Count()}");
foreach (Album item in query)
{
Console.WriteLine($"\t {item.Title}");
}
}
}
输出结果
另一个例子
#region Get all tracks with price > $1.00
var query2 = context.Tracks
.Where(t => t.UnitPrice > 1.00);
Console.WriteLine($"Number of tracks with price greater than $1.00: {query2.Count()} \n");
#endregion
#region Get all playlists that contain track with Id 1
var query3 = context.Tracks
.Include(t => t.PlaylistTracks)
.ThenInclude(t => t.Playlist)
.Where(t => t.TrackId == 1)
.Single();
var playlists = query3.PlaylistTracks
.Select(p => p.Playlist);
Console.WriteLine($"Number of playlists with track Id 1 is: {playlists.Count()}");
foreach (Playlist p in playlists)
{
Console.WriteLine($"\t Id = {p.PlaylistId}, Name = {p.Name}");
}
#endregion
结果是
更新和删除数据
再次，创建一个方法并从 Main() 调用它。
private static void UpdateAndDeleteData()
{
#region Change the name of the track with Id 2 to "No Name"
using (var context = new MyDbContext())
{
var track = context.Tracks
.Where(t => t.TrackId == 2)
.Single();
track.Name = "No Name";
context.SaveChanges();
}
#endregion
#region Delete all tracks with Id > 3507
using (var context = new MyDbContext())
{
var tracks = context.Tracks
.Where(t => t.TrackId > 3507);
context.Tracks.RemoveRange(tracks);
context.SaveChanges();
}
#endregion
}
请注意，如果您尝试删除父表（例如 Artist）中的行，而在子表（例如 Album）中存在对其的引用，则会因外键约束冲突而发生异常。