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.IncludeIf(!string.IsNullOrEmpty(keyword), u => u.Detail);
// 示例六
var person = repository.IncludeIf(!string.IsNullOrEmpty(keyword), u => u.Detail)
.IncludeIf(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 框架还提供了时态查询功能,可以查询特定时间的数据,如:

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():不追踪实体
  • 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