⭐️ 如果你喜欢 Furion ,可以在 Gitee 中给个star ⭐️
Skip to main content

9.11 高级查询操作

9.11.1 关联数据模型#

using Furion.DatabaseAccessor;using System;using System.Collections.Generic;using System.ComponentModel.DataAnnotations;
namespace Furion.Core{    public class Person : Entity    {        /// <summary>        /// 构造函数        /// </summary>        public Person()        {            CreatedTime = DateTime.Now;            IsDeleted = false;        }
        /// <summary>        /// 姓名        /// </summary>        [MaxLength(32)]        public string Name { get; set; }
        /// <summary>        /// 年龄        /// </summary>        public int Age { get; set; }
        /// <summary>        /// 住址        /// </summary>        public string Address { get; set; }
        /// <summary>        /// 从表        /// </summary>        public PersonDetail PersonDetail { get; set; }
        /// <summary>        /// 一对多        /// </summary>        public ICollection<Children> Childrens { get; set; }
        /// <summary>        /// 多对多        /// </summary>        public ICollection<Post> Posts { get; set; }    }}

9.11.2 一对一查询#

// 示例一var person = repository.Include(u => u.Detail);
// 示例二var person = repository.Include(u => u.Detail)                       .Include(u => u.Post);
// 示例三var person = repository.Include(u => u.Detail)                            .ThenInclude(d => d.Review)                       .Include(u => u.Post);
// 示例四var person = repository.Include(u => u.Detail.Where(d => d.Id > 10).OrderBy(d => d.Name))                            .ThenInclude(d => d.Review)                       .Include(u => u.Post);
// 示例五var person = repository.Include(!string.IsNullOrEmpty(keyword), u => u.Detail);
// 示例六var person = repository.Include(!string.IsNullOrEmpty(keyword), u => u.Detail)                       .Include(age > 18, u => u.Detail.Where(d => d.Id > 10).OrderBy(d => d.Name))                            .ThenInclude(d => d.Review)                       .Include(u => u.Post);

9.11.3 一对多查询#

// 示例一var person = repository.Include(u => u.Childrens);
// 参考 一对一 例子
特别说明

一对一一对多 查询方法一样,唯一的区别是:一对多 采用 ICollection<TEntity> 定义属性。

9.11.4 多对多查询#

// 示例一var person = repository.Include(u => u.Posts);
// 参考 一对一 例子
特别说明

一对一多对多 查询方法一样,唯一的区别是:多对多 采用 ICollection<TEntity> 定义属性。

9.11.5 联表查询#

9.11.5.1 内连接 Inner Join#

var query = from p in _personRepository.AsQueryable()            join d in _personDetailRepository.AsQueryable() on p.Id equals d.PersonId            select new PersonDto            {                PhoneNumber = p.PersonDetail.PhoneNumber,                Address = p.Address,                Age = p.Age,                Name = p.Name,                Id = p.Id,                QQ = p.PersonDetail.QQ            };

9.11.5.2 左连接 Left Join#

var query = from p in _personRepository.AsQueryable()            join d in _personDetailRepository.AsQueryable() on p.Id equals d.PersonId into results            from d in results.DefaultIfEmpty()            select new PersonDto            {                PhoneNumber = p.PersonDetail.PhoneNumber,                Address = p.Address,                Age = p.Age,                Name = p.Name,                Id = p.Id,                QQ = p.PersonDetail.QQ            };
小提示

Left JoinInner Join 不同的是,Left Join 会先将结果 into 到新的结果集然后再查询,并调用 DefaultIfEmpty() 方法。

9.11.5.3 右连接 Right Join#

Right Join 只需要将 Left Join 主从表位置更换即可。

9.11.6 分组查询#

// 示例一var query = repository.AsQueryable().GroupBy(x => new { x.Column1, x.Column2 });
// 示例二var query = from student in repository.AsQueryable()            group student by repository2.AsQueryable() into dateGroup            select new ResultData()            {                Key = dateGroup.Key,                Value = dateGroup.Count()            };
// 示例三var query = from a in repository.AsQueryable()            join b in repository2.AsQueryable() on a.Id equals b.Aid            join c in repository3.AsQueryable() on c.id equals b.Bid            group emp by new { emp.Age, emp.Sex } into g            select new {                Peo = g.Key,                Count = g.Count()            };

9.11.7 合并结果集#

var query = repository.AsQueryable(u => u.Id > 10)                      .Union(                          repository2.AsQueryable(u => u.Id <= 10)                      );

9.11.8 查询排序#

9.11.8.1 正序#

// 示例一var query = repository.AsQueryable()                      .OrderBy(u => u.Id);
// 示例二var query =repository.AsQueryable()                     .OrderBy(u => u.Id)                     .ThenBy(u => u.Name);

9.11.8.2 倒序#

// 示例一var query = repository.AsQueryable()                      .OrderByDescending(u => u.Id);
// 示例二var query =repository.AsQueryable()                     .OrderByDescending(u => u.Id)                     .ThenByDescending(u => u.Name);

9.11.8.3 混合倒序#

// 示例一var query = repository.AsQueryable()                      .OrderBy(u => u.Id)                      .OrderByDescending(u => u.Name)                      .ThenBy(u => u.Age);

9.11.9 递归查询#

using Furion.DatabaseAccessor;using Microsoft.EntityFrameworkCore;using Microsoft.EntityFrameworkCore.Metadata.Builders;using System;using System.Collections.Generic;
namespace Furion.Core{    /// <summary>    /// 城市    /// </summary>    public class City : Entity, IEntityTypeBuilder<City>, IEntitySeedData<City>    {        /// <summary>        /// 构造函数        /// </summary>        public City()        {            CreatedTime = DateTime.Now;            IsDeleted = false;        }
        /// <summary>        /// 名称        /// </summary>        public string Name { get; set; }
        /// <summary>        /// 上级Id        /// </summary>        public int? ParentId { get; set; }
        /// <summary>        /// 上级        /// </summary>        public virtual City Parent { get; set; }
        /// <summary>        /// 子集        /// </summary>        public virtual ICollection<City> Childrens { get; set; }
        /// <summary>        /// 配置实体关系        /// </summary>        /// <param name="entityBuilder"></param>        /// <param name="dbContext"></param>        /// <param name="dbContextLocator"></param>        public void Configure(EntityTypeBuilder<City> entityBuilder, DbContext dbContext, Type dbContextLocator)        {            entityBuilder                .HasMany(x => x.Childrens)                .WithOne(x => x.Parent)                .HasForeignKey(x => x.ParentId)                .OnDelete(DeleteBehavior.ClientSetNull); // 必须设置这一行        }
        /// <summary>        /// 种子数据        /// </summary>        /// <param name="dbContext"></param>        /// <param name="dbContextLocator"></param>        /// <returns></returns>        public IEnumerable<City> HasData(DbContext dbContext, Type dbContextLocator)        {            return new List<City>            {                new City { Id=1,CreatedTime =DateTime.Parse("2020-08-20 15:30:20"),IsDeleted=false,Name="中国" },                new City { Id=2,CreatedTime =DateTime.Parse("2020-08-20 15:30:20"),IsDeleted=false,Name="广东省",ParentId=1 },                new City { Id=3,CreatedTime =DateTime.Parse("2020-08-20 15:30:20"),IsDeleted=false,Name="中山市",ParentId=2 },                new City { Id=4,CreatedTime =DateTime.Parse("2020-08-20 15:30:20"),IsDeleted=false,Name="珠海市",ParentId=2 },                new City { Id=5,CreatedTime =DateTime.Parse("2020-08-20 15:30:20"),IsDeleted=false,Name="浙江省",ParentId=1 },            };        }    }}
var cities = await repository.AsQueryable()                             .Include(u => u.Childrens)                             .Where(u => u.Id == 1)                             .ToListAsync();
var dtos = cities.Adapt<List<CityDto>>();

9.11.10 动态 Sql 查询#

Furion 默认不支持 动态 Sql 查询功能,不过可以通过第三方实现:

Furion 项目层安装 System.Linq.Dynamic.Corehttps://github.com/zzzprojects/System.Linq.Dynamic.Core

9.11.10.1 动态 Sql#

// 示例一var query = repository.AsQueryable()                      .Where("City == @0 and Orders.Count >= @1", "China", 10)                      .OrderBy("CompanyName")                      .Select("new(CompanyName as Name, Phone)");
// 示例二var list = repository.AsQueryable()                     .Where("Name.Contains(@0)","Furion")                     .ToList();
// 示例三,支持 ? 语法var customers = repository.AsQueryable()                          .Include(c => c.Location)                          .Where(c => c.Location?.Name == "test") // 注意 Location?.Name                          .ToList();

9.11.10.2 动态 Lambda#

// 示例一var x = Expression.Parameter(typeof(int), "x");var y = Expression.Parameter(typeof(int), "y");var e = DynamicExpressionParser            .ParseLambda(new ParameterExpression[] { x, y }, null, "(x + y) * 2");
// 示例二var e = DynamicExpressionParser.ParseLambda(        typeof(Customer), typeof(bool),        "City = @0 and Orders.Count >= @1",        "London", 10);

9.11.11 时态查询#

功能移除声明

以下内容在 Furion 2.13 + 版本中已移除。

Furion 框架还提供了时态查询功能,可以查询特定时间的数据,如:

var result = rep.Entities    .AsTemporalOf(DateTime.UtcNow.AddDays(-1))    .Include(i=> i.Company)    .FirstOrDefault(i => i.Name == "Furion");

另外提供了多个时态查询方法

