whereexpress如何加括号？

[j4587698]

有类似的需求，
Expression where = x => x.Id > 10;

if(xxx){
where = where.And(x => x.createtime != null && x.createtime < '2021-7-1');
}
if(xxx){
where = where.Or(x => x.updatetime != null && x.updatetime < '2021-7-1')
}

现在想做出这样的Sql，where (Id > 10) and (( createtime != null and x.createtime < '2021-7-1') or (updatetime != null && updatetime < '2021-7-1'))

应该怎么做才能加上对应的括号呢？

[luoyunchong]

• https://docs.microsoft.com/zh-cn/sql/t-sql/language-elements/operator-precedence-transact-sql?view=sql-server-ver15

AND 优先级高于OR

参考 #603

[j4587698]

@luoyunchong 但是例子里有or优先级高于and的地方啊，希望是 xx and (xx or xx)，如果单纯依靠优先级的话应该会变成(xx and xx) or xx 这个逻辑吧？现在是希望做到让or的优先级在某些可控的情况下比and高。

[luoyunchong]

AND 优先级高于 OR，你给的这个没有问题。&& 会被转成 AND。

如果有需要，可把OR放到最后一段测下。

[j4587698]

@luoyunchong 我写了一个测试例子，代码如下：
test.cs

public class Test
    {
        [Column(IsPrimary = true, IsIdentity = true)]
        public int Id { get; set; }

        public string Name { get; set; }
    }

main.cs

static void Main(string[] args)
        {
            Console.WriteLine("Hello World!");
            IFreeSql fsql = new FreeSql.FreeSqlBuilder()
                .UseConnectionString(FreeSql.DataType.Sqlite, @"Data Source=db1.db")
                .UseAutoSyncStructure(true) //自动同步实体结构到数据库，FreeSql不会扫描程序集，只有CRUD时才会生成表。
                .Build(); //请务必定义成 Singleton 单例模式
            List<Test> tests = new List<Test>()
            {
                new Test() {Name = null},
                new Test() {Name = ""},
                new Test() { Name = "test"}
            };
            fsql.Insert<Test>().AppendData(tests).ExecuteAffrows();
            Expression<Func<Test, bool>> where = x => x.Id > 3;
            where = where.And(x => x.Name == null);
            where = where.Or(x => x.Name == "");
            var sql = fsql.Select<Test>().Where(where).ToSql();
            var ret = fsql.Select<Test>().Where(where).ToList();
        }

我希望查询Test表里面Id>3 and (Name == null or Name == "")的内容，如果直接这样写，得到的sql是

SELECT a."Id", a."Name" 
FROM "Test" a 
WHERE ((a."Id" > 3 AND a."Name"  IS  NULL OR a."Name" = ''))

最后的结果为：

它查到了Id=2的那条数据，但是我希望的结果应该是查询到空，表里面根本没有Id>3的内容。

[j4587698]

@luoyunchong 找到办法了，套娃即可，加一个where1

static void Main(string[] args)
        {
            Console.WriteLine("Hello World!");
            IFreeSql fsql = new FreeSql.FreeSqlBuilder()
                .UseConnectionString(FreeSql.DataType.Sqlite, @"Data Source=db1.db")
                .UseAutoSyncStructure(true) //自动同步实体结构到数据库，FreeSql不会扫描程序集，只有CRUD时才会生成表。
                .Build(); //请务必定义成 Singleton 单例模式
            List<Test> tests = new List<Test>()
            {
                new Test() {Name = null},
                new Test() {Name = ""},
                new Test() { Name = "test"}
            };
            fsql.Insert<Test>().AppendData(tests).ExecuteAffrows();
            Expression<Func<Test, bool>> where = x => x.Id > 3;
            Expression<Func<Test, bool>> where1 = x => x.Name == null;
            //where = where.And(x => x.Name == null);
            where1 = where1.Or(x => x.Name == "");
            where = where.And(where1);
            var sql = fsql.Select<Test>().Where(where).ToSql();
            var ret = fsql.Select<Test>().Where(where).ToList();

得到的Sql既可以为

SELECT a."Id", a."Name" 
FROM "Test" a 
WHERE (a."Id" > 3 AND (a."Name"  IS  NULL OR a."Name" = ''))

完结，感谢。

[j4587698]

套娃完成，代码如下

IFreeSql fsql = new FreeSql.FreeSqlBuilder()
                .UseConnectionString(FreeSql.DataType.Sqlite, @"Data Source=db1.db")
                .UseAutoSyncStructure(true) //自动同步实体结构到数据库，FreeSql不会扫描程序集，只有CRUD时才会生成表。
                .Build(); //请务必定义成 Singleton 单例模式
            List<Test> tests = new List<Test>()
            {
                new Test() {Name = null},
                new Test() {Name = ""},
                new Test() { Name = "test"}
            };
            fsql.Insert<Test>().AppendData(tests).ExecuteAffrows();
            Expression<Func<Test, bool>> where = x => x.Id > 3;
            Expression<Func<Test, bool>> where1 = x => x.Name == null;
            //where = where.And(x => x.Name == null);
            where1 = where1.Or(x => x.Name == "");
            where = where.And(where1);
            var sql = fsql.Select<Test>().Where(where).ToSql();
            var ret = fsql.Select<Test>().Where(where).ToList();

[luoyunchong]

这样也能生成括号

Expression<Func<Test, bool>> where = x => x.Id > 3;
where = where.And(x => x.Name == null || x.Name == "");

SELECT a."Id", a."Name" 
FROM "Test" a 
WHERE (a."Id" > 3 AND (a."Name"  IS  NULL OR a."Name" = ''))