.NET 云原生架构师训练营（模块二 基础巩固 EF Core 查询）--学习笔记

public List<Member> Members { get; set; }

public bool IsAssistant { get; set; }

public string GroupId { get; set; }

public ProjectGroup Group { get; set; }

public Member Member { get; set; }

namespace LighterApi.Controller
{
    [ApiController]
    [Route("api/[controller]")]
    public class ProjectGroupController : ControllerBase
    {
        private readonly LighterDbContext _lighterDbContext;

        public ProjectGroupController(LighterDbContext lighterDbContext)
        {
            _lighterDbContext = lighterDbContext;
        }

        [HttpPost]
        public async Task<IActionResult> Create([FromBody] ProjectGroup group)
        {
            _lighterDbContext.ProjectGroups.Add(group);
            await _lighterDbContext.SaveChangesAsync();

            return StatusCode((int) HttpStatusCode.Created, group);
        }
        
        [HttpGet]
        [Route("{id}")]
        public async Task<IActionResult> GetAsync(string id, CancellationToken cancellationToken)
        {
            var project = await _lighterDbContext.Projects.FirstOrDefaultAsync(p => p.Id == id, cancellationToken);
            return Ok(project);
        }
    }
}

dotnet ef migrations add RefactoryProjectEntities

dotnet ef database update

/// <summary>
/// 主键Id
/// </summary>
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public string Id { get; set; }

[HttpGet]
[Route("{id}")]
public async Task<IActionResult> GetAsync(string id, CancellationToken cancellationToken)
{
    var project = await _lighterDbContext.Projects.FirstOrDefaultAsync(p => p.Id == id, cancellationToken);
    return Ok(project);
}

[HttpGet]
[Route("{id}")]
public async Task<IActionResult> GetAsync(string id, CancellationToken cancellationToken)
{
    var project = await _lighterDbContext.Projects.Include(p => p.Groups)
        .FirstOrDefaultAsync(p => p.Id == id, cancellationToken);
    return Ok(project);
}

services.AddControllers()
        .AddNewtonsoftJson(x=>x.SerializerSettings.ReferenceLoopHandling = ReferenceLoopHandling.Ignore);

using (var context = new BloggingContext())
{
    var blogs = context.Blogs
        .Include(blog => blog.Posts)
        .ToList();
}

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();
}

// 显式加载
var project = await _lighterDbContext.Projects.FirstOrDefaultAsync(p => p.Id == id, cancellationToken);
await _lighterDbContext.Entry(project).Collection(p => p.Groups).LoadAsync(cancellationToken);

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder
        .UseLazyLoadingProxies()
        .UseSqlServer(myConnectionString);

.AddDbContext<BloggingContext>(
    b => b.UseLazyLoadingProxies()
          .UseSqlServer(myConnectionString));

public class Blog
{
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Post> Posts { get; set; }
}

public class Post
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public virtual Blog Blog { get; set; }
}

public virtual ICollection<ProjectGroup> Groups { get; set; }

// 延迟加载
project.Groups// 引用到属性时才加载

var blogs = context.Blogs
    .OrderByDescending(blog => blog.Rating)
    .Select(blog => new
    {
        Id = blog.BlogId,
        Url = StandardizeUrl(blog.Url)// 服务端转换SQL，不了解客户端方法实现
    })
    .ToList();

public static string StandardizeUrl(string url)
{
    url = url.ToLower();

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

    return url;
}

foreach (var project in _lighterDbContext.Projects)
{
    project.Groups// 多次查询数据库
}

var projects = _lighterDbContext.Projects.ToList();

var blogs = context.Blogs
    .AsNoTracking()
    .ToList();

context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;

var blogs = context.Blogs.ToList();

var query = from photo in context.Set<PersonPhoto>()
            join person in context.Set<Person>()
                on photo.PersonPhotoId equals person.PhotoId
            select new { person, photo };

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 };

var query = from b in context.Set<Blog>()
            from p in context.Set<Post>()
            select new { b, p };

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

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 };

var blogs = context.Blogs
    .FromSqlRaw("SELECT * FROM dbo.Blogs")
    .ToList();

modelBuilder.Entity<Blog>().HasQueryFilter(b => EF.Property<string>(b, "_tenantId") == _tenantId);
modelBuilder.Entity<Post>().HasQueryFilter(p => !p.IsDeleted);

modelBuilder.Entity<Entity>().HasQueryFilter(x => x.TenantId == "");