static void Main(string[] args)
{
PlutoContext ctx = new PlutoContext();
//FirstExampleLinqVsExtensionMethod(ctx);
//Restrictions(ctx);
//Ordering(ctx);
//Grouping(ctx);
//Joining(ctx);
//Projection(ctx);
//Partitioning(ctx);
//Operators(ctx);
//Quantifying(ctx);
//Aggregating(ctx);
//IQueryableVsIEnumerable(ctx);
//LazyLoading(ctx);
//EagerLoading(ctx);
ExplicitLoading(ctx);
}
static void EagerLoading(PlutoContext ctx)
{
// the oposite of Lazy Loading
// instead of loading related entities on demand, we gonna loading them
// to prevent additional queries to the DB
// solving the 'N+1' issue
var courses = ctx.Courses.Include(c => c.Author).ToList();
foreach (var course in courses)
{
Console.WriteLine($"[{course.Title}] by [{course.Author.Name}]");
}
// for single properties
ctx.Courses.Include(c => c.Author); //ctx.Courses.Include(c => c.Author.Address);
// for collection properties
ctx.Courses.Include(c => c.Tags.Select(t => t.Courses));
// if using a lot of eager loading we will get complex sql query with many joins
// and so we will store in a memory a lot of data when we not neccessary need it
// so be careful with this:
//ctx.Courses
// .Include(c => c.Author)
// .Include(c => c.Tags.Select(t => t.Courses))
// .Include(c => c.AuthorId)
// .Include(c => c.Level);
}
static void ExplicitLoading(PlutoContext ctx)
{
// we tell Entity Framework [exactly] what should be loaded
// no SQL joins
// separate queries, multiple round-trips to the DB
var author = ctx.Authors.Include(a => a.Courses).Single(a => a.Id == 1);
#region Explicit Loading
// MSDN way (works for single entries only)
//ctx.Entry(author).Collection(a => a.Courses).Load();
// Better way (second query that gets all courses for that author):
//ctx.Courses.Where(c => c.AuthorId == author.Id).Load();
#endregion
#region Explicit Loading (with filters)
// also we can apply a filter on the explcit loading:
// MSDN way:
//ctx.Entry(author).Collection(a => a.Courses).Query().Where(c => c.FullPrice == 0).Load();
// prefered way:
//ctx.Courses.Where(c => c.AuthorId == author.Id && c.FullPrice == 0).Load();
#endregion
#region Example: load only free courses by certain authors
var authors = ctx.Authors.ToList();
IEnumerable <int> authorIds = authors.Select(a => a.Id);
ctx.Courses.Where(c => authorIds.Contains(c.AuthorId) && c.FullPrice == 0).Load();
#endregion
}
static void Projection(PlutoContext ctx)
{
// using LINQ Extension methods:
var courses = ctx.Courses
.Where(c => c.Level == CourseLevel.Beginner)
.OrderBy(c => c.Title)
.Select(c => new { CourseName = c.Title, AuthorName = c.Author.Name });
// list of lists
var courses1 = ctx.Courses
.Where(c => c.Level == CourseLevel.Beginner)
.OrderBy(c => c.Title)
.Select(c => c.Tags);
// list of tags
var tags = ctx.Courses
.Where(c => c.Level == CourseLevel.Beginner)
.OrderBy(c => c.Title)
.SelectMany(c => c.Tags).Distinct();
foreach (var tag in tags)
{
Console.WriteLine(tag.Name);
}
}
static void Grouping(PlutoContext ctx)
{
// linq syntax:
var query =
from c in ctx.Courses
group c by c.Level
into g
select g;
foreach (var group in query)
{
Console.WriteLine(group.Key);
//display courses and next to it's level - print the number of courses in each group
Console.WriteLine($"{group.Count()}");
foreach (var course in group)
{
//display courses of each group (grouped by course level)
//Console.WriteLine("\t{0}", course.Title);
}
}
//linq ext. methods:
var groups = ctx.Courses.GroupBy(c => c.Level);
foreach (var g in groups)
{
Console.WriteLine("Key: " + g.Key);
foreach (var course in g)
{
Console.WriteLine(course.Title);
}
}
}
static void Ordering(PlutoContext ctx)
{
// using LINQ Extension methods:
var courses = ctx.Courses
.Where(c => c.Level == CourseLevel.Beginner)
.OrderByDescending(c => c.Title)
.ThenByDescending(c => c.Level);
}
static void Quantifying(PlutoContext ctx)
{
#region Exists only in LINQ Extension Methods
// find if "all" items in a list satisfy a criteria:
bool allAbove10Dollars = ctx.Courses.All(c => c.FullPrice > 10);
// find if "any" item in a list satisfies a criteria:
bool anyCourseInBeginnerLevel = ctx.Courses.Any(c => c.Level == CourseLevel.Beginner);
#endregion
}
static void LazyLoading(PlutoContext ctx)
{
// if a navigational property defined as public virtual then the EF will use LAZY LOADING
// so it will postpone executing the query for bringing the relational data so instead
// going once to the DB and bringing all related data it will go to the DB on demand only
// when we access them
// which will cause additional roundtrips to the DB
// use Single, First, MAX to immediately execute a query
// Use Lazy Loading to load main objects and load the related objects on demand
var courses = ctx.Courses.Single(c => c.Level == CourseLevel.Intermediate);
}
static void Operators(PlutoContext ctx)
{
#region Exists only in LINQ Extension Methods
ctx.Courses.OrderBy(c => c.Level).FirstOrDefault(c => c.FullPrice > 100);
// the "Last" method is not for use with sql database:
ctx.Courses.LastOrDefault();
ctx.Courses.SingleOrDefault(c => c.Id == 1);
#endregion
}
static void Restrictions(PlutoContext ctx)
{
var query =
from c in ctx.Courses
where c.Level == CourseLevel.Beginner && c.AuthorId == 1
select new { Name = c.Title, Id = c.Id };
// LINQ Extension Method:
// get all courses in level 'Beginner':
var courses = ctx.Courses.Where(c => c.Level == CourseLevel.Beginner);
}
static void Aggregating(PlutoContext ctx)
{
#region Exists only in LINQ Extension Methods
var count = ctx.Courses.Where(c => c.Level == CourseLevel.Beginner).Count();
ctx.Courses.Max(c => c.FullPrice);
ctx.Courses.Min(c => c.FullPrice);
ctx.Courses.Average(c => c.FullPrice);
#endregion
}
static void IQueryableVsIEnumerable(PlutoContext ctx)
{
// IQueryable allows queries to be ---extended--- without being immediately executed
// We are building here an expression tree: we [compose] queries, extend them and delay execution
IQueryable <Course> courses = ctx.Courses
.Where(c => c.Level == CourseLevel.Beginner)
.OrderByDescending(c => c.Id)
.Select(c => c);
// When we use IEnumerable, we cannot extend (compose) a query so it executes at every step:
IEnumerable <Course> courses2 = ctx.Courses
.Where(c => c.Level == CourseLevel.Beginner)
.OrderByDescending(c => c.Id)
.Select(c => c);
}
static void Partitioning(PlutoContext ctx)
{
#region Exists only in LINQ Extension Methods
// display courses in pages, the size of each page - 3
var courses = ctx.Courses
.OrderBy(c => c.Id)
.Skip(3)
.Take(3);
foreach (var c in courses)
{
Console.WriteLine($"Course: {c.Title}");
}
#endregion
}
static void FirstExampleLinqVsExtensionMethod(PlutoContext ctx)
{
//LINQ Syntax:
var query =
from c in ctx.Courses
where c.Title.Contains("c#")
orderby c.Title
select c;
foreach (var c in query)
{
Console.WriteLine(c.Title);
}
//Extension methods:
var courses = ctx.Courses
.Where(c => c.Title.Contains("c#"))
.OrderBy(c => c.Title);
foreach (var course in courses)
{
Console.WriteLine(course.Title);
}
}
static void Joining(PlutoContext ctx)
{
#region Navigation Property
var query =
from c in ctx.Courses
select new { CourseName = c.Title, CourseAuthor = c.Author };
foreach (var c in query)
{
Console.WriteLine($"Course name: {c.CourseName.Substring(0, 9)} | course author: {c.CourseAuthor.Name}");
}
Console.WriteLine("-----------------------------------------------------------");
#endregion
#region INNER JOIN (LINQ Syntax)
var query1 =
from c in ctx.Courses
join a in ctx.Authors on c.AuthorId equals a.Id
select new { CourseName = c.Title, CourseAuthorName = a.Name };
foreach (var c in query1)
{
Console.WriteLine($"Course name: { c.CourseName.Substring(0, 9) } | course author: { c.CourseAuthorName }");
}
Console.WriteLine("-----------------------------------------------------------");
#endregion
#region GROUP JOIN (LINQ Syntax)
// group join (no equivalent in sql but useful when we do LEFT JOINs in sql)
var query2 =
from a in ctx.Authors
join c in ctx.Courses on a.Id equals c.AuthorId into g
select new { AuthorName = a.Name, Courses = g.Count() };
foreach (var x in query2)
{
Console.WriteLine($"{x.AuthorName} ({x.Courses})");
}
Console.WriteLine("-----------------------------------------------------------");
#endregion
#region CROSS JOIN (LINQ Syntax)
// cross join
var query3 =
from a in ctx.Authors
from c in ctx.Courses
select new { AuthorName = a.Name, CourseName = c.Title };
foreach (var i in query3)
{
Console.WriteLine($"{i.AuthorName} - {i.CourseName}");
}
#endregion
#region INNER JOIN (LINQ Extension Method)
var joinResult = ctx.Courses.Join(ctx.Authors,
c => c.AuthorId,
a => a.Id,
(Course, Author) => new
{
CourseName = Course.Title,
AuthorName = Author.Name
});
#endregion
#region GROUP JOIN (LINQ Extension Method)
// useful when using for LEFT JOINS with AGGREGATE function
var groupJoinResult = ctx.Authors
.GroupJoin(ctx.Courses,
a => a.Id,
c => c.AuthorId,
(author, courses) =>
new {
AuthorName = author,
Courses = courses.Count()
});
#endregion
#region CROSS JOIN (LINQ Extension Method)
var crossJoinResult = ctx.Authors
.SelectMany(
a => ctx.Courses,
(author, course) =>
new {
AuthorName = author.Name,
Course = course.Title
});
#endregion
}
