.NET中PetaPoco的SQL UNION语句执行报错求助

问题现象 #

SQL UNION语句在SSMS中执行正常，但在.NET Framework中使用PetaPoco执行时，持续报错：

使用UNION、INTERSECT或EXCEPT运算符组合的所有查询必须在目标列表中有相同数量的表达式。

已做测试 #

• 确认各列数据类型均正确；
• 每个SELECT语句均包含14列；
• 移除UNION后，单个SELECT语句均可正常查询数据。

后端代码 #

Sql sql = Sql.Builder;
sql.Select("GC.CategoryName AS Category, GP.ProviderName, GN.en AS GameName, GA.HasBuyFreeSpin, GA.HasFeatureGames," +
    "GA.HasJackpot, GA.EXclusive, GA.MEgaways, GA.Volatility, GA.RTP, GA.MInBet, GA.MAxBet," +
    "GP.Id AS GameProviderId, GC.Id AS GameCategoryId");
sql.From("afbGameAttribute GA WITH(NOLOCK)");
sql.InnerJoin("dbo.afbGameProvider GP WITH(NOLOCK) ON GA.GameProviderId = GP.Id");
sql.InnerJoin("dbo.afbGameCategory GC WITH(NOLOCK) ON GP.GameCategoryId = GC.Id");
sql.InnerJoin("Language.dbo.GameName GN WITH(NOLOCK) ON GA.GameName = GN.key2");
if (param.f != null)
{
    if (param.f.CategoryId > 0) { sql.Where("GC.Id = @0", param.f.CategoryId); }
    if (param.f.ProviderId > 0) { sql.Where("GP.Id = @0", param.f.ProviderId); }
    if (param.f.GameName != null) { sql.Where("GN.en LIKE @0", "%" + param.f.GameName + "%"); }
}
sql.Append(" UNION ");
sql.Select("GC.CategoryName AS Category, GP.ProviderName, GN.en AS GameName, " +
    "CAST(NULL AS TINYINT) AS HasBuyFreeSpin, CAST(NULL AS TINYINT) AS HasFeatureGames, CAST(NULL AS TINYINT) AS HasJackpot, CAST(NULL AS TINYINT) AS EXclusive, CAST(NULL AS TINYINT) AS MEgaways, CAST(NULL AS NVARCHAR(20)) AS Volatility, CAST(NULL AS DECIMAL) AS RTP, CAST(NULL AS DECIMAL(12,4)) AS MInBet, CAST(NULL AS DECIMAL(12,4)) AS MAxBet," +
    "GP.Id AS GameProviderId, GC.Id AS GameCategoryId");
sql.From("afbGame G WITH(NOLOCK)");
sql.InnerJoin("dbo.afbGameProvider GP WITH(NOLOCK) ON G.GameProviderId = GP.Id");
sql.InnerJoin("dbo.afbGameCategory GC WITH(NOLOCK) ON G.GameCategoryId = GC.Id");
sql.InnerJoin("Language.dbo.GameName GN WITH(NOLOCK) ON G.GameName = GN.key2");
sql.Where("NOT EXISTS (SELECT 1 FROM afbGameAttribute GA WITH(NOLOCK) WHERE GA.GameName = G.GameName)");
if (param.f != null)
{
    if (param.f.CategoryId > 0) { sql.Where("GC.Id = @0", param.f.CategoryId); }
    if (param.f.ProviderId > 0) { sql.Where("GP.Id = @0", param.f.ProviderId); }
    if (param.f.GameName != null) { sql.Where("GN.en LIKE @0", "%" + param.f.GameName + "%"); }
}
sql.OrderBy("GameName");

result.D = db.Page<GameAttribute>(param.p, param.n, sql);

问题原因 #

1. PetaPoco的Sql.Builder逻辑限制：它是线性拼接SQL的，调用Append("UNION")后，后续的Select/From/Where会直接拼在UNION后面，但Where方法会把所有条件追加到全局WHERE子句，而非第二个SELECT的WHERE块中，导致UNION结构混乱。
2. 分页逻辑冲突：db.Page方法会自动添加分页相关SQL（如ROW_NUMBER()），但该逻辑会直接作用于整个语句，破坏UNION的结构，最终触发列数不匹配的报错。

