使用 Entity Framework 7 进行 SQLite 的 CURD 操作
原文地址：http://www.oschina.net/translate/sqlite-crud-operation-using-entity-framework
介绍
我善于使用传统的SQL查询风格，从SQL查询解析和转换出无类型的结果，EF给表的操作带来更多的舒适性和生产力：操作的是强类型对象 (比如 Employee, Student, 等等，而不是 row["FirstName"], Int32.Parse(reader["Id"].ToString()), 等等这些东西。), 代码会通过Visual Studio给出错误提示，而且是编译时的，而不是运行时的。
你也许想看看我是如何将 传统的ADO.NET封装运用于SQLite 的。
在读过《SQLite上的Entity Framework 5》 以及 《将Entity Framework 7用于SQLite的编码入门》之后，我已经决定尝试自己写写代码。本文就是我的一些心得。
有 一些限制, 注入建模和移植方面。不过并不是大问题 (见上手提示部分)。
你所需要的就是通过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），而一个专辑拥有许多的音乐作品（track），音乐作品则必须是诸多媒体类型的其中 之一，还有就是风格（genre）也会有许多的音乐作品。最后，一个播放列表（playlist）可以包含许多的音乐作品，并且如我们之前提到过的，一个音乐作品也可以出现在许多个播放列表上。
开始创建一个新的控制台工程，叫做ChinookMediaStore。搜索 NuGet 包，关键词是 'Entity Framework SQLite', 将 'Include prerelease' （包含之前的版本）复选框选上, 然后安装这个包，如下：
此时（2015年2月3日）用于SQLite的EF7的最新版本是 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
</playlisttrack>
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
</playlisttrack>
如我们的代码中所示，我们手动地为PlaylistTrack表设置一个组合键，使用流畅的API将其作为两个实体（Playlist和Track）间多对多关系的标记信号。
还有就是，链接字符串只是简单的表明了SQLite数据库文件的位置，其名称被硬编码成了 'Chinook.db' ，与可执行文件处在相同的目录中(大部分时候是 Debug (或者有时是 Release) 文件夹下面)。我们也可以将它设置为相对或者绝对路径。
首次运行
现在向Main()添加一些代码来测试创建数据库，还有其基于我们的模型的定义:
static void Main(string[] args)
{
using (var context = new MyDbContext())
{
context.Database.EnsureCreated();
}
//Console.ReadKey();
}
运行 Main(), 然后看看output文件夹，我们将会看到一个新创建的数据库，叫做Chinook.db。 通过你的SQLite工具查看一下这个数据库 (我使用的是一个叫做 'SQLite Manager' 的Firefox扩展) 的定义：
注意，如果相同的数据库还不存在，那么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
}
}
</playlisttrack>
运行程序，我们会看到新插入的数据如下:
* 注意:
如果你新增了一个Album，其外键 (ArtistId) 并没有对应到一个现有Artist的主键，那你就会收到一个 SQLite 'FOREIGN KEY' 约束异常。 因此基本的想法就是，首先添加”父“表（Artist）的数据，对它进行保存，然后输入”子“表(Album)的数据, 限制就是每个新加的专辑都会有外键引用一个现有艺术家的主键。这常常是通过一个下拉列表 (HTML), 或者是 combo-box (WPF)来实现的。所以一对多关系 (一个 Artist 对应多个 Album), (一个 Album 包含多个 Track), (一个音乐作品必须是现有媒体类型的其中一个), 还有 (Genre - Tracks: 多个 Track 属于一个特定的 Genere)同样如此。
这就是为什么你会看到上线文中的 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), 因为违背了外键约束，所以会发生一个异常的抛出。