  • AsTemporalAll()
  • AsTemporalAsOf(date)
  • AsTemporalFrom(startDate, endDate)
  • AsTemporalBetween(startDate, endDate)
  • AsTemporalContained(startDate, endDate)

9.11.12 性能优化#

默认情况下,EF Core 会跟踪所有实体,也就是任何数据改变都会引起数据检查,所以如果只做查询操作,建议关闭实体跟踪功能。

Furion 框架提供了以下高性能实体集合:

  • DetachedEntities:脱轨/不追踪实体
  • AsQueryable(false):不追踪实体
  • Entities.AsNoTracking():手动关闭实体追踪

EF Core 中,复杂查询总是会生成一个 sql,也就是 AsSingleQuery(),我们也可以设置为 AsSplitQuery() 切割成多个查询。

9.11.13 分表查询小例子#

using Furion.DatabaseAccessor;using Microsoft.EntityFrameworkCore;using Microsoft.EntityFrameworkCore.Metadata.Builders;using System;using System.Collections.Generic;
namespace Furion.Core{    public class Person : Entity, IEntityTypeBuilder<Person>    {        public string Name { get; set; }
        /// <summary>        /// 配置实体关系        /// </summary>        /// <param name="entityBuilder"></param>        /// <param name="dbContext"></param>        /// <param name="dbContextLocator"></param>        public void Configure(EntityTypeBuilder<City> entityBuilder, DbContext dbContext, Type dbContextLocator)        {            entityBuilder.ToSqlQuery(              @"select * from dbo.person.2020-09-19                union all                select * from dbo.person.2020-09-20");        }    }}
var posts = repository.Where(u => u.Id > 10).ToList();

9.11.14 反馈与建议#

与我们交流

给 Furion 提 Issue

✨ 尝鲜 .NET 6 Preview 6 版本 ✨