EFCore实践教程四

语法格式参考

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace EFQuerying.ComplexQuery
{
    public class Sample
    {
        public static void Run()
        {
            using (var context = new BloggingContext())
            {
                #region Join
                var query = from photo in context.Set<PersonPhoto>()
                            join person in context.Set<Person>()
                                on photo.PersonPhotoId equals person.PhotoId
                            select new { person, photo };
                #endregion
            }

            using (var context = new BloggingContext())
            {
                #region GroupJoin
                var query = from b in context.Set<Blog>()
                            join p in context.Set<Post>()
                                on b.BlogId equals p.PostId into grouping
                            select new { b, grouping };
                #endregion
            }

            using (var context = new BloggingContext())
            {
                #region GroupJoinComposed
                var query = from b in context.Set<Blog>()
                            join p in context.Set<Post>()
                                on b.BlogId equals p.PostId into grouping
                            select new { b, Posts = grouping.Where(p => p.Content.Contains("EF")).ToList() };
                #endregion
            }

            using (var context = new BloggingContext())
            {
                #region SelectManyConvertedToCrossJoin
                var query = from b in context.Set<Blog>()
                            from p in context.Set<Post>()
                            select new { b, p };
                #endregion
            }

            using (var context = new BloggingContext())
            {
                #region SelectManyConvertedToJoin
                var query = from b in context.Set<Blog>()
                            from p in context.Set<Post>().Where(p => b.BlogId == p.BlogId)
                            select new { b, p };

                var query2 = from b in context.Set<Blog>()
                             from p in context.Set<Post>().Where(p => b.BlogId == p.BlogId).DefaultIfEmpty()
                             select new { b, p };
                #endregion
            }

            using (var context = new BloggingContext())
            {
                #region SelectManyConvertedToApply
                var query = from b in context.Set<Blog>()
                            from p in context.Set<Post>().Select(p => b.Url + "=>" + p.Title)
                            select new { b, p };

                var query2 = from b in context.Set<Blog>()
                             from p in context.Set<Post>().Select(p => b.Url + "=>" + p.Title).DefaultIfEmpty()
                             select new { b, p };
                #endregion
            }

            using (var context = new BloggingContext())
            {
                #region GroupBy
                var query = from p in context.Set<Post>()
                            group p by p.AuthorId into g
                            select new
                            {
                                g.Key,
                                Count = g.Count()
                            };
                #endregion
            }

            using (var context = new BloggingContext())
            {
                #region GroupByFilter
                var query = from p in context.Set<Post>()
                            group p by p.AuthorId into g
                            where g.Count() > 0
                            orderby g.Key
                            select new
                            {
                                g.Key,
                                Count = g.Count()
                            };
                #endregion
            }

            using (var context = new BloggingContext())
            {
                #region LeftJoin
                var query = from b in context.Set<Blog>()
                            join p in context.Set<Post>()
                                on b.BlogId equals p.BlogId into grouping
                            from p in grouping.DefaultIfEmpty()
                            select new { b, p };
                #endregion
            }
        }
    }
}

using Microsoft.EntityFrameworkCore;
using System.Linq;

