Esempio n. 1
0
        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();
        }
Esempio n. 2
0
        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);
        }