public List <SelectionList> Artist_List()
{
using (var context = new ChinookSystemContext())
{
//due to the fact that he entities will be internal
// you will NOT be able to use the entity defintions (classes)
// as the return datatypes
//instead, we will create ViewModel classes that will contain
// the data definition for your return data types
//to fill these view model classes, we will use Linq queries
//Linq queries return their data as IEnumerable or IQueryable datasets
//you can use var when declaring your query receiving variables
//this Linq query uses the syntax method for coding
var results = from x in context.Artists
select new SelectionList
{
ValueId = x.ArtistId,
DisplayText = x.Name
};
return(results.OrderBy(x => x.DisplayText).ToList());
//return context.Artists.ToList() // in 1517
}
}
public List <SelectionList> Artists_List()
{
//due to entities will be internal
//you will not be able to use the entity definitions
//as return datatypes
//instead, we will create views within the ViewModel class that will
// contain the data definitions for your return datatypes
// to fill these view model classes, we will be using linq queries
//linq queries return their data as IEnumerable or IQueryable datasets
//you can use var when declairing your query receiving variables
//the receiving variables can then be converted to a List<T>
//this Linq query below uses the query syntax method
using (var context = new ChinookSystemContext())
{
//Linq to Entity queries
//where is the access to the application library system entities
//they are in your context class ChinookSystemContext - > context
//the entities are access by your DbSet<T> ->Artist
//x represents any occurance (instance) in DbSet<T>
var results = from x in context.Artists
select new SelectionList
{
ValueId = x.ArtistId,
DisplayText = x.Name
};
return(results.OrderBy(x => x.DisplayText).ToList());
//return context.Artists.ToList(); //in CPSC 1517 ENTITIES were public
}
}
public Track Track_Find(int trackid)
{
using (var context = new ChinookSystemContext())
{
return(context.Tracks.Find(trackid));
}
}
public List <CustomersOfCountryEmail> Customer_GetCustomersForCountryAndEmail(string country, string email)
{
using (var context = new ChinookSystemContext())
{
//when you are working in your sandbox of Linqpad, you are using Linq to SQL
//when you move your query to your controller class, you are using Linq to Entity
//you therefore have to request the DbSet in your context class
IEnumerable <CustomersOfCountryEmail> results = context.Customers
.Where(x => (x.Country.Contains(country) && x.Email.Contains(email)))
.OrderBy(x => x.LastName)
.ThenBy(x => x.FirstName)
.Select(
x =>
new CustomersOfCountryEmail
{
Name = ((x.LastName + ", ") + x.FirstName),
Email = x.Email,
City = x.City,
State = x.State,
Country = x.Country
}
);
return(results.ToList());
}
}
public List <UserPlayListTrack> ListExistingPlayList(string existingPlayListID)
{
using (var context = new ChinookSystemContext())
{
IEnumerable <UserPlayListTrack> results = null;
if (existingPlayListID != "")
{
int narg = int.Parse(existingPlayListID);
results = from x in context.PlaylistTracks
where x.PlaylistId == narg
orderby x.TrackNumber
select new UserPlayListTrack
{
TrackID = x.Track.TrackId,
TrackNumber = x.TrackNumber,
TrackName = x.Track.Name,
Milliseconds = x.Track.Milliseconds,
UnitPrice = x.Track.UnitPrice
};
return(results.ToList());
}
else
{
return(null);
}
}
}
}//eom
private List <TrackList> List_GenreTracksPlaylistSelection(string tracksby, string arg)
{
int genreid;
if (!Int32.TryParse(arg, out genreid))
{
throw new Exception("big oops on the genre thing");
}
using (var context = new ChinookSystemContext())
{
List <TrackList> results = null;
results = (from x in context.Tracks
where x.GenreId == genreid
select new TrackList
{
TrackID = x.TrackId,
Name = x.Name,
Title = x.Album.Title,
ArtistName = x.Album.Artist.Name,
GenreName = x.Genre.Name,
Composer = x.Composer,
Milliseconds = x.Milliseconds,
Bytes = x.Bytes,
UnitPrice = x.UnitPrice
}).ToList();
return(results);
}
}
public int Album_Add(AlbumItem item)
{
using (var context = new ChinookSystemContext())
{
//due to the fact we have seperaated handling of our entities
//from the data traansfer beteen webapp and class library
//using viewmodel classes, we must create an instaance of the entity
//and move the data from the viewmodel class to the entity instance
Album addItem = new Album
{
//no pkey is set because it is an identity pkey so no value is needed
Title = item.Title,
ArtistId = item.ArtistId,
ReleaseLabel = item.ReleaseLabel,
ReleaseYear = item.ReleaseYear
};
//Staging
//Setup in local memory
//At this point you will not have sent anything to the database
//therefore you will NOT have your new pkey as yet
context.Albums.Add(addItem);
//commit to database
//on this command you
// a)executee entity validtion annotation
// b)send local memory staging to the database for execution
//after successful execution your entity instance will have the new pkey(identity) value
context.SaveChanges();
//at this point entity instance is has the neww primay key value
return(addItem.AlbumId);
}
}
public Artist Artist_Get(int artistid)
{
using (var context = new ChinookSystemContext())
{
return(context.Artists.Find(artistid));
}
}
public int Album_Add(AlbumItem item)
{
using (var context = new ChinookSystemContext())
{
//Due to the fact that we have seperated the handling of our entities, from the data transfer between WebApp and Class library
// using the ViewModel classes, we MUST create an instance of the entity and move the data from the ViewModel class
// to the entity instance.
Album addItem = new Album
{
//Why no pkey set?
//pkey is an identity pkey, no value is needed.
Title = item.Title,
ArtistId = item.ArtistId,
ReleaseYear = item.ReleaseYear,
ReleaseLabel = item.ReleaseLabel
};
//Staging
//Setup in Local Memory
//At this point you will NOT have sent anything to the database.
// ****** therefore, you will NOT have your new pkey as yet. ******
context.Albums.Add(addItem);
//Commit to database
//On this command, you
// a) Execute entity validation annotation
// b) Send your local memory staging to the database for execution
//After a successful execution, your entity instance will have the new pkey (Identity) value.
context.SaveChanges();
//at this point, your entity instance has the new pkey value
return(addItem.AlbumId);
}
}
public int Album_Add(AlbumItem item)
{
using (var context = new ChinookSystemContext())
{
//"item" is viewmodel object => to Entity object before update!
Album additem = new Album
{
//load fast - no PK because KEY is DB given
Title = item.Title,
ArtistId = item.ArtistId,
ReleaseYear = item.ReleaseYear,
ReleaseLabel = item.ReleaseLabel
};
//staging in local mem
//at this point code will NOT have send to Database.
//Therefore will not have the new PKey yet
context.Albums.Add(additem);
//commit to Database - on this command the item is shipped to entity deffinition validation then to context DB
context.SaveChanges();
//entity instance will have new Pkey attached to the object
return(additem.AlbumId);
}
}
public void Album_Update(AlbumItem item)
{
using (var context = new ChinookSystemContext())
{
//"item" is viewmodel object => to Entity object before update!
Album updateitem = new Album
{
AlbumId = item.AlbumId,
Title = item.Title,
ArtistId = item.ArtistId,
ReleaseYear = item.ReleaseYear,
ReleaseLabel = item.ReleaseLabel
};
//staging in local mem
//at this point code will NOT have send to Database.
context.Entry(updateitem).State = System.Data.Entity.EntityState.Modified;
//commit to Database - on this command the item is shipped to entity deffinition validation then to context DB
context.SaveChanges();
//entity instance will have new Pkey attached to the object
}
}
public Album Album_Get(int albumid)
{
using (var context = new ChinookSystemContext())
{
return(context.Albums.Find(albumid));
}
}
public List <Album> Album_List()
{
using (var context = new ChinookSystemContext())
{
return(context.Albums.ToList());
}
}
public int Albums_Add(AlbumItem item)
{
using (var context = new ChinookSystemContext())
{
//need to move the data from the viewmodel class into
// an entity instance BEFORE staging
//the pkey of the Albums table is an Identity() pKey
// therefore you do NOT need to supply the AlbumId value
Album entityItem = new Album
{
Title = item.Title,
ArtistId = item.ArtistId,
ReleaseYear = item.ReleaseYear,
ReleaseLabel = item.ReleaseLabel
};
//stagging is to local memory
context.Albums.Add(entityItem);
//At this point, the new pkey value is NOT available
//the new pkey value is created by the database
//commit is the action of sending your request to
// the database for action.
//Also, any validation annotation in your entity definition class
// is execute during this command
context.SaveChanges();
//since I have an int as the return datatype
// I will return the new identity value
return(entityItem.AlbumId);
}
}
}//eom
public void DeleteTracks(string username, string playlistname, List <int> trackstodelete)
{
using (var context = new ChinookSystemContext())
{
//code to go here
}
}//eom
public void Album_Update(AlbumItem item)
{
using (var context = new ChinookSystemContext())
{
//Due to the fact that we have seperated the handling of our entities, from the data transfer between WebApp and Class library
// using the ViewModel classes, we MUST create an instance of the entity and move the data from the ViewModel class
// to the entity instance.
Album updateItem = new Album
{
//for an update, you need to supply your pkey value
AlbumId = item.AlbumId,
Title = item.Title,
ArtistId = item.ArtistId,
ReleaseYear = item.ReleaseYear,
ReleaseLabel = item.ReleaseLabel
};
//Staging
//Setup in Local Memory
context.Entry(updateItem).State = System.Data.Entity.EntityState.Modified;
//Commit to database
//On this command, you
// a) Execute entity validation annotation
// b) Send your local memory staging to the database for execution
context.SaveChanges();
}
}
public void Albums_Delete(AlbumViewModel item)
{
using (var context = new ChinookSystemContext())
{
Albums_Delete(item.AlbumId);
}
}
public CustomerItem Customer_FindByID(int customerid)
{
using (var context = new ChinookSystemContext())
{
var results = context.Customers
.Where(x => x.CustomerId == customerid)
.Select(x => x)
.FirstOrDefault();
CustomerItem item = new CustomerItem
{
CustomerId = results.CustomerId,
LastName = results.LastName,
FirstName = results.FirstName,
Company = results.Company,
Address = results.Address,
City = results.City,
State = results.State,
Country = results.Country,
PostalCode = results.PostalCode,
Phone = results.Phone,
Fax = results.Fax,
Email = results.Email,
SupportRepId = results.SupportRepId
};
return(item);
}
}
public void Album_Update(AlbumItem item)
{
using (var context = new ChinookSystemContext())
{
//due to the fact we have seperaated handling of our entities
//from the data traansfer beteen webapp and class library
//using viewmodel classes, we must create an instaance of the entity
//and move the data from the viewmodel class to the entity instance
Album updateItem = new Album
{
//Update needs a pkey value
AlbumId = item.AlbumId,
Title = item.Title,
ArtistId = item.ArtistId,
ReleaseLabel = item.ReleaseLabel,
ReleaseYear = item.ReleaseYear
};
//Staging
//Setup in local memory
context.Entry(updateItem).State = System.Data.Entity.EntityState.Modified;
//commit to database
//on this command you
// a)executee entity validtion annotation
// b)send local memory staging to the database for execution
//after successful execution your entity instance will have the new pkey(identity) value
context.SaveChanges();
}
}
public int Album_Add(AlbumItem item)
{
using (var context = new ChinookSystemContext())
{
//due to the fact that we have separated the handling of our entities from the data transfer between web app and class library using the ViewModel classes, we must create an instance of the entity and move the data from the ViewModel class to the entity instance
Album addItem = new Album
{
//why no PK set? PK is an identity, no value needed
Title = item.Title,
ArtistId = item.ArtistId,
ReleaseYear = item.ReleaseYear,
ReleaseLabel = item.ReleaseLabel
};
//staging
//setup of local memory
//at this point you will not have sent anything to the db, therefore you will not have your new PK as yet
context.Albums.Add(addItem);
//commit to db
//on this command you a) execute entity validation annotation b) send your local memory staging to the database for execution
//after a successful execution your entity instance will have the new PK (Identity) value
context.SaveChanges();
//at this point, your entity instance has the new PK value
return(addItem.AlbumId);
}
}
public List <EmployeeCustomerList> Employee_EmployeeCustomerList()
{
using (var context = new ChinookSystemContext())
{
IEnumerable <EmployeeCustomerList> resultsm = context.Employees
.Where(x => x.Title.Contains("Sales Support"))
.OrderBy(x => x.LastName)
.ThenBy(x => x.FirstName)
.Select(
x =>
new EmployeeCustomerList
{
EmployeeName = ((x.LastName + ", ") + x.FirstName),
Title = x.Title,
CustomerSupportCount = x.Customers.Count(),
CustomerList = x.Customers
.Select(
y =>
new CustomerSupportItem
{
CustomerName = ((y.LastName + ", ") + y.FirstName),
Phone = y.Phone,
City = y.City,
State = y.State
}
).ToList()
}
);
return(resultsm.ToList());
}
}
public AlbumItem Albums_FindById(int albumid)
{
using (var context = new ChinookSystemContext())
{
// (...).FirstOrDefault will return either
// a) the first record matching the where condition
// b) a null value
// in 1517 when enrity were pubic, we could use
// the entityframework method extension .Find(xxx)
// to retreive the database record on the primary key
// return context.DbSetName.Find(xxx)
AlbumItem results = (from x in context.Albums
where x.AlbumId == albumid
select new AlbumItem
{
AlbumId = x.AlbumId,
Title = x.Title,
ReleaseYear = x.ReleaseYear,
ArtistId = x.ArtistId,
ReleaseLabel = x.ReleaseLabel
}).FirstOrDefault();
return(results);
}
}
public void SavePlayList(int argplaylistid, List <UserPlayListTrack> argplaylist)
{
PlaylistTrack argPlaylistTrack = null;
using (var context = new ChinookSystemContext())
{
//Delete from PlaylistTracks table all items related to argplaylistid
//presently in the db.
List <PlaylistTrack> playlistOld = (from x in context.PlaylistTracks
where x.PlaylistId == argplaylistid
select x).ToList();
foreach (PlaylistTrack item in playlistOld)
{
context.PlaylistTracks.Remove(item);
}
//Add to PlaylistTracks table the items from argplaylist collection
//argument and relate them to playlistid.
foreach (UserPlayListTrack item in argplaylist)
{
//Instantiate a new entity type.
argPlaylistTrack = new PlaylistTrack();
argPlaylistTrack.PlaylistId = argplaylistid;
argPlaylistTrack.TrackId = item.TrackID;
argPlaylistTrack.TrackNumber = item.TrackNumber;
context.PlaylistTracks.Add(argPlaylistTrack);
}
context.SaveChanges();
}
}
public void Album_Update(AlbumItem item)
{
using (var context = new ChinookSystemContext())
{
//due to the fact that we have separated the handling of our entities
// from the data transfer between web app and class library
//using the viewmodel classes, we MUST create an instance
//of the entity and move the data from the view model class to the entity instance.
Album updateitem = new Album
{
//for an update, you need to supply a pkey value
AlbumId = item.AlbumId,
Title = item.Title,
ArtistId = item.ArtistId,
ReleaseYear = item.ReleaseYear,
ReleaseLabel = item.ReleaseLabel
};
//staging
//setup in local memory
context.Entry(updateitem).State = System.Data.Entity.EntityState.Modified;
//commit to database
//on this command you
//A) execute entity validationm annotation
// B) send your local memory staging to the database for execution
// after a successful execution your entity instance will have the
// new pkey (identity) value
context.SaveChanges();
//at this point, your identity instance has the new pkey value
}
}
public int AddNewPLaylist(string playlistname, string username)
{
using (var context = new ChinookSystemContext())
{
Playlist exists = (from x in context.Playlists
where x.Name.Equals(playlistname) &&
x.UserName.Equals(username)
select x).FirstOrDefault();
if (exists == null)
{
exists = new Playlist()
{
//pkey is an identity int key
Name = playlistname,
UserName = username
};
//A new primary key will be put into exits.PlaylistId
//after the new Playlist is added to the Playists collection.
context.Playlists.Add(exists);
context.SaveChanges();
return(exists.PlaylistId);
}
else
{
throw new Exception("ERROR: PlayList Already Exists for this User");
}
}
}
public List <EmployeeCustomerList> Employee_EmployeeCustomerList()
{
using (var context = new ChinookSystemContext())
{
IEnumerable <EmployeeCustomerList> resultsq =
//select the employee -
from emp in context.Employees
where emp.Title.Contains("Sales Support")
orderby emp.LastName, emp.FirstName
select new EmployeeCustomerList
{
Employee = emp.LastName + "," + emp.FirstName,
Title = emp.Title,
CustomerSupportCount = emp.Customers.Count(),
//select the customer -
CustomerSupportItems = (from cus in emp.Customers
select new CustomerSupportItem
{
CustomerName = cus.LastName + "," + cus.FirstName,
Phone = cus.Phone,
City = cus.City,
State = cus.State
}).ToList()
};
return(resultsq.ToList());
}
}
public List <Track> Track_List()
{
using (var context = new ChinookSystemContext())
{
return(context.Tracks.ToList());
}
}
}//eom
public void MoveTrack(string username, string playlistname, int trackid, int tracknumber, string direction)
{
using (var context = new ChinookSystemContext())
{
//code to go here
}
}//eom
public void Tracks_Delete(TrackViewModel item)
{
using (var context = new ChinookSystemContext())
{
Tracks_Delete(item.TrackId);
}
}
public AlbumItem Albums_FindById(int albumid) //this is just a single record thats why List<> and IEnumerable<> is not used
{
using (var context = new ChinookSystemContext())
{
//----Feb 10, 2020 lesson----
//in CPSC1517, when entities were public, we could use
// the entityFramework method extension .Find(xxx)
// to retrieve the database record on the primary key
// return context.DbSetname.Find(xxx);
//---------------------------
// (...).FirstOrDefault will return either
// a) the first record matching the where condition
// b) a null value
AlbumItem results = (from x in context.Albums
where x.AlbumId == albumid
select new AlbumItem
{
AlbumId = x.AlbumId,
Title = x.Title,
ReleaseYear = x.ReleaseYear,
ArtistId = x.ArtistId,
ReleaseLabel = x.ReleaseLabel
}).FirstOrDefault();
return(results);
}
}
