public void ShouldConnectToChinookWithEF()
{
var projectsFolder = this.TestContext.ShouldGetAssemblyDirectoryParent(this.GetType(), expectedLevels: 2);
AppDomain.CurrentDomain.SetData("DataDirectory", projectsFolder);
using (var context = new ChinookDbContext())
{
context.Database.Connection.Open();
Assert.AreEqual(ConnectionState.Open, context.Database.Connection.State);
}
}
private void AddCustomers(ChinookDbContext context, int quantity)
{
var customerList = A.ListOf <Models.Customer>(quantity);
for (int i = 1; i <= quantity; i++)
{
customerList[i - 1].CustomerId = i;
}
context.Customers.AddRange(customerList);
context.SaveChanges();
}
private void verify_sample_data_in_database(ChinookDbContext db)
{
var digital_download = db.media_types.Where(mt => mt.Name == "Digital Download").Single();
var sample_artist = db.artists.Where(artist => artist.Name == "Bob Sacamano").Single();
var playlists = db.playlists.ToList();
var playlist = db.playlists.Include("tracks").FirstOrDefault();
Assert.IsNotNull(playlist);
Assert.IsNotNull(playlist.tracks);
Assert.AreEqual(1, playlist.tracks.Count);
Assert.AreEqual(1, playlist.tracks.First().TrackId);
}
private void AddAlbum(ChinookDbContext context, int quantity)
{
var albumList = A.ListOf <Album>(quantity);
for (int i = 1; i <= quantity; i++)
{
albumList[i - 1].AlbumId = i;
}
context.Album.AddRange(albumList);
context.SaveChanges();
}
private void Add_Click(object sender, RoutedEventArgs e)
{
using (var db = new ChinookDbContext())
{
var dvd_media_type = new media_type {
Name = NewMediaType.Text
};
db.media_types.Add(dvd_media_type);
db.SaveChanges();
MediaTypes.ItemsSource = db.media_types.ToList();
}
}
public void ShouldConnectToChinookWithEF()
{
var projectsFolder = this.TestContext.ShouldGetAssemblyDirectoryParent(this.GetType(), expectedLevels: 2);
AppDomain.CurrentDomain.SetData("DataDirectory", projectsFolder);
var builder = new DbContextOptionsBuilder <ChinookDbContext>()
.UseSqlite("Name=Chinook");
using (var context = new ChinookDbContext(builder.Options))
{
var connection = context.Database.GetDbConnection();
connection.Open();
Assert.AreEqual(ConnectionState.Open, connection.State);
}
}
public ActionResult CanadianCustomers()
{
using (var chinookContext = new ChinookDbContext())
{
//first and last name of customer from canada order by last name
List <CanadianCustomerViewModel> canadianCustomers = chinookContext.Customer
.Where(cust => cust.Country.ToLower() == "canada")
.OrderBy(a => a.LastName)
.Select(cust => new CanadianCustomerViewModel
{
LastName = cust.LastName,
FirstName = cust.FirstName
}).ToList();
return(View(canadianCustomers));
}
}
private ChinookDbContext create_db_file(string filepath)
{
var db = new ChinookDbContext(filepath);
// file is only created on call to Migrate
db.Database.Migrate();
// verify the expected file was created
if (!System.IO.File.Exists(filepath))
{
Assert.Fail("File: " + filepath + " was not created as expected.");
}
// verify that the tables were created
var num_tracks = db.tracks.Count();
return(db);
}
public ChinookEasyConsole()
: base("EasyConsole Demo", breadcrumbHeader: true)
{
using (var db = new ChinookDbContext())
{
db.Database.Migrate();
}
AddPage(new MainPage(this));
AddPage(new MediaTypesMenuPagePlus(this));
AddPage(new MediaTypesAddPage(this));
AddPage(new MediaTypesListPage(this));
AddPage(new GenresDeletePage(this));
AddPage(new GenresAddPage(this));
AddPage(new GenresListPage(this));
AddPage(new GenresMenuPagePlus(this));
SetPage <MainPage>();
}
public static void DemoLINQSQL()
{
Console.WriteLine("\nLINQ -> SQL\n");
ChinookDbContext context = new ChinookDbContext();
IQueryable <Album> query;
string sql;
// LINQ - Lambda Syntax
query = context.Albums
.Where(x => x.AlbumId < 10)
.OrderByDescending(x => x.Title)
.Include(x => x.Artist);
//.Include("Tracks");
sql = query.ToString();
Console.WriteLine(sql);
//SELECT
// [Extent1].[AlbumId] AS [AlbumId],
// [Extent1].[Title] AS [Title],
// [Extent1].[ArtistId] AS [ArtistId],
// [Extent2].[ArtistId] AS [ArtistId1],
// [Extent2].[Name] AS [Name]
//FROM
// [dbo].[Album] AS [Extent1]
// INNER JOIN [dbo].[Artist] AS [Extent2] ON
// [Extent1].[ArtistId] = [Extent2].[ArtistId]
//WHERE
// [Extent1].[AlbumId] < 10
//ORDER BY
// [Extent1].[Title] DESC
// Dynamic LINQ - Lambda Syntax
query = context.Albums
.Where("AlbumId < @0", 10)
.OrderBy("Title descending")
.Include(x => x.Artist);
//.Include("Tracks");
sql = query.ToString();
Console.WriteLine(sql);
}
private void add_sample_data_to_db()
{
try
{
using (var db = new ChinookDbContext())
{
var playlist = db.playlists.Include("tracks").FirstOrDefault();
if (playlist == null)
{
var new_track = new track()
{
Name = "Bad Medicine"
};
var new_playlist = new playlist()
{
PlaylistId = 0, Name = "Test playlist"
};
var ref_playlist_track = new playlist_track()
{
playlist = new_playlist, track = new_track
};
new_playlist.tracks.Add(ref_playlist_track);
db.playlists.Add(new_playlist);
db.SaveChanges();
Tracks.ItemsSource = new_playlist.tracks;
}
else
{
Tracks.ItemsSource = playlist.tracks; // db.playlists.Include("tracks").ToList();
}
}
}
catch (Exception ex)
{
System.Console.WriteLine("Error: \n" + ex.Message);
if (ex.InnerException != null)
{
System.Console.WriteLine("Inner Exception: \n" + ex.InnerException.Message);
}
}
}
public void Display(string prompt, Action <ChinookDbContext, string> processor)
{
bool done = false;
var new_value = "";
while (!done)
{
base.Display();
new_value = Input.ReadString(prompt);
if (new_value != "")
{
using (var db = new ChinookDbContext())
{
try
{
processor(db, new_value);
db.SaveChanges();
new_value = "";
}
catch (Exception ex)
{
Output.WriteLine(ex.Message);
if (ex.InnerException != null)
{
Output.WriteLine(ex.InnerException.Message);
}
Input.ReadString("Press Enter");
}
}
Output.Clear();
}
else
{
done = true;
}
}
Program.NavigateBack();
}
public GetPlaylistResourceCollectionHandler(ChinookDbContext chinookDbContext)
{
_chinookDbContext = chinookDbContext;
}
public GetInvoiceResourceToCustomerResourceHandler(ChinookDbContext chinookDbContext)
{
_chinookDbContext = chinookDbContext;
}
public GetInvoiceItemResourceToTrackResourceHandler(ChinookDbContext chinookDbContext)
{
_chinookDbContext = chinookDbContext;
}
public GetTrackResourceHandler(ChinookDbContext chinookDbContext)
{
_chinookDbContext = chinookDbContext;
}
public GetMediaTypeResourceToTrackResourceCollectionHandler(ChinookDbContext chinookDbContext)
{
_chinookDbContext = chinookDbContext;
}
public GetCustomerResourceToInvoiceResourceCollectionHandler(ChinookDbContext chinookDbContext)
{
_chinookDbContext = chinookDbContext;
}
public GetTackResourceToAlbumResourceHandler(ChinookDbContext chinookDbContext)
{
_chinookDbContext = chinookDbContext;
}
public HomeController(ChinookDbContext db)
{
this.db = db;
}
public GenericRepository(ChinookDbContext context)
{
_context = context;
}
public GetGenreResourceCollectionHandler(ChinookDbContext chinookDbContext)
{
_chinookDbContext = chinookDbContext;
}
public MusicStoreController(ChinookDbContext dbContext, IShoppingCartService shoppingCartService)
{
DbContext = dbContext;
ShoppingCartService = shoppingCartService;
}
public GetTackResourceToMediaTypeResourceHandler(ChinookDbContext chinookDbContext)
{
_chinookDbContext = chinookDbContext;
}
public GetInvoiceItemResourceCollectionHandler(ChinookDbContext chinookDbContext)
{
_chinookDbContext = chinookDbContext;
}
public GetArtistResourceToAlbumResourceCollectionHandler(ChinookDbContext chinookDbContext)
{
_chinookDbContext = chinookDbContext;
}
public GetCustomerResourceHandler(ChinookDbContext chinookDbContext)
{
_chinookDbContext = chinookDbContext;
}
public static void PersistenceLINQEntityFrameworkDemo()
{
Console.WriteLine("\nPersistence Chinook LINQ Entity Framework Demo\n");
ChinookDbContext context = new ChinookDbContext();
// EF < 6.0 Log
//context.Configuration.LazyLoadingEnabled = false; // DEFAULT
//context.Configuration.ProxyCreationEnabled = false; // DEFAULT
//context.Database.Log = log => EntityFrameworkHelper.Log(log, ZDatabaseLogger.File);
// EF 6.0 Log
DbInterception.Add(new NLogCommandInterceptor()); // Entity Framework
IQueryable <Album> query;
IEnumerable <Album> collection;
Album album;
JsonSerializerSettings settings;
string json;
// LINQ - Query Syntax
query =
from x in context.Albums
where x.AlbumId == 1
orderby x.Title descending
select x;
collection = query
.ToList();
Console.WriteLine(collection.Count().ToString() + " Album(s).");
settings = new JsonSerializerSettings
{
//Formatting = Formatting.Indented,
Formatting = Formatting.None,
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
};
json = JsonConvert.SerializeObject(collection, settings);
query =
from x in context.Albums
where x.AlbumId == 1
orderby x.Title descending
select x;
collection = query
.Include(x => x.Artist) // System.Data.Entity
//.Include("Artist") // System.Data.Entity
//.Include(x => x.Tracks) // System.Data.Entity
.Include("Tracks") // System.Data.Entity
.ToList();
Console.WriteLine(collection.Count().ToString() + " Album(s) by " +
collection.FirstOrDefault().Artist.Name + " with " +
collection.FirstOrDefault().Tracks.Count().ToString() + " Track(s).");
// A circular reference was detected while serializing an object of type 'Chinook.Album'.
//json = new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(collection.ToArray<Album>());
// Self referencing loop detected with type 'Chinook.Album'. Path '[0].Artist.Albums'.
//json = JsonConvert.SerializeObject(ie);
// Newtonsoft.Json :-)
settings = new JsonSerializerSettings
{
//Formatting = Formatting.Indented,
Formatting = Formatting.None,
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
};
json = JsonConvert.SerializeObject(collection, settings);
// LINQ - Lambda Syntax
query = context.Albums
.Where(x => x.AlbumId == 1)
.OrderByDescending(x => x.Title);
collection = query
.ToList();
Console.WriteLine(collection.Count().ToString() + " Album(s).");
// The method 'Skip' is only supported for sorted input in LINQ to Entities.
// The method 'OrderBy' must be called before the method 'Skip'.
query = context.Albums
.Where(x => x.AlbumId >= 1)
.OrderByDescending(x => x.Title)
.Skip(0)
.Take(10);
collection = query
.ToList();
Console.WriteLine(collection.Count().ToString() + " Album(s).");
query = context.Albums
.Where(x => x.AlbumId == 1)
.OrderByDescending(x => x.Title)
.Include(x => x.Artist) // System.Data.Entity
//.Include("Artist") // System.Data.Entity
//.Include(x => x.Tracks) // System.Data.Entity
.Include("Tracks"); // System.Data.Entity
collection = query
.ToList();
Console.WriteLine(collection.Count().ToString() + " Album(s) by " +
collection.FirstOrDefault().Artist.Name + " with " +
collection.FirstOrDefault().Tracks.Count().ToString() + " Track(s).");
album = query.FirstOrDefault(); // SQL
context.Entry(album).Reference(x => x.Artist).Load(); // SQL
context.Entry(album).Collection(x => x.Tracks).Load(); // SQL
Console.WriteLine("\nAlbum [" + album.Title +
"] by [" + album.Artist.Name +
"] with " + album.Tracks.Count() + " Track(s).");
// Dynamic LINQ - Lambda Syntax
query = context.Albums
.Where("AlbumId == @0", 1)
.OrderBy("Title descending");
collection = query
.ToList();
Console.WriteLine(collection.Count().ToString() + " Album(s).");
query = context.Albums
.Where("AlbumId == @0", 1)
.OrderBy("Title descending");
collection = query
.Include("Artist") // System.Data.Entity
.Include("Tracks") // System.Data.Entity
.ToList();
Console.WriteLine(collection.Count().ToString() + " Album(s) by " +
collection.FirstOrDefault().Artist.Name + " with " +
collection.FirstOrDefault().Tracks.Count().ToString() + " Track(s).");
album = query.FirstOrDefault(); // SQL
context.Entry(album).Reference("Artist").Load(); // SQL
context.Entry(album).Collection("Tracks").Load(); // SQL
Console.WriteLine("\nAlbum [" + album.Title +
"] by [" + album.Artist.Name +
"] with " + album.Tracks.Count() + " Track(s).");
}
public GetGenreResourceHandler(ChinookDbContext chinookDbContext)
{
_chinookDbContext = chinookDbContext;
}
public MusicTracksController(ChinookDbContext dbContext)
{
DbContext = dbContext;
}