解决方案 #

方案1：手动构建完整UNION+分页SQL #

直接编写包含UNION和分页逻辑的完整SQL文本，手动处理条件参数：

string baseSql = @"
SELECT * FROM (
    -- 第一个查询
    SELECT GC.CategoryName AS Category, GP.ProviderName, GN.en AS GameName, GA.HasBuyFreeSpin, GA.HasFeatureGames,
        GA.HasJackpot, GA.EXclusive, GA.MEgaways, GA.Volatility, GA.RTP, GA.MInBet, GA.MAxBet,
        GP.Id AS GameProviderId, GC.Id AS GameCategoryId
    FROM afbGameAttribute GA WITH(NOLOCK)
    INNER JOIN dbo.afbGameProvider GP WITH(NOLOCK) ON GA.GameProviderId = GP.Id
    INNER JOIN dbo.afbGameCategory GC WITH(NOLOCK) ON GP.GameCategoryId = GC.Id
    INNER JOIN Language.dbo.GameName GN WITH(NOLOCK) ON GA.GameName = GN.key2
    WHERE 1=1 {0}
    UNION
    -- 第二个查询
    SELECT GC.CategoryName AS Category, GP.ProviderName, GN.en AS GameName, 
        CAST(NULL AS TINYINT) AS HasBuyFreeSpin, CAST(NULL AS TINYINT) AS HasFeatureGames, CAST(NULL AS TINYINT) AS HasJackpot, CAST(NULL AS TINYINT) AS EXclusive, CAST(NULL AS TINYINT) AS MEgaways, CAST(NULL AS NVARCHAR(20)) AS Volatility, CAST(NULL AS DECIMAL) AS RTP, CAST(NULL AS DECIMAL(12,4)) AS MInBet, CAST(NULL AS DECIMAL(12,4)) AS MAxBet,
        GP.Id AS GameProviderId, GC.Id AS GameCategoryId
    FROM afbGame G WITH(NOLOCK)
    INNER JOIN dbo.afbGameProvider GP WITH(NOLOCK) ON G.GameProviderId = GP.Id
    INNER JOIN dbo.afbGameCategory GC WITH(NOLOCK) ON G.GameCategoryId = GC.Id
    INNER JOIN Language.dbo.GameName GN WITH(NOLOCK) ON G.GameName = GN.key2
    WHERE NOT EXISTS (SELECT 1 FROM afbGameAttribute GA WITH(NOLOCK) WHERE GA.GameName = G.GameName) {0}
) AS UnionResult
ORDER BY GameName
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

List<string> conditions = new List<string>();
Dictionary<string, object> parameters = new Dictionary<string, object>
{
    ["Offset"] = (param.p - 1) * param.n,
    ["PageSize"] = param.n
};

if (param.f != null)
{
    if (param.f.CategoryId > 0)
    {
        conditions.Add("AND GC.Id = @CategoryId");
        parameters["CategoryId"] = param.f.CategoryId;
    }
    if (param.f.ProviderId > 0)
    {
        conditions.Add("AND GP.Id = @ProviderId");
        parameters["ProviderId"] = param.f.ProviderId;
    }
    if (param.f.GameName != null)
    {
        conditions.Add("AND GN.en LIKE @GameName");
        parameters["GameName"] = "%" + param.f.GameName + "%";
    }
}

string finalSql = string.Format(baseSql, string.Join(" ", conditions));
result.D = db.Query<GameAttribute>(finalSql, parameters).ToList();

// 查询总条数
string countSql = string.Format(baseSql.Replace("SELECT * FROM (", "SELECT COUNT(*) FROM (").Replace("OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY", ""), string.Join(" ", conditions));
result.Total = db.ExecuteScalar<int>(countSql, parameters);

方案2：分别构建子查询再拼接 #

用Sql.Builder分别构建两个SELECT语句，再拼接成UNION查询，手动添加分页逻辑：

