public void IncreaseTitleOfMoviesWhichWasRentedMoreThanTwiceBy1()
{
using (var db = new CodeFirstContext())
{
var movies = (from copy in db.Copies
join rental in db.Rentals on copy.CopyId equals rental.CopyId
join movie in db.Movies on copy.MovieId equals movie.MovieId
group copy by movie.MovieId into g
select new
{
MovieId = g.Key,
Count = g.Count()
});
foreach (var movie in db.Movies)
{
if (movies.Any(m => m.MovieId == movie.MovieId))
{
movie.Price = movie.Price + 1f;
}
}
db.SaveChanges();
foreach (var row in db.Movies)
{
Console.WriteLine(row.ToString());
}
}
}
public void SelectCountOfMoviesProducedIn1984()
{
using (CodeFirstContext db = new CodeFirstContext())
{
var result = db.Movies.Where(m => m.Year == 1984);
Console.WriteLine($"{result}");
Console.WriteLine(result.ToQueryString());
}
}
public void SelectMostExspensiveMovie()
{
using (CodeFirstContext db = new CodeFirstContext())
{
var result = db.Movies.Select(p => p.Price);
var max = result.Max().ToString();
Console.WriteLine($"{max}");
Console.WriteLine(result.ToQueryString());
}
}
public void SelectClientsFirstnameAndFirstnameFirstCharAndFirstnameLastChar()
{
using (CodeFirstContext db = new CodeFirstContext())
{
var result = db.Clients.Select(p => new { p.Firstname, second = p.Firstname.ToArray()[0], third = p.Firstname.ToArray()[p.Firstname.Length - 1] });
foreach (var client in result)
{
Console.WriteLine($"{client.Firstname},{client.second},{client.third}");
}
Console.WriteLine(result.ToQueryString());
}
}
public void SelectCountOfRentalsWhereMovieEqualsTaxiDriver()
{
using (CodeFirstContext db = new CodeFirstContext())
{
var result = (from starring in db.Starring
join movie in db.Movies on starring.MovieId equals movie.MovieId
where movie.Title == "Taxi Driver"
select movie.Title);
Console.WriteLine($"{result.Count()}");
Console.WriteLine(result.ToQueryString());
}
}
public void SelectMoviesWithYear1998Or1999()
{
using (CodeFirstContext db = new CodeFirstContext())
{
var result = db.Movies.Where(p => p.Year == 1998 || p.Year == 1999);
foreach (var movie in result)
{
Console.WriteLine($"{movie.Title}");
}
Console.WriteLine(result.ToQueryString());
}
}
public void SelectActorFirstnameConcatDotConcatActorsLastname()
{
using (CodeFirstContext db = new CodeFirstContext())
{
var result = db.Actors.Select(p => p.Firstname.ToArray()[0] + "." + p.Lastname);
foreach (var actor in result)
{
Console.WriteLine(actor);
}
Console.WriteLine(result.ToQueryString());
}
}
public void SelectDistinctOrderedByMovieIdMoviesWhichCopiesAreAvailable()
{
using (CodeFirstContext db = new CodeFirstContext())
{
var result = db.Movies.Where(m => m.Copies.Any(c => c.Available == true)).Distinct().OrderBy(m => m.MovieId);
foreach (var movie in result)
{
Console.WriteLine($"{movie.MovieId}");
}
Console.WriteLine(result.ToQueryString());
}
}
public void SelectCopiesCopyIdWhereDataBetween2005_07_15And_2005_07_22()
{
using (CodeFirstContext db = new CodeFirstContext())
{
var result = db.Rentals.Where(r => r.DateOfRental > new DateTime(2005, 07, 15) && r.DateOfRental < new DateTime(2005, 07, 22)).Distinct().OrderBy(r => r.CopyId);
foreach (var copie in result)
{
Console.WriteLine($"{copie.CopyId}");
}
Console.WriteLine(result.ToQueryString());
}
}
public void SelectClientsWhereLastnameLengthGreaterFirstnameLengthMoreBy3()
{
using (CodeFirstContext db = new CodeFirstContext())
{
var result = db.Clients.Where(c => c.Firstname.Length + 3 < c.Lastname.Length);
foreach (var client in result)
{
Console.WriteLine($"{client.Firstname},{client.Lastname}");
}
Console.WriteLine(result.ToQueryString());
}
}
public void SelectOrderedByDescendingActorsLastnameWithFirstnameEqualsArnoldOrJodieOrTom()
{
using (CodeFirstContext db = new CodeFirstContext())
{
var result = db.Actors.Where(act => act.Firstname == "Arnold" || act.Firstname == "Tom" || act.Firstname == "Jodie").OrderByDescending(act => act.Firstname);
foreach (var actor in result)
{
Console.WriteLine($"{actor.Lastname}");
}
Console.WriteLine(result.ToQueryString());
}
}
public void SelectMoviesWithPriceGreater9()
{
using (CodeFirstContext db = new CodeFirstContext())
{
var result = db.Movies.Where(p => p.Price > 9);
foreach (var movie in result)
{
Console.WriteLine($"{movie.Title},{movie.Price}");
}
Console.WriteLine(result.ToQueryString());
}
}
public void SelectClientsWithFirstnameEqualsLisa()
{
using (CodeFirstContext db = new CodeFirstContext())
{
var result = db.Clients.Where(c => c.Firstname == "Lisa");
foreach (var client in result)
{
Console.WriteLine($"{client.Lastname}");
}
Console.WriteLine(result.ToQueryString());
}
}
public void SelectEmailConstructedFromClientsFirstnameAndLastname()
{
using (CodeFirstContext db = new CodeFirstContext())
{
var result = db.Clients.Select(c => c.Firstname.ToLower() + "." + c.Lastname.ToLower() + "@wsb.pl");
foreach (var email in result)
{
Console.WriteLine(email);
}
Console.WriteLine(result.ToQueryString());
}
}
public void SelectMoviesTitleWhereSecondFromLastCharEqualsO()
{
using (CodeFirstContext db = new CodeFirstContext())
{
var result = db.Movies.Where(m => m.Title.Substring(m.Title.Length - 2, m.Title.Length - 2) == "o").Select(m => m.Title);
foreach (var title in result)
{
Console.WriteLine(title);
}
Console.WriteLine(result.ToQueryString());
}
}
public void SelectClientsFirstnameWhereFirstCharEqualsLastChar()
{
using (CodeFirstContext db = new CodeFirstContext())
{
var result = db.Clients.Where(p => p.Firstname.ToLower().Substring(p.Firstname.Length - 1, p.Firstname.Length - 1) == p.Firstname.Substring(p.Firstname.Length - 1, p.Firstname.Length - 1)).Select(p => p.Firstname);
foreach (var name in result)
{
Console.WriteLine(name);
}
Console.WriteLine(result.ToQueryString());
}
}
public void DisplayAveragePriceForEveryYearOfProduction()
{
using (CodeFirstContext db = new CodeFirstContext())
{
var result = db.Movies.GroupBy(m => m.Year).Select(m => new { Year = m.Key, Count = m.Count() });
foreach (var row in result)
{
Console.WriteLine($"{row.Year},{row.Count}");
}
Console.WriteLine(result.ToQueryString());
}
}
public void SelectMoviesTitleOrderedByTitleLength()
{
using (CodeFirstContext db = new CodeFirstContext())
{
var result = db.Movies.OrderBy(p => p.Title.Length).Select(p => p.Title);
foreach (var movie in result)
{
Console.WriteLine(movie);
}
Console.WriteLine(result.ToQueryString());
}
}
public void SelectCopiesRentedMoreThenOneDay()
{
using (CodeFirstContext db = new CodeFirstContext())
{
var result = db.Rentals.Where(r => (((DateTime)r.DateOfReturn.Value).Day - ((DateTime)r.DateOfRental.Value).Day) > 1);
foreach (var rental in result)
{
Console.WriteLine($"{rental.CopyId},{(((DateTime)rental.DateOfReturn.Value).Day - ((DateTime)rental.DateOfRental.Value).Day)}");
}
Console.WriteLine(result.ToQueryString());
}
}
public void SelectNamesOfAllClientsAndActors()
{
using (CodeFirstContext db = new CodeFirstContext())
{
var result = db.Actors.Select(act => new { act.Firstname, act.Lastname }).Union(db.Clients.Select(c => new { c.Firstname, c.Lastname }));
foreach (var row in result)
{
Console.WriteLine($"{row.Firstname},{row.Lastname}");
}
Console.WriteLine(result.ToQueryString());
}
}
public void SelectNewestMovies()
{
using (CodeFirstContext db = new CodeFirstContext())
{
var result = db.Movies.OrderByDescending(p => p.Year).Select(p => new { p.Title, p.Price }).Take(3);
foreach (var movie in result)
{
Console.WriteLine($"{movie.Title},{movie.Price}");
}
Console.WriteLine(result.ToQueryString());
}
}
public void DeleteAllMoviesInWhichNoActorsPlayed()
{
using (var db = new CodeFirstContext())
{
var moviestoDelete = db.Movies.Where(k => db.Starring.Any(m => m.MovieId != k.MovieId));
db.SaveChanges();
foreach (var row in db.Movies)
{
Console.WriteLine(row.ToString());
}
}
}
public void SetNewPriceForTaxiDriver()
{
using (var db = new CodeFirstContext())
{
var movie = db.Movies.Where(m => m.Title == "Taxi Driver").FirstOrDefault();
movie.Price = 5;
db.SaveChanges();
foreach (var row in db.Movies)
{
Console.WriteLine(row.ToString());
}
}
}
public void RemoveMoviesProducedin2005()
{
using (var db = new CodeFirstContext())
{
var moviesToRemove = db.Movies.Where(m => m.Year == 2005);
db.Movies.RemoveRange(moviesToRemove);
db.SaveChanges();
foreach (var row in db.Movies)
{
Console.WriteLine(row.ToString());
}
}
}
public void InsertIntoMoviesTheIncredibels()
{
using (var db = new CodeFirstContext())
{
db.Movies.Add(new Movie {
MovieId = 12, Title = "The Incredibles", Year = 2004, AgeRestriction = 0, Price = 9.5f
});
db.SaveChanges();
foreach (var row in db.Movies)
{
Console.WriteLine(row.ToString());
}
}
}
public void InsertNewCopyOfPlatoon()
{
using (var db = new CodeFirstContext())
{
db.Copies.Add(new Copy {
CopyId = db.Copies.Select(c => c.CopyId).Max() + 1, Available = true, MovieId = db.Movies.Where(m => m.Title == "Platoon").Select(m => m.MovieId).FirstOrDefault()
});
db.SaveChanges();
foreach (var row in db.Movies)
{
Console.WriteLine(row.ToString());
}
}
}
public void InsertIntoMoviesMadagascar()
{
using (var db = new CodeFirstContext())
{
db.Movies.Add(new Movie {
MovieId = 11, Title = "Madagascar", Year = 2005, AgeRestriction = 0, Price = 10.5f
});
db.SaveChanges();
foreach (var row in db.Movies)
{
Console.WriteLine(row.ToString());
}
}
}
public void SelectNamesOfClientsAndCountOfTheirRentals()
{
using (CodeFirstContext db = new CodeFirstContext())
{
var result = (from rental in db.Rentals
join client in db.Clients on rental.ClientId equals client.ClientId
select new { Client = client, rental.CopyId }).Select(g => new { Name = g.Client.Firstname.Concat(" ").Concat(g.Client.Lastname), CopyId = g.CopyId }).GroupBy(g => g.Name).Select(r => new { Name = r.Key, Count = r.Count() });
foreach (var row in result)
{
Console.WriteLine($"{row.Name},{row.Count}");
}
Console.WriteLine(result.ToQueryString());
}
}
public void IncreasePriceOfAllMoviesProducedBefore1980By05()
{
using (var db = new CodeFirstContext())
{
foreach (var movie in db.Movies.Where(m => m.Year < 1980))
{
movie.Price = movie.Price + 0.5f;
}
db.SaveChanges();
foreach (var row in db.Movies)
{
Console.WriteLine(row.ToString());
}
}
}
public void SelectActorsLastnameAndCountOfMoviesWhereTheyStarredSkipActorsWhichWasStarredInOneMovie()
{
using (CodeFirstContext db = new CodeFirstContext())
{
var result = (from starring in db.Starring
join movie in db.Movies on starring.MovieId equals movie.MovieId
join actor in db.Actors on starring.ActorId equals actor.ActorId
select new { Lastname = actor.Lastname, Count = starring.MovieId }).GroupBy(m => m.Lastname).Select(r => new { Lastname = r.Key, Count = r.Count() }).Where(g => g.Count > 1).Select(r => new { Lastname = r.Lastname, Count = r.Count });
foreach (var row in result)
{
Console.WriteLine($"{row.Lastname},{row.Count}");
}
Console.WriteLine(result.ToQueryString());
}
}
