FreeSql 子表查询

1、查询参数：

/// <summary>
/// 查询参数
/// </summary>
public class QueryInWork : BaseTimeQuery
{
	/// <summary>
	/// 数量上限
	/// 小于等于0时，统计次数
	/// 大于0时，统计小于该次数的人员
	/// </summary>
	public int InWellUpper { get; set; }
	/// <summary>
	/// 开始时间
	/// </summary>
	public DateTime BeginTime { get; set; }
	/// <summary>
	/// 结束时间
	/// </summary>
	public DateTime EndTime { get; set; }		
	/// <summary>
	/// 每页大小
	/// </summary>
	public int PageSize { get; set; }
	/// <summary>
	/// 第几页
	/// </summary>
	public int PageIndex { get; set; }
}

2、实体A，数据库有对应表

/// <summary>
/// 实体A
/// </summary>
[Table(Name = "InWork")]
public class InWorkEntity
{
	/// <summary>
	/// 惟一标识符
	/// </summary>
	[Column(IsPrimary = true, IsIdentity = true )]
	public long Id { get; set; }
	
	/// <summary>
	/// 人员ID
	/// </summary>
	public long EmpId { get; set; }
	
	/// <summary>
	/// 时间
	/// </summary>
	public System.DateTime InWorkTime { get; set; }
}

3、实体B，数据库有对应视图

/// <summary>
/// 人员
/// </summary>
[Table(Name = "Employee", DisableSyncStructure = true)]
public class EmployeeEntity
{
	/// <summary>
	/// 标识符
	/// </summary>
	public long Id { get; set; }
	/// <summary>
	/// 名称
	/// </summary>
	public string Name { get; set; }
}

4、实体C,数据库没有对应表，也没有视图

/// <summary>
/// 统计
/// </summary>
[Table(Name = "", DisableSyncStructure = true)]
public class StatInWork
{
	/// <summary>
	/// ID
	/// </summary>
	public long EmployeeId { get; set; }

	/// <summary>
	/// 数量
	/// </summary>
	public int InWorkTotal { get; set; }
}

5、返回数据

/// <summary>
/// 统计信息
/// </summary>
public class StatInWorkInfo
{
	/// <summary>
	/// 标识符
	/// </summary>
	public long Id { get; set; }
	/// <summary>
	/// 姓名
	/// </summary>
	public string Name { get; set; }       
	/// <summary>
	/// 次数
	/// </summary>
	public int InWorkTotal { get; set; }
}

6、查询方法, 主要利用了AsTable实现

/// <summary>
/// 统计次数
/// </summary>
/// <param name="queryParam"></param>
/// <returns></returns>
public ResultListOfPageInfo<StatInWorkInfo> StatEmployeeInWork(QueryInWork queryParam)
{
	if (queryParam.BeginTime <= DateTime.MinValue || queryParam.EndTime <= DateTime.MinValue)
		throw new Exception("请输入正确的起止时间");

	var beginTime = queryParam.BeginTime;
	beginTime = beginTime.AddMonths(2);
	if (beginTime < queryParam.EndTime)
		throw new Exception("查询起止时间请限制在2个月以内!");

	Expression<Func<InWorkEntity, bool>> exp =
		(t) => t.InTime >= queryParam.BeginTime && t.InTime <= queryParam.EndTime;

	var selectInWork = this.dbContext._freeSql.Select<InWorkEntity>().Where(exp)
		.GroupBy(x => x.EmpId);
	if (queryParam.InWorkUpper > 0)
	{
		selectInWork = selectInWork.Having(t => t.Count() < queryParam.InWorkUpper);
	}

	Func<Type, string, string> tableRule = (type, oldname) =>
	{
		if (type == typeof(StatInWork)) return " (" + selectInWork.ToSql(x => 
		new { EmpId = x.Key, InWorkTotal = x.Count() }, FieldAliasOptions.AsProperty) + ") ";

		return oldname;
	};

	var select = this.dbContext._freeSql.Select<EmployeeEntity, StatInWork>()
		.AsTable(tableRule)
		.InnerJoin((x, y) => x.Id == y.EmpId);		

	var result = new ResultListOfPageInfo<StatInWorkInfo>();
	if (queryParam.PageIndex > 0 && queryParam.PageSize > 0)
	{
		result.Total = select.Count();
		select = select.Page(queryParam.PageIndex, queryParam.PageSize);
	}
	select = select.OrderBy((x, y) => x.Name);

	var dtoList = select.ToList((x, y) => new StatInWorkInfo
	{
		Id = x.Id,
		Name = x.Name,		
		InWorkTotal = y.InWorkTotal,
	});

	if (result.Total == 0)
		result.Total = dtoList.Count;            
	result.List = dtoList;

	return result;
}

7、生成的SQL如下：

SELECT a.[Id] as1, a.[Name] as2, b.[InWorkTotal] as3 
FROM [Employee] a 
INNER JOIN (SELECT a.[EmpId], count(1) InWorkTotal 
    FROM [InWork] a 
    WHERE (a.[InTime] >= '2021-01-01 00:00:00.000' AND a.[InTime] <= '2021-01-31 00:00:00.000') 
    GROUP BY a.[EmpId]) b ON a.[Id] = b.[EmpId] 
ORDER BY a.[Name]