转载 linq 实现查询字符串拼接 : And 和 OR 两种方式

转载 linq 实现查询字符串拼接 : And 和 OR 两种方式

N年前我们是这样来 拼接查询字符串的:

 

public string Test(string a, string b, string c,string d)
   {
       string sql = "SELECT * FROM Users WHERE 1=1";
       if (!string.IsNullOrEmpty(a))
       {
           sql += " AND name='" + a + "'";
       }
       if (!string.IsNullOrEmpty(b))
       {
           sql += " AND age='" + b+ "'";
       }
       if (!string.IsNullOrEmpty(c))
       {
           sql += " AND sex='" + c + "'";
       }
       if (!string.IsNullOrEmpty(d))
       {
           sql += " AND address='" + d + "'";
       }
       return sql.ToString();
   }

public string Test(string a, string b, string c,string d) { string sql = "SELECT * FROM Users WHERE 1=1"; if (!string.IsNullOrEmpty(a)) { sql += " AND name='" + a + "'"; } if (!string.IsNullOrEmpty(b)) { sql += " AND age='" + b+ "'"; } if (!string.IsNullOrEmpty(c)) { sql += " AND sex='" + c + "'"; } if (!string.IsNullOrEmpty(d)) { sql += " AND address='" + d + "'"; } return sql.ToString(); } 

现在我们使用linq来实现上边的代码：

 [c-sharp]

public void Test(string a, string b, string c,string d)
       {
           QueryContext query = new QueryContext();
           var q = from u in query.Users
                    select u;
           if (!string.IsNullOrEmpty(a))
           {
               q = q.Where(p => p.name == a);
           }
           if (!string.IsNullOrEmpty(b))
           {
               q = q.Where(p => p.age == b);
           }
           if (!string.IsNullOrEmpty(c))
           {
               q = q.Where(p => p.sex == c);
           }
           if (!string.IsNullOrEmpty(d))
           {
               q = q.Where(p => p.address == d);
           }
           q.ToList();  //上边的所有if，只有到此处才会执行
       }

public void Test(string a, string b, string c,string d) { QueryContext query = new QueryContext(); var q = from u in query.Users select u; if (!string.IsNullOrEmpty(a)) { q = q.Where(p => p.name == a); } if (!string.IsNullOrEmpty(b)) { q = q.Where(p => p.age == b); } if (!string.IsNullOrEmpty(c)) { q = q.Where(p => p.sex == c); } if (!string.IsNullOrEmpty(d)) { q = q.Where(p => p.address == d); } q.ToList(); //上边的所有if，只有到此处才会执行 } 

ps: 如果查询字符串内 不是AND，而是OR 的话

如：

 

 

public string Test(string a, string b, string c,string d)
   {
       string sql = "SELECT * FROM Users WHERE 1=1";
       if (!string.IsNullOrEmpty(a))
       {
           sql += " OR name='" + a + "'";
       }
       if (!string.IsNullOrEmpty(b))
       {
           sql += " OR age='" + b+ "'";
       }
       if (!string.IsNullOrEmpty(c))
       {
           sql += " OR sex='" + c + "'";
       }
       if (!string.IsNullOrEmpty(d))
       {
           sql += " OR address='" + d + "'";
       }
       return sql.ToString();
   }

此种情况，通过linq可以这样实现：

 

[c-sharp]

public void Test(string a,string b,string c,string d)
    {
             QueryContext query = new QueryContext();
             var q1 = from u in query.Users
                      where u.name== a && a != ""
                  || u.age == b && b != ""
                  || u.sex == c && c != ""
                  || u.address ==d && d !=""
                      select u;
             q1.ToList();
    }