分组查询、去重，GroupBy以后出现 列名无效 返回
钱多多 发布于2周前
热忱回答（11）
-
fate sta VIP0
2周前把3个实体类发一下
0 回复 -
钱多多 VIP0
2周前
@fate sta：
[SugarTable("laws_regulations", "法律法规表")]
public class LawsRegulations : EntityBase
{
/// <summary>
/// 分类 （0目录 1文件）
/// </summary>
public int type { get; set; }
/// <summary>
/// 父Id
/// </summary>
public long pid { get; set; }
/// <summary>
/// 名称
/// </summary>
public string title { get; set; }
/// <summary>
/// 状态（0失效 1正常）
/// </summary>
public int status { get; set; }
/// <summary>
/// 法规学习规则表Id
/// </summary>
public long LawsStudyRuleId { get; set; }
/// <summary>
/// 失效时间
/// </summary>
public DateTime? invalidTime { get; set; }
/// <summary>
/// （过期时间）失效时间
/// </summary>
public DateTime? expireTime { get; set; }
/// <summary>
/// 是否新法速递
/// </summary>
public Boolean IsNewLaw { get; set; }
/// <summary>
/// 是否系统设定，禁止修改
/// </summary>
public Boolean IsSystem { get; set; }
/// <summary>
/// 排序
/// </summary>
public int orderNo { get; set; }
/// <summary>
/// 备注
/// </summary>
public string? remark { get; set; }
[Navigate(NavigateType.OneToMany, nameof(LawsRegulationsContent.lawsRegulationsId))]
public List<LawsRegulationsContent> LawsRegulationsContentList { get; set; }
[Navigate(NavigateType.OneToMany, nameof(LawsReadUser.LawsRegulationsId))]
public List<LawsReadUser> LawsReadUserList { get; set; }
[Navigate(NavigateType.OneToOne, nameof(LawsStudyRuleId))]
public LawsStudyRule LawsStudyRule { get; set; }
[Navigate(NavigateType.OneToMany, nameof(LawsStudyUser.LawsRegulationsId))]
public List<LawsStudyUser> LawsStudyUserList { get; set; }
}
[SugarTable("laws_study_role", "角色学习法规需要时长表")]
public class LawsStudyRole : EntityBaseId
{
/// <summary>
/// 法规学习规则表Id
/// </summary>
public long LawsStudyRuleId { get; set; }
/// <summary>
/// 角色Id
/// </summary>
public long RoleId { get; set; }
/// <summary>
/// 学习所需时长（分钟）
/// </summary>
public int NeedStudyLength { get; set; }
[Navigate(NavigateType.OneToMany, nameof(SysUserRole.RoleId), nameof(RoleId))]
public List<SysUserRole> SysUserRoleList { get; set; }
}
[SugarTable(null, "系统用户角色表")]
[SystemTable]
public class SysUserRole : EntityBaseId
{
/// <summary>
/// 用户Id
/// </summary>
[SugarColumn(ColumnDescription = "用户Id")]
public long UserId { get; set; }
/// <summary>
/// 角色Id
/// </summary>
[SugarColumn(ColumnDescription = "角色Id")]
public long RoleId { get; set; }
/// <summary>
/// 角色
/// </summary>
[SugarColumn(IsIgnore = true)]
[Navigate(NavigateType.OneToOne, nameof(RoleId))]
public SysRole SysRole { get; set; }
}
0 回复 -
fate sta VIP0
2周前
用最新版本简单写了个DEMO没有重现
var xx=db.Queryable<ORdER>().Select(it => new { id1 = it.Id1 }) .MergeTable() .GroupBy(it => it.id1) .Select(it => new { id1 = it.id1 }) .ToList();生成的SQL
SELECT [id1] AS [id1] FROM (SELECT [id] AS [id1] FROM [ORdER] ) MergeTable GROUP BY [id1]
0 回复 -
fate sta VIP0
2周前
c#代码也发一下
0 回复 -
fate sta VIP0
2周前
发文本
0 回复 -
钱多多 VIP0
2周前
@fate sta：
var NeedStudyUserCountList = _lawsRegulationsRep.AsQueryable()
.InnerJoin<LawsStudyRole>((a, b) => a.LawsStudyRuleId == b.LawsStudyRuleId)
.InnerJoin<SysUserRole>((a, b, c) => b.RoleId == c.RoleId)
.Where((a, b, c) => a.status == SqlFunc.ToInt32(StatusEnum.Enable) && a.type == 1)
.Select((a, b, c) => new
{
rindex = SqlFunc.RowNumber($"{b.NeedStudyLength} desc ", $"{a.Id},{c.UserId}"),
Id = a.Id,
LawsStudyRuleId = a.LawsStudyRuleId,
UserId = c.UserId,
NeedStudyLength = b.NeedStudyLength
})
.MergeTable()
.Where(u => u.rindex == 1)
.GroupBy(u => u.LawsStudyRuleId)
.Select(u => new { LawsStudyRuleId = u.LawsStudyRuleId, NeedStudyUserCount = SqlFunc.AggregateCount(u.UserId) })
.ToList();
0 回复 -
钱多多 VIP0
2周前
@fate sta：
这么写可以查出数据，我估计是因为MergeTable之前没有Select，字段没有别名，所以Groupby的字段还是数据库字段，可以识别
0 回复 -
fate sta VIP0
2周前
可以重现了，匿名对象进了AOP
if (!p.EntityName.StartsWith("<>f__AnonymousType"))
{
if (!p.DbColumnName.Contains("_"))
p.DbColumnName = UtilMethods.ToUnderLine(p.DbColumnName);//ToUnderLine驼峰转下划线方法
}
0 回复 -
fate sta VIP0
2周前
后面版本AOP我将过滤匿名对象
0 回复 -
fate sta VIP0
2周前
你先手动处理一下，加 if (!p.EntityName.StartsWith("<>f__AnonymousType"))
0 回复 -
钱多多 VIP0
2周前
@fate sta：好的，非常感谢。新年快乐🌷🌷
0 回复