生成了异常的奇怪sql 返回
SqlSugar
沟通中
4
325
悬赏:0 飞吻
public static SqlSugarClient SqlSugarClient
{
get
{
SqlSugarClient sqlDb = new(new ConnectionConfig()
{
ConnectionString = MySqlSetting.Value.MySqlConnection, // 替换为你的连接字符串
DbType = DbType.MySql, // 数据库类型
IsAutoCloseConnection = true, // 自动释放数据库
InitKeyType = InitKeyType.Attribute, // 从实体特性读取主键信息
ConfigureExternalServices = new ConfigureExternalServices
{
// DataInfoCacheService = new SugarCache(), //擴展二級緩存
SqlFuncServices = SqlFuncMySql.MySqlFuncExternal(), // 擴展自定義sql方法
},
MoreSettings = new ConnMoreSettings
{
// IsAutoRemoveDataCache = true,
// DefaultCacheDurationInSeconds = 10, //默认缓存时间为 10 s
DisableMillisecond = true, //插入和更新禁用毫秒
IsAutoDeleteQueryFilter = true, // 删除操作自动启用过滤器
IsAutoUpdateQueryFilter = true // 更新操作自动启用过滤器
},
}, db =>
{
db.QueryFilter.AddTableFilter<IBaseDomain>(it => it.IsDeleted == false);
});
sqlDb.Aop.OnLogExecuted = (sql, parameters) => { OnLogExecuted(sqlDb, sql, parameters); };
sqlDb.Aop.OnError = (exp) => { OnError(sqlDb, exp); };
return sqlDb;
}
}
// 构建关联查询
var query = _sqlSugarClient.Queryable<ProcessVersionsTable, ProcessConfigurationMainTable>(
(pv, pcm) => new JoinQueryInfos(
JoinType.Left, pv.ProcessMainId == pcm.Id
))
.WhereIF(!string.IsNullOrEmpty(request.ProcessConfigurationMainId), (pv, pcm) => pcm.Id == request.ProcessConfigurationMainId)
.WhereIF(!string.IsNullOrEmpty(request.ProcessVersionId), (pv, pcm) => pv.Id == request.ProcessVersionId)
.WhereIF(!string.IsNullOrEmpty(request.VersionNumber), (pv, pcm) => pv.VersionNumber.Contains(request.VersionNumber))
.WhereIF(!string.IsNullOrEmpty(request.VersionStatus), (pv, pcm) => pv.VersionStatus == request.VersionStatus)
.WhereIF(!string.IsNullOrEmpty(request.ProcessConfigurationNumber), (pv, pcm) => pcm.ProcessConfigurationNumber.Contains(request.ProcessConfigurationNumber))
.WhereIF(!string.IsNullOrEmpty(request.ProcessConfigurationName), (pv, pcm) => pcm.ProcessConfigurationName.Contains(request.ProcessConfigurationName))
.WhereIF(!string.IsNullOrEmpty(request.ProcessConfigurationModel), (pv, pcm) => pcm.ProcessConfigurationModel.Contains(request.ProcessConfigurationModel))
.OrderByDescending((pv, pcm) => pv.CreatedAt);
// 获取总记录数
int totalCount = await query.CountAsync(cancellationToken).ConfigureAwait(false);
// 分页查询并选择需要的字段
var list = await query
.Select((pv, pcm) => new ProcessVersionWithConfigurationInfo
{
Id = pv.Id,
ProcessMainId = pv.ProcessMainId,
VersionNumber = pv.VersionNumber,
VersionRemark = pv.VersionRemark,
IsCurrentVersion = pv.IsCurrentVersion,
VersionStatus = pv.VersionStatus,
EffectTime = pv.EffectTime,
FailureTime = pv.FailureTime,
Remark = pv.Remark,
ProcessConfigurationMainId = pcm.Id,
ProcessConfigurationNumber = pcm.ProcessConfigurationNumber,
ProcessConfigurationName = pcm.ProcessConfigurationName,
ProcessConfigurationModel = pcm.ProcessConfigurationModel,
ProcessConfigurationRemark = pcm.Remark,
IsEnabled = pcm.IsEnabled
})
.ToPageListAsync(request.Current, request.PageSize, cancellationToken)
.ConfigureAwait(false);
[SugarTable("process_versions")]
public record ProcessVersionsTable : IBaseDomain
{
/// <summary>
/// 备 注:工艺主键ID,字符串类型,
/// 默认值:
///</summary>
[SugarColumn(ColumnName = "id", IsPrimaryKey = true, Length = 36)]
public string Id { get; set; } = "";
/// <summary>
/// 备 注:关联工艺配置主表ID
/// 默认值:
///</summary>
[SugarColumn(ColumnName = "process_main_id", Length = 36)]
public string ProcessMainId { get; set; } = "";
/// <summary>
/// 备 注:版本号,
/// 默认值:
///</summary>
[SugarColumn(ColumnName = "version_number", Length = 150)]
public string VersionNumber { get; set; } = "";
/// <summary>
/// 备 注:版本备注,
/// 默认值:
///</summary>
[SugarColumn(ColumnName = "version_remark", Length = 150)]
public string VersionRemark { get; set; } = "";
/// <summary>
/// 备 注:是否为当前版本,布尔型,默认值: false。
/// 默认值:
///</summary>
[SugarColumn(ColumnName = "is_current_version")]
public bool IsCurrentVersion { get; set; }
/// <summary>
/// 备 注:版本状态,草稿 , 已发布 , 已停用
/// 默认值:
///</summary>
[SugarColumn(ColumnName = "version_status", Length = 20)]
public string VersionStatus { get; set; } = "";
/// <summary>
/// 备 注:生效时间,默认值: 最小时间。
/// 默认值:
///</summary>
[SugarColumn(ColumnName = "effect_time")]
public DateTime EffectTime { get; set; }
/// <summary>
/// 备 注:失效时间,默认值: 最小时间。
/// 默认值:
///</summary>
[SugarColumn(ColumnName = "failure_time")]
public DateTime FailureTime { get; set; }
/// <summary>
/// 备 注:备注,
/// 默认值:
///</summary>
[SugarColumn(ColumnName = "remark", Length = 255)]
public string Remark { get; set; } = "";
/// <summary>
/// 备 注:创建时间
/// 默认值:
///</summary>
[SugarColumn(ColumnName = "created_at", IsOnlyIgnoreUpdate = true)]
public DateTime CreatedAt { get; set; } = DateTime.Now;
/// <summary>
/// 备 注:创建者主键
/// 默认值:
///</summary>
[SugarColumn(ColumnName = "created_by_id", Length = 36, IsOnlyIgnoreUpdate = true)]
public string CreatedById { get; set; } = "";
/// <summary>
/// 备 注:更新时间
/// 默认值:
///</summary>
[SugarColumn(ColumnName = "updated_at")]
public DateTime UpdatedAt { get; set; } = DateTime.Now;
/// <summary>
/// 备 注:更新者主键
/// 默认值:
///</summary>
[SugarColumn(ColumnName = "updated_by_id", Length = 36)]
public string UpdatedById { get; set; } = "";
/// <summary>
/// 备 注:是否删除
/// 默认值:
///</summary>
[SugarColumn(ColumnName = "is_deleted")]
public bool IsDeleted { get; set; }
}
[SugarTable("process_configuration_main")]
public record ProcessConfigurationMainTable : IBaseDomain
{
/// <summary>
/// 备 注:工艺配置ID,主键
/// 默认值:
///</summary>
[SugarColumn(ColumnName = "id", IsPrimaryKey = true, Length = 36)]
public string Id { get; set; } = "";
/// <summary>
/// 备 注:工艺配置编号,
/// 默认值:
///</summary>
[SugarColumn(ColumnName = "process_configuration_number", Length = 50)]
public string ProcessConfigurationNumber { get; set; } = "";
/// <summary>
/// 备 注:工艺配置名称,
/// 默认值:
///</summary>
[SugarColumn(ColumnName = "process_configuration_name", Length = 255)]
public string ProcessConfigurationName { get; set; } = "";
/// <summary>
/// 备 注:适用产品型号,
/// 默认值:
///</summary>
[SugarColumn(ColumnName = "process_configuration_model", Length = 50)]
public string ProcessConfigurationModel { get; set; } = "";
/// <summary>
/// 备 注:备注,
/// 默认值:
///</summary>
[SugarColumn(ColumnName = "remark", Length = 500)]
public string Remark { get; set; } = "";
/// <summary>
/// 备 注:创建时间
/// 默认值:
///</summary>
[SugarColumn(ColumnName = "created_at", IsOnlyIgnoreUpdate = true)]
public DateTime CreatedAt { get; set; } = DateTime.Now;
/// <summary>
/// 备 注:创建者主键
/// 默认值:
///</summary>
[SugarColumn(ColumnName = "created_by_id", Length = 36, IsOnlyIgnoreUpdate = true)]
public string CreatedById { get; set; } = "";
/// <summary>
/// 备 注:更新时间
/// 默认值:
///</summary>
[SugarColumn(ColumnName = "updated_at")]
public DateTime UpdatedAt { get; set; } = DateTime.Now;
/// <summary>
/// 备 注:更新者主键
/// 默认值:
///</summary>
[SugarColumn(ColumnName = "updated_by_id", Length = 36)]
public string UpdatedById { get; set; } = "";
/// <summary>
/// 备 注:是否启用
/// 默认值:
///</summary>
[SugarColumn(ColumnName = "is_enabled", Length = 1)]
public bool IsEnabled { get; set; } = true;
/// <summary>
/// 备 注:是否删除
/// 默认值:
///</summary>
[SugarColumn(ColumnName = "is_deleted", Length = 1)]
public bool IsDeleted { get; set; }
}
生成了以下sql语句 .
SELECT
`pv`.`id` AS `Id`,
`pv`.`process_main_id` AS `ProcessMainId`,
`pv`.`version_number` AS `VersionNumber`,
`pv`.`version_remark` AS `VersionRemark`,
`pv`.`is_current_version` AS `IsCurrentVersion`,
`pv`.`version_status` AS `VersionStatus`,
`pv`.`effect_time` AS `EffectTime`,
`pv`.`failure_time` AS `FailureTime`,
`pv`.`remark` AS `Remark`,
`pcm`.`id` AS `ProcessConfigurationMainId`,
`pcm`.`process_configuration_number` AS `ProcessConfigurationNumber`,
`pcm`.`process_configuration_name` AS `ProcessConfigurationName`,
`pcm`.`process_configuration_model` AS `ProcessConfigurationModel`,
`pcm`.`remark` AS `ProcessConfigurationRemark`,
`pcm`.`is_enabled` AS `IsEnabled`
FROM
`process_versions` `pv`
LEFT JOIN
`process_configuration_main` `pcm` ON (`pv`.`process_main_id` = `pcm`.`id`)
AND (`pcm`.`is_deleted` = 0)
AND (`pcm`.`is_deleted` = 0)
WHERE
(`pv`.`is_deleted` = 0)
AND (`pv`.`is_deleted` = 0)
ORDER BY `pv`.`created_at` DESC
LIMIT 0 , 20这个sql是不是有问题的?
当前使用版本 : 5.1.4.199
热忱回答(4)
-
fate sta VIP0
2025/8/20await query.Clone().CountAsync
少了clone()
0 回复 -
fate sta VIP0
2025/8/20另外如果有问题说清楚SQL哪里不对。
0 回复 -
Aaron 傲 VIP0
2025/8/20@fate sta: 注意那个where
一模一样的条件的?(`pv`.`is_deleted` = 0)AND (`pv`.`is_deleted` = 0)还有这个
`process_versions` `pv`LEFT JOIN`process_configuration_main` `pcm` ON (`pv`.`process_main_id` = `pcm`.`id`)AND (`pcm`.`is_deleted` = 0)AND (`pcm`.`is_deleted` = 0)0 回复 -
Aaron 傲 VIP0
2025/8/20@fate sta:我试试这个先 .
0 回复