Introduction
While I'm fine working with traditional SQL query style, parsing and converting un-typed results from SQL query, EF brings more comfort and productivity to the table: working with strong typed objects (such as Employee, Student, etc. rather than row["FirstName"], Int32.Parse(reader["Id"].ToString()), etc.), code hints from Visual Studio, and compiled-time instead of run-time error.
You might want to have a look at how I used traditional ADO.NET wrapper for SQLite.
After reading Entity Framework 5 on SQLite and CodeFirst with SQLite using Entity Framework 7, I have decided to try my own code. This article is about what I have got.
There are some limitations, such as modeling and migration. But it wouldn't be a big deal (see some walkaround tips).
Install EntityFramework.SQLite via NuGET is all you need. The package already includes the SQLite engine (for both x86 and x64 machine). Then include using Microsoft.Data.Entity; and using Microsoft.Data.Sqlite;, we are good to go.
Get Started
The project is about a digital media store. First, let's have a look at the Chinook database schema:
We are 'reverse-engineering' the database. For now, we are just focusing on the following tables: Artist, Album, Track, Playlist, MediaType, and Genre. Notice that PlaylistTrack (has a composite key) is just to make 2 tables (Playlist and Track) a many-to-many relationship. An artist has many albums, an albums has many tracks, a track must be one of many media types, and a genre has many tracks too. Finally, a playlist can contain many tracks, and also a track can appear on many multiple playlists, as we noticed before.
Start a new Console project, called ChinookMediaStore. Search NuGet package for 'Entity Framework SQLite', check the 'Include prerelease' checkbox, then install the package as follows:
The latest version of EF7 for SQLite at this time (Feb 3, 2015) is EntityFramework.SQLite 7.0.0-beta8.
Models
Based on the observation on the schema above, these are our entities:
#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
{
public int PlaylistId { get; set; }
public Playlist Playlist { get; set; }
public int TrackId { get; set; }
public Track Track { get; set; }
}
#endregion
DbContext
Two things I notice the difference in EF7 is: (1) pluralization of the table names is not default. And (2) many-to-many relation is not recognized by default convention (at this time). For the table name, personally, I prefer singular names. So, this is our 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
As shown in our DbContext, we manually set up a composite key for the PlaylistTrack table, as a signal to a many-to-many relationship between two entities (Playlist and Track) using fluent API.
Also, the connection string is simply showing where to the SQLite database file, which is hard-coded named 'Chinook.db' in the same output directory of the executable (mostly the Debug (or sometimes Release) folder). We can also set it relative or an absolute path.
First Run
Now add some code to the Main() to test creating database and its schema based on our models:
static void Main(string[] args)
{
using (var context = new MyDbContext())
{
context.Database.EnsureCreated();
}
}
Run the Main(), then have a look at the output folder, we will see a newly created database named Chinook.db. Explore this database by your SQLite tool (I'm using a FireFox extension named 'SQLite Manager') to see its schema:
Note that, if the same database doesn't exist, then EF7 will create one based on the definition of our DBContext (and our model classes). If it already exists, then NO effort is made to ensure the compatibility of it with our current context and models, and the program just goes ahead and uses the currently existing database.
Add Data To Our db's Tables
Create a method, InsertData(), and call it from Main(), to insert some dummy data:
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",
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();
context.Albums.AddRange(someAlbums);
context.MediaTypes.AddRange(someMediaTypes);
context.Genres.AddRange(someGenres);
context.Playlists.AddRange(somePlaylists);
context.Tracks.AddRange(someTracks);
context.SaveChanges();
context.PlaylistTracks.AddRange(somePlaylistTracks);
context.Artists.Add(anotherArtist);
context.SaveChanges();
}
}
Run the program, we will see the newly inserted data as shown:
* Note
If you add an Album with the foreign key (ArtistId) that is not corresponding to the primary key of an existing Artist, you will get an SQLite 'FOREIGN KEY' constraint exception. So the basic idea is, we input data for the 'parent' table (Artist) first, save it, then input the data for the 'child' table (Album), with the limitation is that each new album has the foreign key referring to one of the existing artist's primary key. Usually, this is implemented as a dropdown-list (HTML), or combo-box (WPF). So the same one-to-many relationship with (an Artist as many Albums), (an Album contains many Tracks), (a track must be one of many existing Media Types), and (Genre - Tracks: many Tracks belong to a particular Genre).
That is why you see the SaveChanges() on the context is called multiple times. It's a nice thing that EF7 by default enables foreign key constraint (as well as unique constraint).
Retrieve Data
Go ahead and download a copy of sample SQLite Chinook database from CodePlex. Extract the zip file, the one you want to use is 'Chinook_Sqlite_AutoIncrementPKs.sqlite'. Rename it to 'Chinook.db' (according to our connection string in DbContext) and copy it (or overwrite if one exists) to your Debug folder. The reason is, the sample database contains many good data to play with.
Create a method, SelectData(), then call it from 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}");
}
}
}
Output result:
Another example:
#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
And the results are:
Update and Delete Data
Again, create a method and call it from 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
}
Note that if you try to delete row(s) from the parent table (Artist, for example) while there exists a reference to it from a child table (Album, for example), an exception will happen because of foreign key constraint violation.
End
