单网页网站源码,攻略网站的建设,wordpress开发工具,海南网站建设哪里有2.4.5 EF Core -- 查询关联数据加载客户端与服务端运算跟踪与不跟踪复杂查询运算原生 SQL 查询全局查询筛选器关联数据加载学员和助教都在项目分组中#xff0c;调整模型#xff0c;删除 AssistantProjectGroup 添加 Member 列表public ListMember Members { get; se…2.4.5 EF Core -- 查询关联数据加载客户端与服务端运算跟踪与不跟踪复杂查询运算原生 SQL 查询全局查询筛选器关联数据加载学员和助教都在项目分组中调整模型删除 AssistantProjectGroup 添加 Member 列表public ListMember Members { get; set; }Member 添加 是否助教判断分组信息public bool IsAssistant { get; set; }public string GroupId { get; set; }public ProjectGroup Group { get; set; }Task 添加 学员信息public Member Member { get; set; }接下来为每一个表添加一个控制器一个 Project 对应多个 ProjectGroupProjectGroupnamespace LighterApi.Controller
{[ApiController][Route(api/[controller])]public class ProjectGroupController : ControllerBase{private readonly LighterDbContext _lighterDbContext;public ProjectGroupController(LighterDbContext lighterDbContext){_lighterDbContext lighterDbContext;}[HttpPost]public async TaskIActionResult Create([FromBody] ProjectGroup group){_lighterDbContext.ProjectGroups.Add(group);await _lighterDbContext.SaveChangesAsync();return StatusCode((int) HttpStatusCode.Created, group);}[HttpGet][Route({id})]public async TaskIActionResult GetAsync(string id, CancellationToken cancellationToken){var project await _lighterDbContext.Projects.FirstOrDefaultAsync(p p.Id id, cancellationToken);return Ok(project);}}
}迁移dotnet ef migrations add RefactoryProjectEntitiesdotnet ef database updateEntity 主键添加自动生成/// summary
/// 主键Id
/// /summary
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public string Id { get; set; }启动程序Postman 访问ProjectController[HttpGet]
[Route({id})]
public async TaskIActionResult GetAsync(string id, CancellationToken cancellationToken)
{var project await _lighterDbContext.Projects.FirstOrDefaultAsync(p p.Id id, cancellationToken);return Ok(project);
}查询项目信息发现分组信息 groups 为空因为 EF 默认不会查询关联数据所以需要实现一下ProjectController 获取项目时使用 Include[HttpGet]
[Route({id})]
public async TaskIActionResult GetAsync(string id, CancellationToken cancellationToken)
{var project await _lighterDbContext.Projects.Include(p p.Groups).FirstOrDefaultAsync(p p.Id id, cancellationToken);return Ok(project);
}由于项目中有分组引用分组中有项目引用所以需要在序列化的时候处理循环引用Startupservices.AddControllers().AddNewtonsoftJson(xx.SerializerSettings.ReferenceLoopHandling ReferenceLoopHandling.Ignore);这样就可以查到项目信息EF Core 为我们提供了三种加载数据的方式预先加载显式加载延迟加载加载相关数据https://docs.microsoft.com/zh-cn/ef/core/querying/related-data/预先加载预先加载表示从数据库中加载关联数据作为初始查询的一部分。在以下示例中结果中返回的blogs将使用关联的posts填充其 Posts 属性。using (var context new BloggingContext())
{var blogs context.Blogs.Include(blog blog.Posts).ToList();
}显式加载显式加载表示稍后从数据库中显式加载关联数据。可以通过 DbContext.Entry(...) API 显式加载导航属性。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();
}ProjectController// 显式加载
var project await _lighterDbContext.Projects.FirstOrDefaultAsync(p p.Id id, cancellationToken);
await _lighterDbContext.Entry(project).Collection(p p.Groups).LoadAsync(cancellationToken);延迟加载延迟加载表示在访问导航属性时从数据库中以透明方式加载关联数据。使用延迟加载的最简单方式是通过安装 Microsoft.EntityFrameworkCore.Proxies 包并通过调用 UseLazyLoadingProxies 来启用该包。protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) optionsBuilder.UseLazyLoadingProxies().UseSqlServer(myConnectionString);或在使用 AddDbContext 时.AddDbContextBloggingContext(b b.UseLazyLoadingProxies().UseSqlServer(myConnectionString));EF Core 接着会为可重写的任何导航属性即必须是 virtual 且在可被继承的类上启用延迟加载。 例如在以下实体中Post.Blog 和 Blog.Posts 导航属性将被延迟加载。public class Blog
{public int Id { get; set; }public string Name { get; set; }public virtual ICollectionPost 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; }
}Projectpublic virtual ICollectionProjectGroup Groups { get; set; }ProjectController// 延迟加载
project.Groups// 引用到属性时才加载客户端与服务端运算客户端与服务端运算https://docs.microsoft.com/zh-cn/ef/core/querying/client-eval由于 SQL Server 提供程序不了解此方法的实现方式因此无法将其转换为 SQL。 查询的所有其余部分是在数据库中评估的但通过此方法传递返回的 URL 却是在客户端上完成。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();跟踪与不跟踪跟踪与不跟踪https://docs.microsoft.com/zh-cn/ef/core/querying/tracking默认情况下跟踪返回实体类型的查询。 这表示可以更改这些实体实例然后通过 SaveChanges() 持久化这些更改。非跟踪查询var blogs context.Blogs.AsNoTracking().ToList();还可以在上下文实例级别更改默认跟踪行为context.ChangeTracker.QueryTrackingBehavior QueryTrackingBehavior.NoTracking;var blogs context.Blogs.ToList();
复杂查询运算复杂查询运算https://docs.microsoft.com/zh-cn/ef/core/querying/complex-query-operators联接var query from photo in context.SetPersonPhoto()join person in context.SetPerson()on photo.PersonPhotoId equals person.PhotoIdselect new { person, photo };GroupJoinvar query from b in context.SetBlog()join p in context.SetPost()on b.BlogId equals p.PostId into groupingselect new { b, grouping };SelectManyvar query from b in context.SetBlog()from p in context.SetPost()select new { b, p };GroupByvar query from p in context.SetPost()group p by p.AuthorId into gselect new{g.Key,Count g.Count()};Left Joinvar query from b in context.SetBlog()join p in context.SetPost()on b.BlogId equals p.BlogId into groupingfrom p in grouping.DefaultIfEmpty()select new { b, p };原生 SQL 查询原生 SQL 查询https://docs.microsoft.com/zh-cn/ef/core/querying/raw-sqlvar blogs context.Blogs.FromSqlRaw(SELECT * FROM dbo.Blogs).ToList();全局查询筛选器全局查询筛选器https://docs.microsoft.com/zh-cn/ef/core/querying/filtersmodelBuilder.EntityBlog().HasQueryFilter(b EF.Propertystring(b, _tenantId) _tenantId);
modelBuilder.EntityPost().HasQueryFilter(p !p.IsDeleted);所有实体都继承了基类 Entity所以这样会把过滤器添加在所有查询上面LighterDbContextmodelBuilder.EntityEntity().HasQueryFilter(x x.TenantId );GitHub源码链接https://github.com/MINGSON666/Personal-Learning-Library/tree/main/ArchitectTrainingCamp/LighterApi
