有条件地转换SQL内置where子句到LINQ [英] Converting conditionally built SQL where-clause into LINQ

问题描述

所以我没有在这里看到一个问题，确实解决了这个问题。它还挺有关LINQ一个新手的问​​题，但我想知道是否有可能下面的SQL查询转换为LINQ查询（用C＃建）：

 公共无效DoSomeQuery（布尔whereCriteria1，布尔whereCriteria2）
 {
字符串的SQLQuery =SELECT p *。 
串fromClause =由人P; 
串whereClause =WHERE; 
 
如果（whereCriteria1）
 {
 fromClause + =，地址; 
 whereClause + =p.addressid = a.addressid和a.state ='霸'和a.zip ='16127'
} 
 
如果（whereCriteria2）
 {
 fromClause + =，颜色为c 
 whereClause + =p.favoritecolorid = c.colorid和c.name =蓝
} 
 
 //如果块可以随意在这里还有更多的标准
 
的SQLQuery + = fromClause + whereClause; 
 
 //做的东西来运行查询
} 
  

那有意义吗？我有一堆布尔变量让我知道条款的标准添加这哪里。我想这样做，在LINQ因为嗯...这是丑陋的。

解决方案

  VAR查询从人的选择p p =; 
如果（whereCriteria1）
 {
 =查询从查询​​
 p在上p.addressid地址加入等于a.addressid 
，其中a.state = PA '
，其中a.zip ='16127'
器选择p; 
} 
如果（whereCriteria2）
 {
 =查询从查询​​
 p将C在p.favoritecolorid颜色等于c.colorid 
，其中c 。名称='蓝'
的选择p; 
} 
  

So I didn't see a question here that really answers this question. It's kinda a newbie question about linq but I would like to know if it would be possible to convert the following sql query (built using C#) into a linq query:

public void DoSomeQuery(bool whereCriteria1, bool whereCriteria2)
{
    string sqlQuery = "SELECT p.*";
    string fromClause = " FROM person p";
    string whereClause = " WHERE ";

    if (whereCriteria1)
    {
        fromClause += ", address a";
        whereClause += " p.addressid = a.addressid and a.state = 'PA' and a.zip = '16127' "
    }

    if (whereCriteria2)
    {
        fromClause += ", color c";
        whereClause += " p.favoritecolorid = c.colorid and c.name = 'blue'"
    }

    // arbitrarily many more criteria if blocks could be here

    sqlQuery += fromClause + whereClause;

    // do stuff to run the query
}

Does that make sense? I have a bunch of bool variables that let me know which where clause criteria to add. I want to do that in linq because well ... this is ugly.

解决方案

var query = from p in persons select p;
if (whereCriteria1)
{
  query = from p in query 
  join a in address on p.addressid equals a.addressid 
  where a.state = 'PA' 
  where a.zip = '16127'
  select p;
}
if (whereCriteria2)
{
  query = from p in query
  join c in colors on p.favoritecolorid equals c.colorid 
  where c.name = 'blue'
  select p;
}

这篇关于有条件地转换SQL内置where子句到LINQ的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！