namespace EFQuerying.RelatedData
{
    public class Sample
    {
        public static void Run()
        {
            #region SingleInclude
            using (var context = new BloggingContext())
            {
                var blogs = context.Blogs
                    .Include(blog => blog.Posts)
                    .ToList();
            }
            #endregion

            #region IgnoredInclude
            using (var context = new BloggingContext())
            {
                var blogs = context.Blogs
                    .Include(blog => blog.Posts)
                    .Select(blog => new
                    {
                        Id = blog.BlogId,
                        Url = blog.Url
                    })
                    .ToList();
            }
            #endregion

            #region MultipleIncludes
            using (var context = new BloggingContext())
            {
                var blogs = context.Blogs
                    .Include(blog => blog.Posts)
                    .Include(blog => blog.Owner)
                    .ToList();
            }
            #endregion

            #region SingleThenInclude
            using (var context = new BloggingContext())
            {
                var blogs = context.Blogs
                    .Include(blog => blog.Posts)
                        .ThenInclude(post => post.Author)
                    .ToList();
            }
            #endregion

            #region MultipleThenIncludes
            using (var context = new BloggingContext())
            {
                var blogs = context.Blogs
                    .Include(blog => blog.Posts)
                        .ThenInclude(post => post.Author)
                            .ThenInclude(author => author.Photo)
                    .ToList();
            }
            #endregion

            #region MultipleLeafIncludes
            using (var context = new BloggingContext())
            {
                var blogs = context.Blogs
                    .Include(blog => blog.Posts)
                        .ThenInclude(post => post.Author)
                    .Include(blog => blog.Posts)
                        .ThenInclude(post => post.Tags)
                    .ToList();
            }
            #endregion

            #region IncludeTree
            using (var context = new BloggingContext())
            {
                var blogs = context.Blogs
                    .Include(blog => blog.Posts)
                        .ThenInclude(post => post.Author)
                            .ThenInclude(author => author.Photo)
                    .Include(blog => blog.Owner)
                        .ThenInclude(owner => owner.Photo)
                    .ToList();
            }
            #endregion
            
            #region Eager
            using (var context = new BloggingContext())
            {
                var blog = context.Blogs
                    .Single(b => b.BlogId == 1);

                context.Entry(blog)
                    .Collection(b => b.Posts)
                    .Load();

                context.Entry(blog)
                    .Reference(b => b.Owner)
                    .Load();
            }
            #endregion

            #region NavQueryAggregate
            using (var context = new BloggingContext())
            {
                var blog = context.Blogs
                    .Single(b => b.BlogId == 1);

                var postCount = context.Entry(blog)
                    .Collection(b => b.Posts)
                    .Query()
                    .Count();
            }
            #endregion

            #region NavQueryFiltered
            using (var context = new BloggingContext())
            {
                var blog = context.Blogs
                    .Single(b => b.BlogId == 1);

                var goodPosts = context.Entry(blog)
                    .Collection(b => b.Posts)
                    .Query()
                    .Where(p => p.Rating > 3)
                    .ToList();
            }
            #endregion
        }
    }
}

using Microsoft.EntityFrameworkCore;
using System.Linq;

namespace EFQuerying.Tracking
{
    public class Sample
    {
        public static void Run()
        {
            using (var context = new BloggingContext())
            {
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();
            }

            using (var context = new BloggingContext())
            {
                // seeding database
                context.Blogs.Add(new Blog { Url = "http://sample.com/blog" });
                context.Blogs.Add(new Blog { Url = "http://sample.com/another_blog" });
                context.SaveChanges();
            }

            using (var context = new BloggingContext())
            {
                #region Tracking
                var blog = context.Blogs.SingleOrDefault(b => b.BlogId == 1);
                blog.Rating = 5;
                context.SaveChanges();
                #endregion
            }

            using (var context = new BloggingContext())
            {
                #region NoTracking
                var blogs = context.Blogs
                    .AsNoTracking()
                    .ToList();
                #endregion
            }

            using (var context = new BloggingContext())
            {
                #region ContextDefaultTrackingBehavior
                context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;

                var blogs = context.Blogs.ToList();
                #endregion
            }

            using (var context = new BloggingContext())
            {
                #region CustomProjection1
                var blog = context.Blogs
                    .Select(b =>
                        new
                        {
                            Blog = b,
                            PostCount = b.Posts.Count()
                        });
                #endregion
            }

            using (var context = new BloggingContext())
            {
                #region CustomProjection2
                var blog = context.Blogs
                    .Select(b =>
                        new
                        {
                            Blog = b,
                            Post = b.Posts.OrderBy(p => p.Rating).LastOrDefault()
                        });
                #endregion
            }

            using (var context = new BloggingContext())
            {
                #region CustomProjection3
                var blog = context.Blogs
                    .Select(b =>
                        new
                        {
                            Id = b.BlogId,
                            Url = b.Url
                        });
                #endregion
            }

            using (var context = new BloggingContext())
            {
                #region ClientProjection
                var blogs = context.Blogs
                    .OrderByDescending(blog => blog.Rating)
                    .Select(blog => new
                    {
                        Id = blog.BlogId,
                        Url = StandardizeUrl(blog)
                    })
                    .ToList();
                #endregion
            }
        }

        #region ClientMethod
        public static string StandardizeUrl(Blog blog)
        {
            var url = blog.Url.ToLower();

            if (!url.StartsWith("http://"))
            {
                url = string.Concat("http://", url);
            }

            return url;
        }
        #endregion
    }
}