使用Entity Framework 7进行SQLite的CURD操作

2016-2-24 22:26| 发布者: joejoe0332| 查看: 2341| 评论: 0|原作者: leoxu|来自: oschina

摘要: 我善于使用传统的SQL查询风格，从SQL查询解析和转换出无类型的结果，EF给表的操作带来更多的舒适性和生产力：操作的是强类型对象 (比如 Employee, Student, 等等，而不是 row, Int32.Parse(reader.ToString()), 等等 ...

介绍

我善于使用传统的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 &apos;new HashSet<album>()&apos; initialized in Artist model
                Albums =
                {
                    new Album { Title = "D&apos;s 1st Album" },
                    new Album { Title = "D&apos;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 &apos;Love&apos; 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
}