[Fact]
public void 参数化有问题()
{
g.pgsql.Delete<Roles>().Where(s=>true).ExecuteAffrows();
var roleList = new List<Roles>
{
new Roles()
{
Name = "默认",
CompanyId = 0,
Type=RoleType.ALl
},
new Roles()
{
Name = "A",
CompanyId = 1,
TeamId = 2,
Type=RoleType.Team
},
new Roles()
{
Name = "B",
CompanyId = 1,
TeamId = 2,
Type=RoleType.Team
},
};
g.pgsql.Insert(roleList).ExecuteAffrows();
var defualtRoles = g.pgsql.Select<Roles>()
.Where(s => s.CompanyId == 0)
.Where(s=>s.Type==RoleType.ALl);
var filter = new { CompanyId = 1, TeamId = 2, PageIndex = 1, PageSize = 10 };
var data = g.pgsql.Select<Roles>()
.WhereIf(filter.CompanyId > 0, s => s.CompanyId == filter.CompanyId)
.WhereIf(filter.TeamId > 0, s => s.TeamId == filter.TeamId)
.UnionAll(defualtRoles)
.Count(out var _)
.OrderBy(s => s.CompanyId)
.OrderBy(s => s.Id)
.Page(filter.PageIndex, filter.PageSize)
.ToList<Roles>();
_testOutputHelper.WriteLine(data.Count.ToString());
}
public class Roles
{
[Column(IsIdentity = true, IsPrimary = true)] public Int32 Id { get; set; }
public String Name { get; set; }
public int CompanyId { get; set; }
public int TeamId { get; set; }
public RoleType Type { get; set; }
}
public enum RoleType
{
ALl=10,
Team=20,
User=30
}
生成的sql
SELECT a."id", a."name", a."companyid", a."teamid", a."type"
FROM ( SELECT a."id", a."name", a."companyid", a."teamid", a."type"
FROM "roles" a
WHERE (a."companyid" = @exp_0) AND (a."teamid" = @exp_1)
UNION ALL
SELECT a."id", a."name", a."companyid", a."teamid", a."type"
FROM "roles" a
WHERE (a."companyid" = 0) AND (a."type" = @exp_0) ) a
ORDER BY a."companyid", a."id"
limit 10
下面的这个sql ,type 不应该参数化, 逻辑也有点问题
FROM "roles" a WHERE (a."companyid" = 0) AND (a."type" = @exp_0) ) a
demo 代码 【PostgreSQLCodeFirstTest】
生成的sql
下面的这个sql ,type 不应该参数化, 逻辑也有点问题 FROM "roles" a WHERE (a."companyid" = 0) AND (a."type" = @exp_0) ) a
数据库版本
all
安装的Nuget包
3.2.813
.net framework/. net core? 及具体版本
net 6