static void Main(string[] args)
{
#region Linq Syntax queries
var context = new QueriesContext();
var query = from c in context.Courses
where c.DatePublished < DateTime.Today
orderby c.Title
select c;
foreach (var result in query)
{
Console.WriteLine(result.Title);
}
var projectionQuery = from c in context.Courses
where c.DatePublished < DateTime.Today
select new { NewName = c.Title, DateOfQuery = c.DatePublished };
// An example of an inner join
// Below we use the navigational property to retrieve our data.
var InnerJoinQuery =
from c in context.Courses
select new { c.Author.Name };
// Adding the group count method
var groupJoin =
from a in context.Authors
join c in context.Courses on a.Id equals c.AuthorId into g
select new { Name = a.Name, count = g.Count() };
// into 'g' then makes this join a group join
#endregion
#region Linq extension methods
// Restriction query
var course = context.Courses.Where(c => c.Id == 1);
// Ordering - note the use of 'thenby'
var courseOrdered = context.Courses
.Where(c => c.Id == 1)
.OrderBy(c => c.Title)
.ThenBy(c => c.Description);
// Projection
var courseProjection = context.Courses
.Where(c => c.Id == 1)
.Select(c => new { Name = c.Author.Name, Title = c.Title });
// Grouping
var courseGroup = context.Courses
.GroupBy(c => c.Level);
// GroupBy the course level
foreach (var group in courseGroup)
{
// Each group is produced a key
Console.WriteLine($"key - {group.Key}");
// Iterate over each item within the group
foreach (var item in group)
{
Console.WriteLine($"course - {item.Title}");
}
}
// Joining
// Inner Join (Course & Authors)
//context.Courses.Join(context.Authors,
// c => c.AuthorId,
// a => a.Id,
// (course, author) => new
// { CourseName = course.Title,
// AuthorName = author.Name
// }
// );
#endregion
#region Lazy Loading
var LazyResults = context.Courses;
// This is lazy loading and also an example of the N+1 problem within Lazy Loading in EF.
foreach (var lazyCourse in LazyResults)
{
Console.WriteLine($"Course name : {lazyCourse.Title} - Course Author :{lazyCourse.Author.Name}");
}
#endregion
#region Eager Loading
var eagerLoading = context.Courses.Include("Author").ToList();
foreach (var lazyCourse in LazyResults)
{
Console.WriteLine($"Course name : {lazyCourse.Title} - Course Author :{lazyCourse.Author.Name}");
}
// Adding Lambda definition instead (c => c)
var eagerLoadingWithLambda = context.Courses
.Include(c => c.Author)
.ToList();
#endregion
#region Explicit loading
var ExplicitLoading = context.Authors.Single(a => a.Id == 1);
// The MSDN approach of Explicit loading (Entry, Collection, Load)
context.Entry(ExplicitLoading).Collection(a => a.Courses).Load();
#endregion
#region Improved Searching Query
var searchAuthors = context.Authors.ToList();
var searchAuthorIds = searchAuthors.Select(a => a.Id);
// We are adding a list within the where clause
// Basically does this current course contain an id within this list.
var searchCourses = context.Courses.Where(c => searchAuthorIds.Contains(c.AuthorId));
#endregion
Console.ReadLine();
}
static void Main(string[] args)
{
var context = new QueriesContext();
var actionMovies = context.Videos.Where(m => m.Genre.Name == "Action").OrderBy(m => m.Name);
// foreach (var movie in actionMovies)
// Console.WriteLine(movie.Name);
var dramaMovies = context.Videos
.Where(m => m.Genre.Name == "Drama" && m.Classification == Classification.Gold)
.OrderByDescending(m => m.ReleaseDate)
.Where(m => m.Classification == Classification.Gold);
// foreach(var movie in dramaMovies)
// Console.WriteLine(movie.Name);
var allMovies = context.Videos
.Select(m => new { MovieName = m.Name, Genre = m.Name });
// foreach (var x in allMovies)
// Console.WriteLine("\t {0} ", x.MovieName);
var allMoviesGroupedByClassification = context.Videos
.GroupBy(m => m.Classification);
// foreach (var groups in allMoviesGroupedByClassification)
// {
// Console.WriteLine("\t {0}", groups.Key);
//
// foreach (var movie in groups)
// {
// Console.WriteLine("\t {0}", movie.Name);
// }
// }
var groups = context.Videos
.GroupBy(v => v.Classification)
.Select(g => new
{
Classification = g.Key.ToString(),
Videos = g.OrderBy(v => v.Name)
});
// foreach (var g in groups)
// {
// Console.WriteLine("Classification: " + g.Classification);
//
// foreach (var v in g.Videos)
// Console.WriteLine("\t" + v.Name);
// }
var classifications = context.Videos
.GroupBy(v => v.Classification)
.Select(g => new
{
Name = g.Key.ToString(),
VideosCount = g.Count()
})
.OrderBy(c => c.Name);
// foreach (var c in classifications)
// Console.WriteLine("{0} ({1})", c.Name, c.VideosCount);
var genres = context.Genres
.GroupJoin(context.Videos, g => g.Id, v => v.GenreId, (genre, videos) => new
{
Name = genre.Name,
VideosCount = videos.Count()
})
.OrderByDescending(g => g.VideosCount);
// foreach (var g in genres)
// Console.WriteLine("{0} ({1})", g.Name, g.VideosCount);
}