// 构建第一个查询
Sql sql1 = Sql.Builder;
sql1.Select("GC.CategoryName AS Category, GP.ProviderName, GN.en AS GameName, GA.HasBuyFreeSpin, GA.HasFeatureGames," +
    "GA.HasJackpot, GA.EXclusive, GA.MEgaways, GA.Volatility, GA.RTP, GA.MInBet, GA.MAxBet," +
    "GP.Id AS GameProviderId, GC.Id AS GameCategoryId");
sql1.From("afbGameAttribute GA WITH(NOLOCK)");
sql1.InnerJoin("dbo.afbGameProvider GP WITH(NOLOCK) ON GA.GameProviderId = GP.Id");
sql1.InnerJoin("dbo.afbGameCategory GC WITH(NOLOCK) ON GP.GameCategoryId = GC.Id");
sql1.InnerJoin("Language.dbo.GameName GN WITH(NOLOCK) ON GA.GameName = GN.key2");
if (param.f != null)
{
    if (param.f.CategoryId > 0) sql1.Where("GC.Id = @0", param.f.CategoryId);
    if (param.f.ProviderId > 0) sql1.Where("GP.Id = @0", param.f.ProviderId);
    if (param.f.GameName != null) sql1.Where("GN.en LIKE @0", "%" + param.f.GameName + "%");
}

// 构建第二个查询
Sql sql2 = Sql.Builder;
sql2.Select("GC.CategoryName AS Category, GP.ProviderName, GN.en AS GameName, " +
    "CAST(NULL AS TINYINT) AS HasBuyFreeSpin, CAST(NULL AS TINYINT) AS HasFeatureGames, CAST(NULL AS TINYINT) AS HasJackpot, CAST(NULL AS TINYINT) AS EXclusive, CAST(NULL AS TINYINT) AS MEgaways, CAST(NULL AS NVARCHAR(20)) AS Volatility, CAST(NULL AS DECIMAL) AS RTP, CAST(NULL AS DECIMAL(12,4)) AS MInBet, CAST(NULL AS DECIMAL(12,4)) AS MAxBet," +
    "GP.Id AS GameProviderId, GC.Id AS GameCategoryId");
sql2.From("afbGame G WITH(NOLOCK)");
sql2.InnerJoin("dbo.afbGameProvider GP WITH(NOLOCK) ON G.GameProviderId = GP.Id");
sql2.InnerJoin("dbo.afbGameCategory GC WITH(NOLOCK) ON G.GameCategoryId = GC.Id");
sql2.InnerJoin("Language.dbo.GameName GN WITH(NOLOCK) ON G.GameName = GN.key2");
sql2.Where("NOT EXISTS (SELECT 1 FROM afbGameAttribute GA WITH(NOLOCK) WHERE GA.GameName = G.GameName)");
if (param.f != null)
{
    if (param.f.CategoryId > 0) sql2.Where("GC.Id = @0", param.f.CategoryId);
    if (param.f.ProviderId > 0) sql2.Where("GP.Id = @0", param.f.ProviderId);
    if (param.f.GameName != null) sql2.Where("GN.en LIKE @0", "%" + param.f.GameName + "%");
}

// 拼接UNION并添加分页
string unionSql = $"({sql1}) UNION ({sql2})";
string paginatedSql = $"SELECT * FROM ({unionSql}) AS UnionResult ORDER BY GameName OFFSET @0 ROWS FETCH NEXT @1 ROWS ONLY";
var allParams = sql1.Parameters.Concat(sql2.Parameters).Concat(new object[] { (param.p - 1) * param.n, param.n }).ToArray();

// 查询数据
result.D = db.Query<GameAttribute>(paginatedSql, allParams).ToList();

// 查询总条数
string countSql = $"SELECT COUNT(*) FROM ({unionSql}) AS UnionResult";
result.Total = db.ExecuteScalar<int>(countSql, sql1.Parameters.Concat(sql2.Parameters).ToArray());

内容的提问来源于stack exchange，提问作者Kiostz