dotnetcore / dotnetcore/FreeSql
Repository.Select.From<T>() 多表查询生成重复表别名,导致 SQL 错误
- Dominant language
- C#
- Stars
- 4.4k
- Forks
- 910
- PR merge metrics
- No merged PRs in 30d
Description
使用仓储的 `Select.From()` 做多表查询时,生成的 SQL 两张表使用了相同别名 `a`,导致 SQL 错误。
C# lambda 参数名虽然分别是 `auth` 和 `a`,但生成 SQL 时两个表都被命名成了 `a`。
### 复现代码
```csharp
var apiConfigList = await _logisticsAccountsAuthorizationRepository.Select
.From()
.InnerJoin((auth, a) => auth.LogisticsAccountId == a.Id)
.Where((auth, a) => accountIds.Contains(a.Id) && a.IsEnabled && auth.ApiType == 1)
.ToListAsync((auth, a) => new
{
AccountDbId = a.Id,
a.AccountId,
auth.DeveloperAccountId,
auth.ApiKey,
auth.ApiSeceret,
auth.OrderApiHost
});
```
### 实际生成的 SQL
```sql
SELECT a.Id as1, a.AccountId as2, a.DeveloperAccountId as3, a.ApiKey as4, a.ApiSeceret as5, a.OrderApiHost as6
FROM logistics_accounts_authorization a
INNER JOIN logistics_accounts a ON a.LogisticsAccountId = a.Id AND (a.TenantId = 2) AND (a.IsDeleted = 0)
WHERE (((a.Id) in (14,13)) AND a.IsEnabled AND a.ApiType = 1) AND (a.IsDeleted = 0)
```
### 期望结果
两张表应该生成不同别名,例如:
```sql
FROM logistics_accounts_authorization a
INNER JOIN logistics_accounts b ON a.LogisticsAccountId = b.Id
```
并且字段引用也应该分别对应正确的表别名。
### 临时解决方案
改成直接使用 `Orm.Select()` 后可以正常生成不同别名:
```csharp
var apiConfigList = await _logisticsAccountsAuthorizationRepository.Orm
.Select()
.InnerJoin((auth, account) => auth.LogisticsAccountId == account.Id)
.Where((auth, account) => accountIds.Contains(account.Id) && account.IsEnabled && auth.ApiType == 1)
.ToListAsync((auth, account) => new
{
AccountDbId = account.Id,
account.AccountId,
auth.DeveloperAccountId,
auth.ApiKey,
auth.ApiSeceret,
auth.OrderApiHost
});
```
### 想确认
请问这是 `Repository.Select.From()` 多表查询别名生成的问题,还是这种写法本身不推荐使用?如果是用法问题,推荐的仓储多表查询写法是什么?
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.